Chapter 24 — Quiz

13 questions. Answers at the bottom.

Multiple choice

Q1. EXPLAIN (without ANALYZE): - A) Runs the query and shows actual times - B) Shows the chosen plan and cost estimates without running it - C) Deletes the query plan - D) Is the same as EXPLAIN ANALYZE

Q2. EXPLAIN ANALYZE: - A) Only estimates - B) Actually runs the query and reports real rows/timings - C) Cannot be used on SELECT - D) Builds an index

Q3. A plan is read: - A) Top-down (top node first) - B) Bottom-up / inside-out (most indented nodes run first) - C) Left to right - D) Randomly

Q4. A Seq Scan on a large table returning few rows usually means: - A) The query is optimal - B) A missing index - C) A corrupt table - D) Too much memory

Q5. A large gap between estimated rows and actual rows suggests: - A) Stale statistics → run ANALYZE - B) A hardware fault - C) A syntax error - D) Nothing

Q6. Which join is best for two large, unindexed inputs? - A) Nested loop - B) Hash join - C) Merge join (without sorting) - D) Cross join

Q7. A nested loop over two large inputs is usually: - A) Optimal - B) A problem — often a missing index on the inner side - C) Impossible - D) Faster than a hash join

Q8. ANALYZE does what? - A) Rewrites queries - B) Updates table statistics the optimizer uses - C) Creates indexes - D) Deletes old rows

Q9. Rows Removed by Filter: 50000000 in a plan indicates: - A) Rows were deleted - B) The filter examined and discarded many rows (an index could pre-narrow) - C) A successful index scan - D) A sort

Q10. A Sort spilling to disk can be helped by: - A) Dropping the table - B) More work_mem, or an index supplying sorted rows - C) ANALYZE only - D) Removing the ORDER BY meaning

Q11. When optimizing, focus on: - A) The cheapest node - B) The node with the highest actual time (the bottleneck) - C) The top node only - D) The number of columns

True/False

Q12. You should diagnose slow queries by reading the plan, not by guessing. (True / False)

Q13. The optimizer always chooses the fastest possible plan regardless of statistics. (True / False)

Short answer

Q14. Describe the professional loop for fixing a slow query with EXPLAIN.

---

Answer key

Q1 — B. Plan + estimates, no execution.

Q2 — B. Runs the query; reports actual rows/timings (careful with writes).

Q3 — B. Bottom-up; the most-indented nodes feed their parents.

Q4 — B. Missing index — the classic smell.

Q5 — A. Stale statistics; run ANALYZE.

Q6 — B. Hash join (build/probe), no index needed.

Q7 — B. Usually a missing inner-side index; can be O(n×m).

Q8 — B. Refreshes the statistics the planner estimates from.

Q9 — B. The filter discarded many rows after reading them — an index on the filter column would help.

Q10 — B. Raise work_mem or provide a sorted index.

Q11 — B. Optimize the bottleneck (highest actual time), not the cheap nodes.

Q12 — True. Read the plan; never guess.

Q13 — False. It minimizes estimated cost using statistics; stale/missing stats → bad plans.

Q14. Run EXPLAIN ANALYZE to see the chosen plan and actual numbers; read it bottom-up to find the bottleneck node and the red flag (Seq Scan / estimate-actual mismatch / disk sort / big nested loop); apply the implied fix (add an index, run ANALYZE, rewrite the query, raise work_mem); then re-run EXPLAIN ANALYZE to confirm the plan changed and the time dropped. Diagnose → fix → verify, never guess.

Scoring: 11–13 you can optimize anything; 8–10 review scan/join types and red flags; below 8, do the diagnose-and-fix exercises hands-on.