Chapter 24 — Key Takeaways

The big idea

EXPLAIN shows how the optimizer will run your query; EXPLAIN ANALYZE shows what actually happened. Reading the plan turns "it's slow" into "here's exactly why" — the core of performance work. Diagnose → fix → verify; never guess.

EXPLAIN basics

  • EXPLAIN = plan + cost estimates (no execution).
  • EXPLAIN ANALYZE = runs it, reports actual rows/timings (wrap writes in a rolled-back transaction).
  • Add BUFFERS to see cache vs. disk reads.
  • Read the plan bottom-up (most-indented nodes run first). Each node: cost, estimated/actual rows, time.

Scan types

Node Meaning
Seq Scan read all rows — red flag on a big table returning few rows
Index Scan jump to matching rows via index
Index Only Scan answered from the index alone (fastest)
Bitmap Index + Heap Scan medium selectivity

Join algorithms

  • Nested Loop — small outer + indexed inner (bad if both large/unindexed).
  • Hash Join — large unindexed inputs (needs work_mem).
  • Merge Join — pre-sorted (or affordable-to-sort) large inputs.

Statistics drive the plan

The optimizer minimizes estimated cost using statistics (ANALYZE, run by autovacuum). Stale stats → bad plans, signaled by a big estimate-vs-actual rows mismatch. After bulk loads/mass updates, run ANALYZE. (Case Study 2: stale stats → nested loop over millions → ANALYZE fixed it.)

Red flags → fixes

Red flag Likely fix
Seq Scan on big table, few rows add an index (Ch. 23)
huge Rows Removed by Filter index the filter column (Case Study 1)
estimate ≠ actual rows (large gap) ANALYZE
Sort spilling to disk more work_mem, or a sorted index
Nested Loop over large inputs index inner side / let it hash-join

Optimize the bottleneck node (highest actual time), not the cheap ones.

The professional loop

EXPLAIN ANALYZE → find the red flag/bottleneck → apply the fix (index / ANALYZE / rewrite / work_mem) → re-run EXPLAIN ANALYZE to verify. (Case Study 1.)

You can now…

  • ☐ Read a plan tree bottom-up (cost, rows, time).
  • ☐ Identify scan types and join algorithms and when each is good/bad.
  • ☐ Spot the red flags and map each to its fix.
  • ☐ Explain statistics' role and fix bad plans with ANALYZE.
  • ☐ Diagnose a slow query and verify the fix.

Looking ahead

Chapter 25 — Table Partitioning. When even good indexes strain on a giant table, split it into partitions (range/list/hash) so the database prunes to the relevant chunk.

One sentence to carry forward: Read the plan, find the red flag, fix it, and re-read the plan to prove it — and when a plan looks insane, suspect stale statistics first.