Reading an EXPLAIN Plan Without Fear

The Problem#

Slow queries are often fixed by guessing an index. That works until it does not. EXPLAIN ANALYZE shows what Postgres actually did and where time was spent.

Why It Matters#

Query performance issues become production incidents when dashboards, API endpoints, background jobs, or billing reports suddenly scan millions of rows. Execution plans turn the problem from "Postgres is slow" into specific evidence: which scan ran, how many rows were read, whether an index was used, and where time was spent.

Project Example#

A dashboard query filters by account_id, sorts by created_at, and limits to 50 rows. If the plan shows a sequential scan over millions of rows plus a sort, the right fix may be a composite index:

CREATE INDEX CONCURRENTLY idx_events_account_created
ON events (account_id, created_at DESC);

What To Look For#

Start with the highest-cost or slowest node, but do not stop there. Compare estimated rows with actual rows. If Postgres expected 50 rows and got 500,000, the problem may be stale statistics or a bad predicate, not just a missing index.

Look for:

• Seq Scan on large tables.
• Sort after reading many rows.
• Nested Loop where the inner side runs too many times.
• High Buffers reads from disk.
• Large gaps between estimated and actual rows.

Implementation Checklist#

• Run EXPLAIN (ANALYZE, BUFFERS) on the real query.
• Compare estimated rows with actual rows.
• Look for sequential scans on large tables.
• Check sort, hash, and nested-loop costs.
• Validate indexes with production-like data.
• Run ANALYZE after large data changes.
• Test partial and composite indexes against the exact production query.
• Remove unused indexes after verifying with pg_stat_user_indexes.

Production Notes#

Use CREATE INDEX CONCURRENTLY for large production tables so writes are not blocked. It takes longer and cannot run inside a transaction block, but it avoids a class of deployment incidents.

For managed Postgres, pair query plans with database metrics: CPU, disk I/O, lock waits, cache hit ratio, and connection pool wait time. The plan tells you the query shape; metrics tell you whether the database is under system pressure.

Common Mistakes#

• Reading cost numbers as milliseconds.
• Ignoring row-estimate errors caused by stale stats.
• Adding indexes without checking write overhead.
• Testing with tiny local datasets.
• Adding an index to fix one query while slowing every write path.
• Forgetting that parameter values can change the chosen plan.

Summary#

An execution plan is a map, not a mystery. Read row counts, scan type, buffers, and sorts before adding indexes.