Chapter 24 — Further Reading

Official reference (everyone)

  • PostgreSQL Docs: "Using EXPLAIN" — the definitive guide to reading plans, with worked examples of scan and join nodes. https://www.postgresql.org/docs/current/using-explain.html
  • PostgreSQL Docs: "Query Planning" and "Planner Cost Constants" — how the optimizer estimates and what knobs influence it.
  • PostgreSQL Docs: "Statistics Used by the Planner" and ANALYZE — the statistics behind Case Study 2.

Reading plans (everyone — this is a skill)

  • explain.depesz.com / explain.dalibo.com — paste an EXPLAIN ANALYZE plan and get a visual, annotated breakdown that highlights the slow nodes. Invaluable for learning to read plans.
  • "How to read a Postgres EXPLAIN plan" tutorials — several excellent walkthroughs; read one with a plan open beside it.
  • Markus Winand, use-the-index-luke.com — "Execution Plans" chapter — ties plans back to indexing (Chapter 23).

Deeper optimization (🔬 CS Student · 🏗️ DBA)

  • Join algorithms (nested loop / hash / merge) deep dives — how each works and its cost profile; ties to Chapter 28 internals.
  • "Why isn't Postgres using my index?" — the canonical troubleshooting list (function on column, type mismatch, low selectivity, stale stats).
  • Extended statistics (CREATE STATISTICS) — for correlated columns where default per-column stats mislead the planner.
  • work_mem, random_page_cost, and planner tuning — when defaults don't fit your hardware/workload.

Practice (everyone)

  • Run EXPLAIN ANALYZE on every nontrivial query you write for a week. Pattern-recognition for plans comes fast with reps.
  • PGMustard / pganalyze (tools) — automated plan analysis that suggests fixes; good for learning what experts look for.

Reference (this book)

  • Chapter 23 — Indexing: the most common fix the plan implies.
  • Chapter 28 — Database Internals: why scans/joins/VACUUM behave as they do.
  • Appendix G — EXPLAIN and Tuning Reference: plan-reading cheat sheet and red-flag→fix table.

Do, don't just read

  • Reproduce both case studies on generate_data.sql: the missing-index report (read Rows Removed by Filter, add the index, verify) and the stale-stats blowup (load data, see the estimate/actual gap, ANALYZE, verify).
  • Paste a plan into explain.depesz.com and let it point you at the slow node.
  • Build the diagnose→fix→verify habit on your own slow queries.

Next: Chapter 25 — Table Partitioning: scaling a single huge table.