Reading a Query Plan as an Execution Hypothesis

A query planner estimates alternative ways to produce a result and chooses one using statistics and cost assumptions. A plan is an executable tree of operators — scans, joins, sorts, aggregates — and reading it well means treating it as a hypothesis: here is what the engine believes about your data, and here is the decision that belief produced.

Join algorithms are different shapes of work

The planner's central choice for a join is which algorithm, and each has a cost shape:

nested loop    for each outer row, probe the inner side
  good when    outer is small (hundreds) AND inner probe is an index
  cost         O(outer × probe cost)
               1,000 outer rows × 0.1 ms index probe = ~100 ms
  bad when     inner has no index: 1,000 × 140 ms scan = 140 s
 
hash join     build a hash table on one side, probe with the other
  good when    large unsorted equijoin (the common case)
  cost         O(build + probe) — linear in both inputs
               100k build rows + 132k probe rows ≈ tens of ms
  bad when     inner side must produce output in order (adds a sort),
               or inputs exceed work_mem (spills to disk, 10×+ slower)
 
merge join    sort both sides, then walk them together
  good when    inputs already sorted (by an index or a prior operator)
  cost         O(sort unless free + merge)

The planner picks by estimated row counts and available indexes — which is why the same query can flip algorithms after ANALYZE, and why a join that "used to be fast" may change shape the day your table crosses a size threshold.

Estimations diverge, and the root tells you where

Hash Join (cost=…) (actual time=187.6 rows=131904 loops=1)
  rows est. 132,400  vs actual 131,904     ← this node is fine (0.4% off)
  -> Seq Scan on orders (estimated 132,400, actual 132,011)   ← fine
  -> Hash (estimated 41,000, actual 88,500)                   ← first big miss

Read the tree top-down and find the first node where estimated and actual rows diverge by more than ~2×. Everything above it inherited a bad cardinality estimate — a wrong join order, a premature aggregation, a method choice made for a data shape that doesn't exist. A plan root showing 10× error usually points at one child with a 100× error.

Also keep the units straight: cost=6841.00 is relative units, not milliseconds — the planner compares alternatives with it, and only actual time from EXPLAIN ANALYZE is real time. A plan "costing 200,000" may run in 12 ms on warm cache.

An index exists but the query still scans: four reasons

The check question, with the evidence that distinguishes each cause:

reason                          evidence in the plan
------------------------------  ---------------------------------------------
1. estimate says the index       estimated rows ≈ total rows (e.g., 130k/200k);
   wouldn't help                 planner expects to read most of the table, and
                                 a seq scan of N pages beats index+N random reads
2. table is small         total seq-scan cost ≈ index-scan cost; few pages;
                                 index adds tree traversal for no page savings
3. statistics are stale/wrong    huge rows-estimate vs actual mismatch, old
                                 last_analyze in pg_stat_user_tables
4. (variant) index can't         predicate appears under "Filter:" instead of
   serve the predicate           "Index Cond:" — expression, leading-column gap,
                                 type mismatch, or OR across columns

Reason 4 is the one that means the index is unusable, and the Filter: versus Index Cond: distinction tells you in one glance. Reasons 1–3 mean the index is usable and the decision is a prediction — fix the statistics (ANALYZE), the selectivity assumption (extended statistics on correlated columns), or, if the estimate is honestly right, the query (make the predicate more selective, or accept the scan).

One trap worth naming: a query can be correct and still be the wrong shape. If the predicate matches 40% of rows, the sequential scan is the right plan — no amount of index tuning will beat reading the table once. The fix then is either a covering index that turns random reads into sequential ones, partial indexing for the hot slice, or an architectural change (denormalize the hot read path) rather than more statistics.

Cost is a hypothesis; verify it with execution

EXPLAIN predicts; EXPLAIN ANALYZE runs the statement and reports reality — including planning time, which can itself be a problem with deeply nested queries. Because ANALYZE actually executes the statement, avoid it on data-modifying SQL — an UPDATE or INSERT … RETURNING commits its changes. To measure one, use the documented pattern: BEGIN; EXPLAIN ANALYZE …; ROLLBACK;.

practical loop:
  1. EXPLAIN            → is the shape plausible? (index vs scan, join order)
  2. EXPLAIN ANALYZE    → where do estimates diverge? how many buffers?
  3. fix the INPUT to the planner (stats, predicate, schema)
     not the output (hints are a patch over a wrong belief)
  4. re-run at production-like data volume — staging with 1% of rows
     plans correctly and proves nothing

Key takeaways

• A plan is the engine's belief made visible. Estimated vs actual rows at each node is the belief check; find the first divergence, not the slowest node.
• Join method is a cost shape, not a preference: nested loop needs a small outer side and an indexed probe; hash join needs memory; merge needs sorted input.
• An unused index has four standard explanations — estimate, size, stale stats, or an unusable predicate — and Filter: vs Index Cond: plus the row estimates separate them.
• Cost units are relative; time is measured. Verify with EXPLAIN ANALYZE (BUFFERS) on production-scale data, then fix statistics before reaching for hints.

PostgreSQL Documentation: Using EXPLAIN covers the operator types and estimate columns.