This topic describes cost calculation and related formulas.

Cost calculation model

Cost Default value Description
seq_page_cost 1.0 The cost of a sequential scan on a page.
random_page_cost 4.0 The cost of a random scan on a page.
cpu_tuple_cost 0.01 The cost of processing a single row.
cpu_index_tuple_cost 0.005 The cost of processing an index entry.
cpu_operator_cost 0.0025 The cost of processing each function or operator.

Full table scan

postgres=# explain select * from test where id <1000;
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on test  (cost=0.00..1693.00 rows=5680 width=4)
   Filter: (id < 1000)
(2 rows)

postgres=# select relpages,reltuples from pg_class where relname='test';
 relpages | reltuples
 ------------+-----------
        443  |    100000
(1 row)

The following formulas are provided to calculate the costs:

  • TOTAL_COST = CPU cost + I/O cost
  • CPU cost = cpu_tuple_cost × reltuples + cpu_operator_cost × reltuples
  • CPU cost = 0.01 × 100000 + 0.0025 × 100000 = 1250.0
  • I/O cost = seq_page_cost × relpages
  • I/O cost = 1.0 × 443 = 443
  • TOTAL_COST = 1250 + 443 = 1693

Index scan

postgres=# explain select * from test where id =1414;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Index Only Scan using test_id_idx on test  (cost=0.29..72.66 rows=30 width=4)
   Index Cond: (id = 1414)
(2 rows)

The following formulas are provided to calculate the costs:

  • RUN_COST = CPU cost + I/O cost
  • CPU cost = CPU cost of the index scan + CPU cost of the table scan
  • CPU cost of the index scan = Selectivity × Number of index entries × (cpu_index_tuple_cost + cpu_operator_cost)
  • CPU cost of the table scan = Selectivity × Number of rows in the table × cpu_tuple_cost

Calculate the CPU cost of the index scan

postgres=# select most_common_freqs[array_position((most_common_vals::text)::real[],1414::real)] from pg_stats where tablename ='test';
 most_common_freqs
-------------------
            0.0003

## Selectivity = 0.0003

postgres=# select relpages,reltuples from pg_class where relname='test_id_idx';
 relpages | reltuples
    ----------+-----------
          276 |    100000

## Number of index entries = 100000 Number of index pages = 276

postgres=# select relpages,reltuples from pg_class where relname='test';
 relpages | reltuples
    ----------+-----------
          443 |    100000

## Number of rows in the table = 100000 Number of table pages = 443

In this example, the following CPU costs are calculated:

  • CPU cost of the index scan = 0.0003 × 100000 × (0.0025 + 0.005) = 0.22500000
  • CPU cost of the table scan = 0.0003 × 100000 × 0.01 = 0.300000
  • CPU cost = 0.300000 + 0.22500000 = 0.52500000

Calculate the I/O cost of the index scan

I/O cost of the index scan = ceil (Selectivity × Number of index pages) × random_page_cost

I/O cost of the table scan = max_io_cost + Correlation coefficient × Correlation coefficient × (min_io_cost - max_io_cost)

max_io_cost = Selectivity × Number of rows × random_page_cost = 120

min_io_cost = 1 × random_page_cost + (ceil [Selectivity × Number of table pages] - 1) × seq_page_cost = 4

postgres=# select correlation from pg_stats where tablename ='test';
 correlation
-------------
    0.670535

Correlation coefficient = 0.670535

I/O cost = 4 + 67.844406397900 = 71.844406397900

In this example, the following I/O costs are calculated:

  • RUN_COST = 71.844406397900 + 0.52500000 = 72.369406397900
  • START_COST = 0.29
  • TOTAL_COST = 72.369406397900 + 0.29 = 72.659406397900 ≈ 72.66