# How Query Planners Think: Costs, Cardinality, and Lies

> SQL says what, the planner decides how — by estimating row counts through a chain of guesses and picking the cheapest of thousands of plans. When the estimate is wrong by 1000x, so is the plan. Reading EXPLAIN is learning to audit those guesses.

URL: https://harshith.in/blog/query-planner-thinking/
Author: Harshith Sunku (Staff Engineer) — https://harshith.in/about/
Tags: databases, performance, algorithms

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.