Database Reference
In-Depth Information
The following table shows the various cost parameters along with their default values:
Parameter
Default value
seq_page_cost
1.00
random_page_cost
4.00
cpu_tuple_cost
0.01
cpu_operator_cost
0.0025
cpu_index_tuple_cost
0.005
Consider the following example for the
seq_page_cost
and
random_page_cost
parameters:
warehouse_db=# SELECT current_setting('seq_page_cost'),
current_setting('random_page_cost');
current_setting | current_setting
-----------------+-----------------
1 | 4
(1 row)
Parent nodes have the cumulative cost of all of its children.
Sequential scans
The sequential scan scans the whole table sequentially to retrieve the required rows
from the table. The planner selects the sequential scan when a query is retrieving
large number of rows from the table and the number of appropriate indexes found.
The following is an example of a sequential scan where a query has to select all
the
history
table's records where
hist_id
is greater than
1000
. There is an index
deined for the
hist_id
column but it won't help in this case:
warehouse_db=# EXPLAIN SELECT * FROM record.history WHERE
history_id > 1000;
QUERY PLAN
------------------------------------------------------------------
Seq Scan on history (cost=0.00..2184580.00 rows=99998968
width=46)
Filter: (history_id > 1000)
Planning time: 57.910 ms
(3 rows)