Mastering PostgreSQL EXPLAIN ANALYZE: From Sequential Scans to Index Optimization

1. Decoding EXPLAIN (ANALYZE, BUFFERS)

EXPLAIN without ANALYZE only tells you what the planner intends to do, based on statistics collected by autovacuum's ANALYZE pass. It is a cost estimate, not a measurement, and cost units are arbitrary — they represent relative disk-page fetches and CPU cycles, not milliseconds. The moment you add ANALYZE, PostgreSQL actually executes the query (including any side effects of writes, which is why you should wrap DML in a transaction and roll it back when profiling), and the output gains actual time, actual rows and loops for every node in the plan tree.

BUFFERS is the option most people skip and shouldn't. It reports shared_hit (pages found in shared_buffers), shared_read (pages fetched from the OS/disk), and for write-heavy nodes, shared_dirtied and shared_written. A query that looks fast in actual time but shows a high shared_read count on a cold cache is deceptive — the first run pulls pages into the buffer cache, and every subsequent run looks artificially fast. Always run EXPLAIN (ANALYZE, BUFFERS) at least twice and compare, or you'll optimize for a cache-warm scenario that never happens in production under real eviction pressure.

The single most useful number in the whole output is the ratio between the planner's estimated rows and the actual rows returned by a node. When these diverge by an order of magnitude or more, the planner is working from stale or insufficiently granular statistics, and every cost-based decision downstream of that node — join order, join algorithm, whether to use an index at all — is built on a bad assumption. Fixing statistics (via ANALYZE, increasing default_statistics_target, or extended statistics for correlated columns) is often a higher-leverage fix than adding an index.

Read the plan tree from the innermost, most-indented node outward — that's execution order, not top-to-bottom. Each node's actual time is cumulative including its children, so a Nested Loop showing 800ms isn't necessarily an expensive join operator; it might just be summing an expensive child scan. Subtract child times from parent times manually, or use a visualizer, before you conclude which node is actually the bottleneck.

2. Identifying Sequential Scan Bottlenecks

A Seq Scan is not inherently a bug. For a table under a few thousand rows, or a query that legitimately needs most of the table's rows, a sequential scan beats an index scan because it reads pages in physical order with read-ahead, while an index scan does random I/O per matching row unless the table is well-clustered. The problem is when the planner chooses a Seq Scan on a large table for a selective predicate — that's the signal something is wrong: a missing index, a function wrapped around the filtered column defeating an existing index, or statistics that underestimate selectivity.

The classic trap is applying a function to the indexed column in the WHERE clause — `WHERE lower(email) = 'x'` cannot use a plain btree index on `email`, because the index stores raw values, not the transformed ones. PostgreSQL will happily fall back to a full Seq Scan with a Filter. The fix is either a functional index on `lower(email)`, or rewriting the predicate to avoid the transformation, e.g. citext for case-insensitive columns.