Query processing

Planner cost model

The planner estimates how many rows each step returns and how many pages it must read, multiplies them by a few cost parameters, and picks the plan with the lowest total.

In plain words

The planner cannot try every plan for real, so it guesses. It asks two questions: how many rows will match, and how many pages must be read to find them? The first answer comes from statistics that ANALYZE collects (for example a histogram of the values in a column).

Then it prices the work with a small price list: reading the next page in order costs seq_page_cost (1.0), jumping to a random page costs random_page_cost (4.0), and handling each row costs a tiny CPU amount. The cheapest total wins.

Play with the sliders: when only a few rows match, an index is cheaper; when many match, reading the whole table in order wins. On SSDs random reads are almost as cheap as sequential ones, so many people lower random_page_cost to about 1.1, which makes index plans win more often.

For DBAs and operators

The units

Costs are unitless; by convention one sequential page fetch = 1.0. Each node has a startup cost (work before the first row can be returned) and a total cost (all rows). The planner compares total cost, except when only part of the result is needed: with LIMIT n it uses startup + (total − startup) × n / rows, and for cursors it assumes cursor_tuple_fraction (0.1) of rows will be fetched.

The formulas used in this simulation (simplified from costsize.c)

Seq Scan: pages × seq_page_cost + rows × (cpu_tuple_cost + quals × cpu_operator_cost). For the demo table: 12739 × 1.0 + 2,000,000 × (0.01 + 2 × 0.0025) = 42739, exactly what PostgreSQL 18 prints.
Index Scan: index pages × random_page_cost + index tuples × (cpu_index_tuple_cost + cpu_operator_cost per index condition), plus heap pages fetched × random_page_cost (uncorrelated) or mostly seq_page_cost (when the table is physically sorted, pg_stats.correlation ≈ ±1). Heap pages fetched come from the Mackert–Lohman formula, which uses effective_cache_size to guess how many repeated visits to the same page will be cache hits.
Bitmap Heap Scan: each heap page is read once, in physical order; the per-page cost slides from random_page_cost towards seq_page_cost as the fraction of the table read grows.

Where row estimates come from

ANALYZE samples 300 × default_statistics_target rows and stores, per column, null_frac, n_distinct, most common values and a histogram (pg_stats). Range predicates use the histogram, equality uses MCVs or 1/n_distinct, and multiple predicates are multiplied as if independent unless you create extended statistics.

SELECT attname, n_distinct, correlation, most_common_vals, histogram_bounds
  FROM pg_stats WHERE tablename = 'orders';
CREATE STATISTICS orders_cs (dependencies, ndistinct) ON city, zip FROM orders;

Tuning advice

SSD / NVMe or a fully cached database: random_page_cost = 1.1 is the common choice. The 4.0 default models spinning disks where a random read costs ~40× a sequential one but ~90% of reads are assumed cached.
effective_cache_size allocates nothing; it is a hint for how much of shared_buffers + OS cache is available (typically 50–75% of RAM).
Before touching cost parameters, check estimates: EXPLAIN ANALYZE rows vs actual rows. Most bad plans are bad estimates (stale stats, correlated columns, functions on columns), not bad prices.
Test a change in one session: SET random_page_cost = 1.1; EXPLAIN …, then set it per tablespace (ALTER TABLESPACE … SET (random_page_cost = 1.1)), database, role, or globally.

For developers: inside the source

Where it lives

costsize.c: the cost model and the cost GUC variables. cost_seqscan(), cost_index(), cost_bitmap_heap_scan(), index_pages_fetched() (Mackert–Lohman, uses effective_cache_size), the join costers, set_baserel_size_estimates().
selfuncs.c: selectivity estimators attached to operators through pg_operator.oprrest / oprjoin: eqsel() → var_eq_const() (MCVs, then 1/n_distinct), scalarltsel() → scalarineqsel() → ineq_histogram_selectivity(); and index cost estimators such as btcostestimate() → genericcostestimate().
clausesel.c: clauselist_selectivity() combines clauses (extended statistics first, then range-pair detection, otherwise multiplication).
plancat.c: get_relation_info() → estimate_rel_size() scales reltuples/relpages density to the current number of blocks.
pathnode.c: add_path() keeps only non-dominated paths (costs compared with a 1% fuzz, STD_FUZZ_FACTOR), and compare_path_costs().
analyze.c (do_analyze_rel() → acquire_sample_rows(), std_typanalyze() → compute_scalar_stats()) and extended_stats.c for CREATE STATISTICS.

Key structures

Path (pathnodes.h) carries rows, startup_cost, total_cost, pathkeys and, since PostgreSQL 18, disabled_nodes. RelOptInfo holds rows, pages, tuples and the pathlist. QualCost splits expression cost into startup and per_tuple. In pg_statistic each column has stakindN slots (STATISTIC_KIND_MCV, STATISTIC_KIND_HISTOGRAM, STATISTIC_KIND_CORRELATION, …).

The heart of cost_seqscan() is the formula used on this page:

disk_run_cost = spc_seq_page_cost * baserel->pages;
get_restriction_qual_cost(root, baserel, param_info, &qpqual_cost);
cpu_per_tuple = cpu_tuple_cost + qpqual_cost.per_tuple;
cpu_run_cost = cpu_per_tuple * baserel->tuples;
...
path->disabled_nodes = enable_seqscan ? 0 : 1;

The page costs come from get_tablespace_page_costs(), which is why per-tablespace random_page_cost works. compare_path_costs() compares disabled_nodes before any cost: the enable_* switches no longer add the huge disable_cost penalty (it survives only in a few special cases).

Hooks and experiments

get_relation_info_hook (adjust or add indexes and sizes), get_relation_stats_hook / get_index_stats_hook (supply statistics), set_rel_pathlist_hook, set_join_pathlist_hook, create_upper_paths_hook; an index AM supplies amcostestimate.
PostgreSQL 18 adds pg_restore_relation_stats(), pg_restore_attribute_stats() and pg_clear_relation_stats() (relation_stats.c, attribute_stats.c): you can inject statistics into a test table and watch the plan change without loading data.
Read optimizer/README; regression tests src/test/regress/sql/stats_ext.sql and join.sql; debug_print_plan shows every node's cost fields.

Parameters that shape this

seq_page_cost random_page_cost cpu_tuple_cost cpu_index_tuple_cost cpu_operator_cost effective_cache_size default_statistics_target cursor_tuple_fraction