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