Databases
Execution plans: multiple-choice review
Crux Multiple-choice synthesis across the execution-plans unit — reading EXPLAIN ANALYZE, scan and join choice, the row-estimate cascade, extended statistics, and the generic-plan trap.
Your altitude — climbing toward senior
ZeroJuniorMiddleSenior
You are at senior altitude — in orbitSix questions that cut across the whole unit. Each one is a plan you would actually read in an incident — not a definition to recite, but a diagnosis to make from the output and a fix to choose under load.
Goal
Confirm you can read an EXPLAIN ANALYZE plan, trace a bad row estimate up through the join it broke, and pick the fix that addresses the cause rather than the symptom — the synthesis the seven lessons built toward.
Quiz
Completed
A query is fast on staging and slow in production. Same schema, same indexes. EXPLAIN ANALYZE on the prod run shows: Nested Loop (actual time=4180..4192 rows=50 loops=1) -> Index Scan on orders (cost=0..3800000 rows=50) (actual time=12..3950 rows=520000 loops=1) -> Index Scan on customers_pkey (actual time=0.002..0.002 rows=1 loops=520000) What is the root cause?
Heads-up The inner is an Index Scan on customers_pkey — the index exists. The problem is that loops=520000, driven by the outer-side underestimate, not a missing index.
Heads-up Forcing the algorithm (enable_nestloop=off) masks the symptom. With a correct 520,000-row estimate the planner would pick Hash Join itself. Fix the estimate, not the algorithm.
Heads-up A correct plan over more data scales gracefully. A 10,400x loop blowup from a 50-vs-520,000 estimate gap is a planner error, not linear growth.
Quiz
Completed
A 1M-row table has an index on status. The query WHERE status = 'active' matches 400,000 rows (40% of the table). The planner picks Seq Scan instead of the index. Is this a bug, and why?
Heads-up The planner chooses by cost, not by index presence. Index Scan pays random_page_cost per matching row; at 40% selectivity that dwarfs a single sequential pass.
Heads-up Index Only Scan needs all projected columns in the index AND an all-visible Visibility Map. Neither is implied here, and it would still touch 40% of the data.
Heads-up Seq Scan wins on low selectivity, not on size. For a predicate matching 100 rows out of 1M the index is dramatically cheaper. Selectivity, not row count, drives the crossover.
Quiz
Completed
A predicate filters on WHERE country='US' AND region='CA' AND status='shipped'. The planner predicts 500 rows; the node actually emits 50,000. Single-column statistics are fresh. What fixes the estimate?
Heads-up A higher per-column target gives finer single-column histograms and MCV lists, but the planner still multiplies P(country) x P(region). The independence assumption survives — only extended statistics replace it.
Heads-up An index changes execution paths, not the planner's multi-column row-estimate model. The estimate stays wrong until extended statistics teach it the correlation.
Heads-up Same problem at the global level: finer per-column stats, but cross-column correlation is still unmodeled. CREATE STATISTICS is the only tool that captures functional dependencies.
Quiz
Completed
A parameterised query is fast for months, then P99 jumps to 4s while the mean stays at ~3ms. pg_stat_statements shows a large stddev_exec_time. EXPLAIN (GENERIC_PLAN) on the prepared form shows: Index Scan using idx_orders_created on orders Filter: ((workspace_id = $1) AND (status = $2)) What happened and what is the immediate fix?
Heads-up The composite index still exists — the generic plan just cannot use it because it does not know the parameter values at plan time. Re-planning per execution, not re-indexing, is the fix.
Heads-up Data growth causes gradual degradation, not bimodal latency (low mean, high stddev) that appears after a fixed number of executions. The signature points squarely at the generic-plan switch.
Heads-up Recycling restarts the 5-execution custom-plan cycle from scratch — it would not produce a sustained generic-plan regression. The stddev spike is the generic-plan trap.
Quiz
Completed
A B2B dashboard degrades every Monday morning after a Sunday-night batch import of 20M rows, then recovers by Tuesday on its own. What is the cause and the durable fix?
Heads-up The self-recovery by Tuesday is the tell — autovacuum eventually runs ANALYZE and the estimates correct themselves. Traffic differences would not heal automatically overnight.
Heads-up Lock contention manifests as queries blocked in pg_stat_activity, and releases when the transaction commits — not a day later. The pattern matches stale statistics, not locking.
Heads-up Index usefulness is set by selectivity, not size, and rebuilds would not explain the Tuesday self-recovery. The root cause is statistics freshness, fixed by ANALYZE in the batch hook.
Quiz
Completed
EXPLAIN (ANALYZE, BUFFERS) on a Hash Join shows Hash Batches: 64 and Sort Method: external merge Disk: 450MB on a sort beneath it. Mean latency is high but reads are mostly shared hit. What is the bottleneck and the targeted fix?
Heads-up Buffers are mostly shared hit, so the heap pages are cached — this is not a cold-data I/O problem. The spill to disk from work_mem overflow is what is costing time.
Heads-up A sort-aligned index could enable a Merge Join, but the immediate, measured symptom is work_mem spill on both nodes. Sizing work_mem fixes the spill directly.
Heads-up Hash Batches is the number of disk-partitioned passes when the hash table does not fit work_mem — not a loop count. The fix is more work_mem, not a rewrite.
Recap
The through-line across the unit is one reading discipline: at every plan node compare rows-estimated to actual rows, find where the gap opens, and trace it upward — a bad estimate cascades into wrong scan choices, wrong join algorithms, and wrong sort methods above it. Scan choice follows selectivity, join choice follows the outer-side estimate, and the durable fixes attack the cause: fresh statistics via ANALYZE, extended statistics for correlated columns, force_custom_plan for skewed prepared statements, and SSD-tuned cost constants. Forcing an algorithm or rebuilding an index treats the symptom; fixing the estimate corrects the whole plan.