Reading Postgres EXPLAIN (ANALYZE, BUFFERS)

The first time someone runs EXPLAIN ANALYZE on a slow query, they either panic at Seq Scan or celebrate Index Scan without reading the numbers. Both reactions are wrong often enough to hurt.

Start with actual time

explain.sql

1EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)2SELECT * FROM posts WHERE topic = 'rust' ORDER BY published_at DESC LIMIT 20;

ANALYZE runs the query; BUFFERS tells you whether you're hitting shared buffers or disk. Wall time and rows matter more than the operator name.

Seq scan vs index scan

🦀

Ferris' hot tip

Postgres may choose a seq scan when it expects to read a large fraction of the table anyway — an index lookup + heap fetch for half the rows is more expensive than one sequential pass. Small tables almost always seq scan.

Red flags in the plan

timeline

**Nested Loop** with huge row estimates on the inner side — missing index or stale stats.

**Sort** on millions of rows — consider an index that matches `ORDER BY`.

**Buffers: read=… written=…** dominated by `read` — you're IO-bound, not CPU-bound.

**Planning time** spikes — check for prepared-statement churn or pooler mode mismatches (see [pooling pitfalls](/blog/postgres-connection-pooling-pitfalls)).

Fix order

ANALYZE / autovacuum health — bad stats lie to the planner.
Indexes that match filter + sort columns.
Query shape — fewer round trips beats micro-optimizing operators.

EXPLAIN is a conversation with the planner, not a scoreboard.