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