Chapter 24 — Exercises

Load sql/generate_data.sql first. Use EXPLAIN ANALYZE throughout. (answer in Appendix) = worked solution in Answers. ⭐ = stretch.

Group A — Reading plans

24.1 Run EXPLAIN ANALYZE SELECT * FROM products WHERE price > 1500;. Identify the scan type, estimated vs actual rows, and total time. (answer in Appendix)

24.2 Explain the difference between EXPLAIN and EXPLAIN ANALYZE, and when to use each.

24.3 In a plan tree, which node runs first — the most indented or the least? Why read bottom-up? (answer in Appendix)

24.4 ⭐ Add BUFFERS (EXPLAIN (ANALYZE, BUFFERS)) to a query and explain what the buffer numbers tell you.

Group B — Scan types

24.5 Force/observe a Seq Scan (query a column with no index) and an Index Scan (an indexed column). Compare. (answer in Appendix)

24.6 What is a Bitmap Index Scan + Bitmap Heap Scan, and when does the optimizer prefer it over a plain index scan?

24.7 ⭐ Construct a query that produces an Index Only Scan (covering index). What makes it possible?

Group C — Join algorithms

24.8 Run EXPLAIN ANALYZE on a join of orders and customers. Which join algorithm did the optimizer pick? (answer in Appendix)

24.9 Drop the index on a join column, re-run, and see if the algorithm changes (e.g., to a hash join or a costly nested loop). Recreate the index.

24.10 ⭐ Describe when each join algorithm (nested loop, hash, merge) is the best choice.

Group D — Statistics

24.11 What does ANALYZE do, and why does a stale statistics state cause bad plans? (answer in Appendix)

24.12 After loading generate_data.sql, the script runs ANALYZE. Why is that important before benchmarking queries?

24.13 ⭐ Create a scenario with a large estimate-vs-actual rows mismatch (e.g., load data without ANALYZE), observe the bad plan, run ANALYZE, and watch it improve.

Group E — Diagnose & fix

24.14 Take a slow multi-table query, read its plan, identify the bottleneck node and red flag, apply a fix (index/ANALYZE/rewrite), and re-run to confirm. Document before/after. (answer in Appendix)

24.15 A query has a Sort node spilling to disk. Name two fixes.

24.16 ⭐ Rewrite a correlated-subquery query (Chapter 9) as a join, and compare the two plans.

Group F — Progressive project

24.17 Profile your three most important queries with EXPLAIN ANALYZE. For each, record the bottleneck, the fix, and the after-plan.

24.18 ⭐ Find one query where stale statistics or a missing index caused a bad plan, and fix it.

Self-check. If your instinct for any slow query is "run EXPLAIN ANALYZE, find the red flag, fix it, re-run to confirm" — never guessing — you can optimize anything. Next: scaling a single huge table.