The Query Planner: Statistics, Selectivity, Scans and Joins

In one line

PostgreSQL's cost-based planner estimates how many rows each step returns (cardinality) from table statistics, assigns costs to candidate plans, and picks the cheapest. Scan types (sequential, index, index-only, bitmap) and join algorithms (nested loop, hash, merge) each win in different row-count regimes; wrong estimates lead to wrong choices.

0/5 · 0%

Think of it like this

Choosing a route. A navigation app estimates traffic on each road (statistics), computes the cost of several routes, and picks the fastest. If its traffic data is stale, it confidently sends you into a jam.

Key ideas

01
Statistics (ANALYZE, run by autovacuum): row counts, per-column null fraction, distinct values, most common values with frequencies, and histograms. default_statistics_target = 100 samples 30,000 rows; raise it for skewed columns.
02
Selectivity = the fraction of rows a predicate keeps. status = 'open' might be 2% (use an index) or 90% (sequential scan is cheaper). Columns are assumed independent unless you create extended statistics (CREATE STATISTICS ... (dependencies, ndistinct, mcv)) for correlated columns like city and pin code.
03
Scans: sequential (read everything, best for large fractions), index scan (follow the index to each heap tuple; random I/O), index-only scan (no heap), bitmap index + bitmap heap scan (collect matching pages, then read them in physical order; good for medium selectivity and combining indexes).
04
Joins: nested loop (for each outer row, probe the inner, ideally via an index; best for small outer sets), hash join (build a hash table of the smaller side; best for large unsorted equi-joins; needs work_mem), merge join (both sides sorted on the key; good for large pre-sorted inputs).
05
Cost model constants: seq_page_cost = 1, random_page_cost = 4 (lower it to ~1.1 on SSDs so the planner doesn't avoid index scans), cpu_tuple_cost and others. Costs are relative units, not milliseconds.

Code & diagrams

planner.sqlsql

SELECT attname, n_distinct, null_frac, most_common_vals, most_common_freqs
FROM pg_stats WHERE tablename = 'orders' AND attname = 'status';
--  attname | n_distinct | null_frac | most_common_vals          | most_common_freqs
--  status  |          4 |         0 | {delivered,open,cancelled} | {0.91,0.06,0.03}

-- correlated columns: teach the planner
CREATE STATISTICS addr_stats (dependencies, mcv) ON city, postal_code FROM address;
ANALYZE address;

-- SSD: make random reads realistic
ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();

Interview problem

The problem

The same query is fast for one tenant and slow for another

SELECT * FROM orders WHERE tenant_id = ? AND status = 'open' ORDER BY created_at LIMIT 50 takes 2 ms for most tenants but 40 s for the largest tenant, which has 60% of all rows. Explain and fix.

When it breaks

Stale statistics after a bulk load

What you see

The planner thinks a table has 1,000 rows when it has 50M, chooses nested loops, and a report runs for hours.

Fix & prevent

Run ANALYZE after bulk loads and large deletes; autovacuum's analyze may lag behind batch jobs.

Explain it without notes

When is a hash join better than a nested loop?

Practice

Explain why a query returning 40% of a table uses a sequential scan despite an index.

Trade-offs

↔
Cost-based planning adapts to data; bad statistics or skew make it confidently wrong, so monitor estimate accuracy.

Done when you can

I can explain statistics, selectivity, scan types and join algorithms and why the planner chose a plan.

Command Palette