---
title: "EXPLAIN, ANALYZE, BUFFERS: Three Different Questions"
description: "Which variant you ran decides whether the numbers in front of you are the planner's estimate or a measurement, and whether the pages came from cache."
url: https://sqlpress.com/blog/explain-analyze-buffers/
category: PostgreSQL
tags: ["execution-plans", "query-optimization", "performance"]
author: "Elias Rowe"
published: 2026-09-06T00:00:00.000Z
measured: false
---

# EXPLAIN, ANALYZE, BUFFERS: Three Different Questions

## Key takeaways

- EXPLAIN reports what the planner intends to do and costs it in arbitrary units; nothing is executed and no timing exists.
- EXPLAIN ANALYZE runs the statement, which means it also runs your INSERT, UPDATE or DELETE unless you wrap it in a transaction you roll back.
- BUFFERS reports pages read and hit, which is the only way to tell a cold cache from a genuinely expensive plan.
- Start with plain EXPLAIN when the statement is expensive or destructive: the estimate is often visibly wrong and nothing else needs measuring.

## Test environment

- System: PostgreSQL 18
- System: SQL Server 2022
- Workload: Illustrative (examples verified, nothing timed)
- Notes: PostgreSQL is the subject; BUFFERS became the default for EXPLAIN ANALYZE in PostgreSQL 18 and earlier releases require it explicitly. SQL Server appears only as a comparison on parameter skew.

`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.

```sql title="explain.sql"
EXPLAIN SELECT id FROM orders WHERE customer_id = 4181;
```

```text
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](/blog/index-not-used-estimate-not-index/).

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.

```text
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.

:::danger
`EXPLAIN ANALYZE` executes the statement. On an `UPDATE` or `DELETE` it performs
the write. Wrap it in a transaction you roll back.
:::

```sql title="safe-analyze.sql"
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.

```text
  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.

## FAQ

### Does EXPLAIN ANALYZE actually execute the query in PostgreSQL?

Yes. EXPLAIN ANALYZE runs the statement it is given, which means an INSERT, UPDATE or DELETE performs its write. Plain EXPLAIN executes nothing and reports only the plan the planner chose, with no timing in the output. To inspect a destructive statement safely, wrap the EXPLAIN ANALYZE in a transaction and roll it back.

### What does BUFFERS add to a PostgreSQL EXPLAIN plan?

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 it. Those counts separate a query that was slow because the cache was cold from one that reads the same pages on every execution because the plan is wrong. The two situations look identical in timing.

### Is BUFFERS enabled by default in PostgreSQL 18?

Yes. As of PostgreSQL 18, BUFFERS is enabled by default with ANALYZE, so EXPLAIN ANALYZE already reports page counts. On PostgreSQL 17 and earlier it has to be requested explicitly, which is why so much older advice about reading plans insists on adding it.

### What does the cost number in a PostgreSQL EXPLAIN plan mean?

Cost is printed as a startup cost followed by a total cost, in arbitrary units anchored to seq_page_cost. Those units are comparable to each other and to nothing else: a cost of 8.45 does not correspond to any duration. A duration appears only once the statement has actually been executed with ANALYZE.

## Sources

- [PostgreSQL 18 documentation — EXPLAIN](https://www.postgresql.org/docs/18/sql-explain.html) (docs)
- [PostgreSQL 18 documentation — Using EXPLAIN](https://www.postgresql.org/docs/18/using-explain.html) (docs)
- [PostgreSQL 18 release notes](https://www.postgresql.org/docs/18/release-18.html) (release notes) — BUFFERS became the default for EXPLAIN ANALYZE in this release.
- [PostgreSQL 18 documentation — Table Partitioning](https://www.postgresql.org/docs/18/ddl-partitioning.html) (docs) — Planning time grows with the partitions left after pruning.
- [Microsoft Learn — Query Processing Architecture Guide](https://learn.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide) (docs) — Parameter sensitivity — SQL Server sniffs parameter values at compile time.