EXPLAIN Best Practices

Rigorous plan capture turns guesswork into reviewable evidence. Use this list when triaging slow queries or approving index PRs on PostgreSQL 18.4.

How to Use This List

• Paste full EXPLAIN (ANALYZE, BUFFERS, SETTINGS) output in tickets.
• Always capture before and after changes on the same dataset.
• Mark nodes where estimated rows diverge sharply from actual rows.

A - Capture Discipline

• Run ANALYZE on touched tables before comparing plans. Fair cardinality baseline.
• Use EXPLAIN (ANALYZE, BUFFERS, SETTINGS) for investigations. Timings, I/O, and GUC context.
• Execute twice on benchmarks; record the second run. Reduces cold-cache noise.
• Note role, search_path, and pool mode (session vs transaction). Plans differ by session shape.
• Store plans as JSON in CI for regressions. Diff-friendly artifacts.

B - Reading the Tree

• Find the highest actual time node first. Optimize the real bottleneck.
• Compare rows= estimates to actual rows= on driving nodes. Flags stats or correlation bugs.
• Check for sort/hash spill (external merge Disk, hash batches > 1). Memory tuning signal.
• Verify index names match intended migrations. Wrong index means wrong fix.
• Read Heap Fetches on index-only attempts. Visibility map may need vacuum.

C - Production Safety

• Avoid EXPLAIN ANALYZE on destructive SQL without ROLLBACK. ANALYZE executes the statement.
• Cap ad hoc analysis with statement_timeout. Protect shared primaries.
• Use read replicas or masked snapshots for heavy experiments. Isolate OLTP risk.
• Redact literals containing PII in shared logs. Parameterize in apps.
• Pair plans with pg_stat_statements totals. Confirm the query matters at scale.

D - Change Validation

• Require before/after plans in index PRs. Prove the planner uses the new index.
• Measure write amplification of new indexes. Inserts pay maintenance cost.
• Re-check plans after bulk ETL, not only after DDL. Stats drift silently.
• Document rejected indexes with plan evidence. Prevent repeat requests.
• Revisit plans after major version upgrades. Planner changes behavior.

FAQs

Related

• EXPLAIN Basics - Node vocabulary
• Bad Plan Case Studies - Failure patterns
• Statistics Best Practices - Stats hygiene
• Query Review Standards - Team process

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Y29kZWd1aWRlcy5pb3xjZ2lvMTA2N3wyMDI2MDc