Databases
Execution plans: free-recall review
Retrieval beats re-reading. For each prompt, reconstruct a full answer from memory before you open the model answer — the effort of recall is what makes the plan-reading discipline stick when you are staring at a slow query at 2 a.m.
Reconstruct the unit’s spine without looking back: how to read EXPLAIN ANALYZE, why row estimates cascade, how scan and join choice are driven by cost, what extended statistics fix, and the operational playbook for plan stability.
- 01What is the single most diagnostic number in an EXPLAIN ANALYZE plan, and why?
- 02How does the planner choose among Seq Scan, Index Scan, Bitmap Heap Scan, and Index Only Scan?
- 03Explain the row-estimate cascade and why it makes Nested Loop catastrophic.
- 04Why does the planner's independence assumption fail on correlated columns, and what fixes it?
- 05What is the generic-plan trap on prepared statements, how do you detect it, and how do you fix it?
- 06Describe the five-layer strategy for keeping plans stable across deploys.
If you reconstructed each answer from memory, you hold the unit’s spine: read EXPLAIN ANALYZE by comparing estimated to actual rows at every node; scan choice follows selectivity and join choice follows the outer-side estimate; the row-estimate cascade explains why a single bad leaf estimate poisons every decision above it; extended statistics replace the independence assumption for correlated columns; the generic-plan trap produces bimodal latency on skewed prepared statements; and plan stability is an operational discipline of fresh statistics, SSD-tuned cost constants, force_custom_plan, observability, and a plan-diffing deploy procedure.