Reading Postgres Query Plans: EXPLAIN (ANALYZE, BUFFERS) Without Crying

SQLFormat — Free Online SQL Formatter, Beautifier & Minifier Guides · Updated 2026-10-01 · All guides

EXPLAIN alone tells you what Postgres plans to do, which is often the lie. EXPLAIN (ANALYZE, BUFFERS) tells you what it did — actual rows, actual time, actual shared-buffer reads, and the nodes it skipped entirely. That's the one you debug with. Everything below is Postgres 16 syntax; MySQL's EXPLAIN ANALYZE arrived in 8.0.18 and is a coarser cousin.

The invocation

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT o.id, o.total
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE u.region = 'eu' AND o.created_at >= '2026-09-01'
ORDER BY o.created_at DESC
LIMIT 20;

ANALYZE runs the query. Never point it at an INSERT/UPDATE/DELETE against prod unless you want the side effects (wrap in BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;).
BUFFERS adds the line that turns "slow" into "why slow": shared block reads per node.
TIMING OFF trims per-node overhead when you only care about rows and buffers; use FORMAT JSON for machine-readable plans.
Plan text is deeply nested and right-aligned — paste it into the formatter set to indent style before you read it. Humans parse plans ~2x faster when the node tree is indented instead of the default flat right-aligned dump; see below.

Read the tree inside-out, bottom-up

Limit (rows=20 actual=20 loops=1) (time=1.84ms)
  ->  Sort (rows=1,043 actual=1,043) (time=1.72ms)
        Sort Method: quicksort  memory: 312kB
        ->  Nested Loop (rows=1,043 actual=1,043)
              ->  Index Scan using idx_users_region on users u
                    (rows=980 actual=1,012) (time=0.31ms)
              ->  Index Scan using idx_orders_user_time on orders o
                    Index Cond: (created_at >= '2026-09-01')
                    (rows=1 actual=1) (loops=1,012)

Time is cumulative-with-children. And loops matters: actual=1 × loops=1,012 means ~1,012 rows total through that inner scan. Multiply rows by loops before judging anything.

Red flag #1: estimated ÷ actual rows

The planner picks join order and strategy from row estimates. When estimates are wrong, everything downstream is wrong. The ratio test:

Within 2x: fine, believe the plan.
10x+ off: stop tuning the displayed problem and fix the estimate. Run ANALYZE tablename; first — stale statistics cause the majority of bad plans on busy tables. Still off? Look for correlated columns or expression filters (WHERE lower(email) = ? without an expression index, or CREATE STATISTICS on the column pair); the planner assumes independence and multiplies filter selectivities, so two 10% filters "become" 1% and it picks a nested loop where a hash join belonged.

If you fix one thing per slow query, fix the rows ratio. A "wrong scan type" is almost always a symptom of the wrong row estimate one level up.

Scan types and the decision rules

Seq Scan — reads every heap page. Not a failure: on small tables or truly-unfiltered reads it's the fastest option, and the planner chooses it correctly most of the time. It's a red flag only when the filtered result is a small fraction of the table and an index exists. Then either the predicate isn't indexable (WHERE created_at::date = ? — wrap the value, not the column: created_at >= '2026-10-01' AND created_at < '2026-10-02') or the table is so bloated with dead tuples that autovacuum lags; check pg_stat_user_tables.

Index Scan — walks the B-tree, then fetches heap rows one at a time in index order. Wins when you need order (ORDER BY ... LIMIT — the Limit node eating 20 of 1M rows is exactly what index scans are for) or when selectivity is high and rows are scattered. Loses when you need a large fraction of the table: random heap I/O beats no one.

Bitmap Heap Scan (+ Bitmap Index Scan / BitmapAnd) — collect matching page numbers, sort them, read the heap in page order, each page once. This is the planner's answer to "too many rows for index scan, too few for seq scan," and to compound predicates (BitmapAnd of two single-column scans). If you're debating adding a composite index, check whether bitmap-and is already doing the job cheaply.

The one-line rule: Index scan for few-and-ordered, bitmap for many-and-unordered, seq for small-or-everything. If the plan contradicts that, the stats are lying, not the rule.

Red flag #2: BUFFERS without a plan node

BUFFERS shows lines for nodes that never executed:

  ->  Materialize  (rows=0 actual=0 loops=0)
        Buffers: shared hit=0

loops=0 means the inner side of a parameterized nested loop never ran because the outer side returned nothing. Great — that explains why the "expensive" subtree costed 4,200 but took 0ms. Conversely, check total Buffers: shared hit=X read=Y at the top: read is blocks pulled from disk/OS cache — if read dominates and hit (found in shared_buffers) is tiny, the query is I/O-bound and the fix is caching or a covering index (INCLUDE columns), not more CPU-side tuning.

Red flag #3: spills — `Sort Method: external merge`

  Sort  (rows=48,211 actual=49,533)
        Sort Method: external merge  disk: 6,144kB
        Buffers: temp read=742 written=751

The sort didn't fit in work_mem (per-node-per-backend budget — eight concurrent sorts can each get the full work_mem), so Postgres chunked to temporary files and merged. The fix order: (1) does the query actually need those rows? add the filter earlier or LIMIT where semantics allow; (2) is there an index that provides the order, killing the sort outright (ORDER BY matching an index scan is the cheapest sort there is); (3) raise work_mem for the session or role — SET work_mem = '64MB'; is real and reversible — never globally. HashAggregate/Hash Join spill the same way and show Batches: N — batches > 4 is the same smell.

EXPLAIN vs auto_explain

auto_explain covers the common case where nobody knows which query is slow. Load it via shared_preload_libraries = 'auto_explain', set auto_explain.log_min_duration (say 100ms) and auto_explain.log_analyze = on, and every query over the threshold logs its own (ANALYZE, BUFFERS) plan. No per-query instrumentation, works on ORM SQL you can't edit. Cost: log_analyze runs every qualifying query for real — gate it by log_min_duration, not enthusiasm. pg_stat_statements tells you which query; auto_explain tells you why.

Why formatting matters here

Plans nest; unread plans get skipped. Indenting both the query and the pasted plan preserves the node tree as visual structure, and the Postgres formatter route runs client-side — production query text never leaves the browser, which matters precisely for the queries worth explaining. Same discipline as best practices: format at paste time.

FAQ

Why is my query slow only in production? Estimates. Prod has real table sizes, real skew, real concurrent work_mem pressure; dev has none of the three. EXPLAIN (ANALYZE, BUFFERS) against a prod-shaped dataset or the answer is fiction.

Does EXPLAIN ANALYZE run triggers? On DML, yes — the statement actually executes, triggers and all. ROLLBACK undoes data, not side effects like NEXTVAL, advisory locks, or anything a trigger sent to a queue. Test against a replica when you can.

The plan says Index Scan but it's still slow. Now what? Look at Buffers read=. An index scan that visits 40,000 heap pages one at a time is a random-I/O machine — the index is real, the table order is the problem. Consider a covering index (INCLUDE) or clustering on the hot access pattern, and re-read the BUFFERS line before tuning anything else.