Part 6 · 3 chapters · ~18 min

The Planner

Exhaustive and genetic join search, statistics (pg_statistic, MCVs, histograms, n_distinct, extended statistics), cost parameters and the SSD adjustment, scan and join nodes including merge join, aggregation and spilling, parallel query, JIT, reading EXPLAIN (ANALYZE, BUFFERS), and plan instability with pg_hint_plan.

16

Statistics and costs

code
-- what the planner knows about a column
SELECT attname, null_frac, n_distinct, most_common_vals, most_common_freqs, histogram_bounds, correlation
FROM pg_stats WHERE tablename = 'transfers' AND attname = 'state';

-- correlated columns: the planner assumes independence unless told otherwise
CREATE STATISTICS transfers_country_currency (dependencies, ndistinct, mcv) ON country, currency FROM transfers;
ANALYZE transfers;

-- more detail for skewed columns
ALTER TABLE transfers ALTER COLUMN merchant_id SET STATISTICS 1000;   -- default 100

-- the SSD adjustment almost everyone forgets
ALTER SYSTEM SET random_page_cost = 1.1;  SELECT pg_reload_conf();
FROM SQL TO A PLAN
parse, rewrite, plan with statistics and costs, execute
SQL textparserparse treerewriterviews, rulesplannerpaths + costsexecutorplan tree
swipe the figure sideways, or tap expand for full screen
1/5
parse
The parser checks syntax and the analyser resolves names against the catalogue into a query tree.
syntax, then names resolved into a query treeerrors about unknown columns come from here
17

Scan and join nodes

Aggregation uses HashAggregate (a hash table of groups) or GroupAggregate (sorted input). Both can spill to disk since 13 when groups exceed work_mem. Parallel query starts workers (max_parallel_workers_per_gather) that each scan part of a table; a Gather or Gather Merge node collects results. Not everything parallelises: queries that write, cursors, functions marked parallel unsafe, and small tables. JIT (LLVM) compiles expressions and tuple deforming for long analytical queries; for short OLTP queries the compile time costs more than it saves, so many teams set jit = off or raise jit_above_cost.

SCANS AND JOINS
the executor nodes, and when the planner picks each
Seq ScanRead every page in order. Bestwhen a large fraction of rowsmatch or the table is small.Index ScanWalk the index, fetch each heaptuple. Best for few rows; randomIO per row.Bitmap ScanCollect matching ctids into abitmap, then read heap pages inorder. Middle ground; combinesindexes.Nested LoopFor each outer row, probe theinner (ideally via an index). Bestwhen the outer side is small.Hash JoinBuild a hash table on the smallerinput, probe with the larger.Equality joins; needs work_mem.Merge JoinWalk two sorted inputs together.Large inputs already sorted. MySQLhas no merge join.
swipe the figure sideways, or tap expand for full screen
1/6
seq scan
Sequential reads are cheap per page (seq_page_cost = 1). A seq scan on a big table is not wrong if the query needs most of it.
reads everything, sequentiallyright when most rows match
18

Reading EXPLAIN, and plan instability

code
EXPLAIN (ANALYZE, BUFFERS)
SELECT t.id, t.amount_kobo FROM transfers t JOIN accounts a ON a.id = t.account_id
WHERE a.owner_id = 42 AND t.created_at > now() - interval '30 days';

Nested Loop  (cost=0.85..412.10 rows=18 width=16) (actual time=0.05..2.31 rows=1240 loops=1)
  Buffers: shared hit=3104 read=12
  -> Index Scan using accounts_owner_id_idx on accounts a  (rows=1) (actual rows=3 loops=1)
  -> Index Scan using transfers_account_id_created_at_idx on transfers t (rows=18) (actual rows=413 loops=3)
reading it
  1. Compare estimated rows with actual rows at each node; a 10× or worse gap is the usual root cause.
  2. Multiply actual time and rows by loops for inner nodes.
  3. Buffers: shared hit (from cache) versus read (from disk or OS cache); temp read and written means spilling.
  4. Find the node where time jumps; fix its estimate (statistics) or its access path (an index) before reaching for hints.

Plan instability: plans change when statistics change, when parameter values hit different MCVs (generic versus custom plans for prepared statements, controlled by plan_cache_mode), or after upgrades. Postgres core has no hints by philosophy (fix the statistics, not the plan); the pg_hint_plan extension exists for emergencies, and auto_explain logs slow plans as they happen.