Query Planner

Having an index from the Indexing lesson doesn't guarantee Postgres uses it. Every query is handed to the planner, which generates several candidate execution strategies, estimates the cost of each, and picks the cheapest one it can find — not necessarily the fastest one that exists.

The planner never looks at your actual rows to make its choice — only at statistics gathered in advance and the cost constants below. That's worth keeping in mind whenever a plan looks "wrong": it may be a perfectly rational decision given stale or incomplete information.

The cost model

Costs are abstract units, not milliseconds, built from a handful of tunable constants:

SHOW seq_page_cost;      -- 1     (cost to read one page sequentially)
SHOW random_page_cost;   -- 4     (cost to read one page randomly)
SHOW cpu_tuple_cost;     -- 0.01  (cost to process one row)
SHOW cpu_index_tuple_cost; -- 0.005

random_page_cost defaulting to 4x seq_page_cost reflects spinning-disk seek latency. On SSD-backed storage — which is most production deployments today — random reads are much closer in cost to sequential ones, and it's common to tune random_page_cost down to 1.1, which makes the planner choose index scans more readily.

Statistics

The planner's cost estimates are only as good as its statistics about the actual data — row counts, most-common values, and value distribution per column — collected by ANALYZE (which autovacuum also runs automatically):

ANALYZE accounts;

SELECT * FROM pg_stats WHERE tablename = 'accounts' AND attname = 'balance';
-- n_distinct, most_common_vals, most_common_freqs, correlation, ...

Scan strategies

For a single table, the planner chooses between:

• Sequential scan — read every page in order. Cheapest when a large fraction of the table matches, since it avoids random I/O entirely.
• Index scan — walk the index, then fetch each matching row from the heap. Good when a small fraction of rows match.
• Bitmap heap scan — walk the index to build an in-memory bitmap of matching pages, then read those pages in physical order. A middle ground: it beats a plain index scan when enough rows match that jumping around the heap row-by-row would thrash, but still beats a full sequential scan.

Join strategies

For joining two tables, the planner picks from:

• Nested loop — for each row in the outer table, scan (or index-probe) the inner table. Best when one side is small or well-indexed on the join column.
• Hash join — build an in-memory hash table from the smaller side, then stream the larger side through it. Good for large, unsorted inputs with no useful index.
• Merge join — if both inputs are already sorted on the join key (or the planner sorts them first), walk both in order simultaneously. Good for large, pre-sorted inputs.

The planner estimates the cost of every viable combination of scan and join strategy for a query and picks the cheapest total. You rarely need to force a particular plan — but you do need to be able to read the one it chose, which is the whole subject of the next lesson, EXPLAIN & EXPLAIN ANALYZE.