EXPLAIN ANALYZE Discipline

Read query plans.

Overview

EXPLAIN ANALYZE discipline reads query plans before optimising. The plan tells you what the database is actually doing; tuning without reading the plan is guess-and-check that wastes engineer time.

• Read query plans. Per-query execution plan from EXPLAIN. The plan is evidence; intuition without plan is speculation.
• EXPLAIN vs EXPLAIN ANALYZE. EXPLAIN is the plan; ANALYZE adds actual execution data. Both have their place.
• Costs and rows. Per-node estimates and actuals. Mismatch between estimate and actual is a leading indicator of stale statistics.
• Index usage plus visualisers. Seq Scan, Index Scan, Bitmap Heap Scan all surface in the plan; pgMustard and dalibo make complex Postgres plans readable.

The approach

Three habits separate evidence-based optimisation from guess-and-check: read the plan first, run ANALYZE for real numbers, visualise when the plan gets complex.

• Plan before tune. Read EXPLAIN output before adding an index. The plan tells you whether the index will help.
• ANALYZE for real numbers. EXPLAIN ANALYZE actually runs the query and reports actual time per node. Plans without ANALYZE are estimates only.
• Visualise complex plans. pgMustard or dalibo for Postgres turns nested plan output into readable trees.
• Compare plans plus documented rationale. Before-and-after comparison measures impact; per-query rationale captures why the optimisation matters.

Why this compounds

Each plan read teaches the team a little more about the optimiser. Compounded across hundreds of queries, the team develops an actual model of how the database executes work.

• Faster queries. Evidence-based optimisation produces measurable query-time improvements. Random index-adding does not.
• Engineering culture. Plan-first culture produces evidence-based decisions. New engineers absorb the standard.
• Operational velocity. Reading plans replaces the guess-and-check loop. Optimisation work finishes in hours rather than days.
• Year-one investment, year-two habit. The first plan read is heavy lift. By year two, reading plans is reflexive on every slow-query investigation.