SQL is famously declarative: you specify the result, not the procedure. Which means somewhere between your JOIN and the disk, a piece of software writes the actual program — chooses scan methods, join algorithms, join order — from a space of thousands of alternatives whose runtimes differ by factors of a million. The chooser is the query planner, its currency is estimated row counts, and the single most useful database skill above indexing is knowing how those estimates are made — because every mystery slowdown is an estimate that lied.

The Machine in One Pass

The planner enumerates plans (join-order search is the combinatorial core — dynamic programming over join subsets, falling back to heuristic search when the table count explodes past geqo_threshold-style limits), prices each with a cost model (page fetches weighted sequential-vs-random, CPU per tuple), and keeps the cheapest. Both inputs are approximations: the cost constants are your hardware distilled into four numbers, and the row counts come from statistics — per-column histograms, most-common-value lists, and distinct-count estimates gathered by ANALYZE on a sample.

Cardinality flows through the plan: the estimate for WHERE country = 'IS' feeds the choice of join algorithm, whose output estimate feeds the next join, and so on. Errors don’t add — they multiply. The canonical failure sources:

  • Correlation. city = 'Reykjavik' AND country = 'IS' — the planner multiplies the two selectivities as if independent and under-estimates by orders of magnitude. Extended/multi-column statistics exist for exactly this confession.
  • Skew. user_id = <whale> behaves nothing like the average user_id; MCV lists catch the top offenders, the long tail stays approximate.
  • Staleness. Yesterday’s ANALYZE knows nothing about last night’s bulk load; the classic “query was fine Friday” incident.
  • Opaque predicates. Functions, type coercions, LIKE '%x%' — the estimator shrugs and applies a default guess (often something like 0.5% — a constant with global production consequences).

Why a Bad Estimate Becomes a Bad Plan

Join algorithms have regimes, and cardinality picks the regime. Nested loop is ideal for few outer rows hitting an index — and catastrophic when “few” was actually two million (two million index probes). Hash join is the bulk workhorse — unless the build side was underestimated and spills to disk. Merge join wants both sides sorted, great along indexes. The signature production disaster is the misestimate that flips regimes: planner expects 3 rows, picks nested loop, reality delivers 300k, and a 50 ms query runs for an hour. In EXPLAIN (ANALYZE, BUFFERS) this is directly visible — you’re looking for one thing above all: the node where rows= estimated and actual diverge by orders of magnitude. Everything downstream of that node is the planner playing a game whose board it misread.

The same lens explains the folklore fixes. ANALYZE refreshes the guesses. Extended statistics repair correlation. Rewriting WHERE date + 1 > x to WHERE date > x - 1 restores estimability (and index eligibility). Parameter sniffing — plans cached for the first bound value and reused for very different ones — is cardinality misestimation with extra steps, and its cures (plan-per-value, recompile hints, Postgres’s generic-vs-custom plan logic) all amount to “stop assuming one estimate fits all parameters.” And the nuclear options — planner hints (pg_hint_plan, Oracle/SQL Server hints), enable_nestloop = off session experiments — are best used the way a debugger uses breakpoints: to prove which decision was wrong, then fix the statistics or schema so the planner gets it right unhinted.

It’s worth respecting how well this mostly works: a machine that turns a five-way declarative join into a near-optimal physical program in microseconds, using kilobytes of summary statistics, is one of CS’s quiet triumphs — cost-based optimization is fifty years old (System R, 1979) and still the strongest argument that declarative interfaces can be fast. The craft is keeping its inputs honest.

Takeaways

  • Plans are chosen by estimated cardinality; errors multiply through join trees, and regime-flips (nested loop at 300k rows) are the classic disaster.
  • Read EXPLAIN ANALYZE for the first node where estimate and actual diverge wildly — that’s the bug; downstream is consequence.
  • Correlation, skew, staleness, and opaque expressions are the four ways statistics lie; extended stats, MCVs, ANALYZE, and sargable rewrites are the counters.
  • Parameter sniffing = one cached estimate serving many realities; every engine has a knob because every engine has the problem.
  • Use hints and enable_* toggles to diagnose, statistics and schema to cure.