EXPLAIN, ANALYZE, BUFFERS: Three Different Questions

EXPLAIN estimates, EXPLAIN ANALYZE measures, and BUFFERS answers a third question neither of them can. That question — cache misses or a wrong plan — is invisible in timing, and it decides whether today’s slow query is slow again tomorrow. They are not three levels of detail on one output, and running the wrong one is how a plan investigation stalls.

EXPLAIN: what the planner intends

Plain EXPLAIN produces the chosen plan and the planner’s cost estimates. The statement is not executed. Nothing in the output is a measurement.

EXPLAIN SELECT id FROM orders WHERE customer_id = 4181;

Index Scan using orders_customer_id_idx on orders
  (cost=0.43..8.45 rows=1 width=8)
  Index Cond: (customer_id = 4181)

Two numbers matter here. cost=0.43..8.45 is startup cost and total cost, in arbitrary units anchored to seq_page_cost. They are comparable to each other and to nothing else — a cost of 8.45 does not correspond to any duration. rows=1 is the estimate, and it is the number that determined which plan you are looking at, including whether an index was used at all.

Use this variant when you want to know what the planner decided, without paying to run the query. On an expensive statement that is often the right first step.

EXPLAIN ANALYZE: what happened

Adding ANALYZE executes the statement and reports actuals alongside the estimates.

Index Scan using orders_customer_id_idx on orders
  (cost=0.43..8.45 rows=1 width=8)
  (actual time=0.019..0.021 rows=3 loops=1)

Now there is a measurement: actual time in milliseconds, rows actually produced, and loops, the number of times the node ran. The timing is reported per loop, not as a total: multiply by loops to get the time actually spent in the node.

BEGIN;
EXPLAIN ANALYZE DELETE FROM sessions WHERE expires_at < now();
ROLLBACK;

BUFFERS: where the work went

BUFFERS reports page access per node: shared hit for pages already in the buffer cache, shared read for pages fetched from the operating system, dirtied for pages the statement changed, and written for previously dirtied pages this backend evicted from the cache while running the statement. The last one is a signal about cache pressure, not about your statement’s writes.

The counts are reported for a node and all of its child nodes, and heap pages count toward them — which is how an index-only scan that still reads the table on most rows becomes visible.

  Buffers: shared hit=4 read=1180

This line separates two situations that look identical in timing. A query that reads 1,180 pages on a cold cache and a query that reads 1,180 pages on every execution because the plan is wrong take the same time on the first run and behave very differently in production. Without BUFFERS, the second run being faster tells you nothing about which case you have.

As of PostgreSQL 18, BUFFERS is enabled by default with ANALYZE. On 17 and earlier you have to request it, which is why so much older advice insists on it.

Which EXPLAIN variant to run

The choice is mostly about what the statement costs to execute and what you already know.

If the query is expensive or destructive, start with plain EXPLAIN. It answers the question that comes first anyway — which plan was chosen and what the planner expected — at no cost. A large share of investigations end here, because the estimate is visibly wrong and nothing else needs measuring.

If the plan looks reasonable and the query is still slow, run EXPLAIN ANALYZE. The gap between estimate and reality is the finding, and it only exists once the statement has run.

If the timing is inconsistent between runs, or the same query behaves differently in production than on your machine, the answer is in the buffer counts. Two plans with identical structure and very different page access are the normal explanation for a query that is fast in staging and slow in production, and no amount of staring at node types will show it.

One habit is worth adopting regardless: run the statement twice and compare. The first run populates the cache, the second reports the steady state. Quoting the first run as a measurement is one of the most common ways to report a number that does not survive contact with production.

What does an EXPLAIN plan not show?

The plan shows what was executed, not what was waited for. Time spent blocked on a lock appears as elapsed time in a node with no explanation attached. Planning time is reported separately from execution time, and it grows with the number of partitions the planner could not prune.

A single run also describes a single set of parameter values. Where the data is skewed, the next set may produce a different plan, and comparing the two runs as though they were the same query will mislead you. SQL Server has a name for the same dependency seen from the other side: the optimizer compiles a plan from the parameter values it sniffed at compile time.

Those limits are why the next part starts with the structure of a plan rather than with its numbers.