TopolearnWhen a plan goes wrong, the cause is almost always a bad estimate; fixing it requires knowing where estimates come from.
PostgreSQL's planner is cost-based: it enumerates candidate plans, estimates the cost of each, and picks the cheapest. Costs combine page reads (seq_page_cost = 1, random_page_cost = 4 by default; values near 1.1 suit SSDs) with per-row CPU costs. Everything hinges on row-count estimates.
Estimates come from statistics gathered by ANALYZE (run automatically by autovacuum). It samples rows (300 times default_statistics_target, so 30,000 at the default of 100) and stores, per column, in pg_stats: the fraction of NULLs, the number of distinct values, the most common values with their frequencies, a histogram of the rest, and the correlation between value order and physical order.
From those, the planner computes selectivity. Its weak spot is the assumption that conditions are independent: for WHERE city = 'London' AND country = 'UK' it multiplies the two selectivities and underestimates badly. Extended statistics fix that: CREATE STATISTICS ... (dependencies, ndistinct, mcv) ON city, country FROM addresses. For skewed columns, raise the per-column target: ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 1000.
Joins have three algorithms:
- Nested loop: for each outer row, look up matches, ideally via an index. Best when the outer side is small.
- Hash join: build a hash table on the smaller input, probe with the other. Good for large equality joins; needs
work_mem. - Merge join: walk two inputs sorted on the join key. Good when both are already sorted or very large.
The planner searches join orders exhaustively up to join_collapse_limit (8) tables; beyond geqo_threshold (12) a genetic search takes over.
Prepared statements may switch from custom plans to a cached generic plan after five executions, which can hurt for skewed values; plan_cache_mode controls this.
Example
-- What the planner knows about a column
SELECT null_frac, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';
-- null_frac | n_distinct | most_common_vals | most_common_freqs
-- 0 | 3 | {shipped,pending,cancelled} | {0.81,0.15,0.04}
-- Refresh statistics after a bulk load
ANALYZE orders;
-- Skewed column: keep more detail
ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000;
-- Correlated columns: teach the planner about the dependency
CREATE STATISTICS addr_city_country (dependencies, ndistinct, mcv)
ON city, country FROM addresses;
ANALYZE addresses;
-- On SSD storage, random reads are not 4x sequential reads
ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();Remember
- Plans are chosen by estimated cost; estimates come from pg_stats via ANALYZE
- Independent-column assumptions misestimate correlated filters; use CREATE STATISTICS
- Nested loop for small outer sides, hash join for large equality joins, merge join for sorted inputs
- Run ANALYZE after bulk loads
Watch out for
- Bulk-loading a table and querying it immediately, before autovacuum has analysed it, with plans based on an empty table.