# How to read a PostgreSQL EXPLAIN ANALYZE plan

> **Quick answer:** Run `EXPLAIN (ANALYZE, BUFFERS)` and read the tree from the
> innermost node outward.  For each node compare **estimated rows** (planner's
> guess) to **actual rows** (reality), a large gap is the root cause of most bad
> plans.  Watch for a `Seq Scan` where an index scan should be, and use `BUFFERS`
> to read the buffer counts (on PostgreSQL 18, plain `EXPLAIN ANALYZE` already
> includes them) for shared-buffer hits versus misses.

## EXPLAIN vs EXPLAIN ANALYZE

- `EXPLAIN` prints the plan the planner *would* choose, with **estimates**, and
  does not run the query.
- `EXPLAIN ANALYZE` actually **executes** the query and adds the real timing and
  **actual** row counts.

```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42;
```

Because `EXPLAIN ANALYZE` runs the statement, be careful with writes, wrap
`INSERT`/`UPDATE`/`DELETE` in a transaction and roll back:

```sql
BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status = 'x' WHERE id = 1;
ROLLBACK;
```

## How do I read a plan node?

Each line is a node, and the numbers come in two pairs:

```
Index Scan using orders_customer_id_idx on orders
  (cost=0.43..8.45 rows=1 width=64) (actual time=0.021..0.024 rows=1.00 loops=1)
  Index Searches: 1
  Buffers: shared hit=4
```

- `cost=start..total`, the planner's cost estimate in arbitrary units (not ms).
  The first number is startup cost, the second is total.
- `rows=1` (in the `cost=...` pair), the planner's **estimated** row count.
- `actual time=start..total`, real time in **milliseconds**, cumulative and
  including child nodes.
- `rows=1` (in the `actual...` pair), the **actual** rows returned.
- `loops=N`, how many times the node ran; **actual rows and time are per loop**,
  so multiply by `loops` for the true node total.

Reading **inside out**, deepest, most indented node first, is a useful habit
for tracing where rows originate, but it is not the execution order.  Execution is
demand-driven from the top: a parent pulls rows from its children as it needs
them, and node semantics vary.  A nested loop re-runs its inner child once per
outer row; a hash join fully builds its hash table from the inner side before it
probes with the outer.  Treat "deepest first" as a reading aid, not a rule for
when each node runs.

Because a parent node's `cost` and `actual time` are **inclusive of its
children**, do not add up per-node times, that double-counts.  Read the query's
total runtime off the **root** (topmost) node.

## The signal that matters: estimated vs actual rows

The single most useful thing in a plan is the gap between estimated and actual
rows:

| Estimated rows | Actual rows | What it means |
|---|---|---|
| ~ actual | ~ actual | Planner is well-informed; trust the plan |
| 1–10 | 1,000,000 | Planner is guessing low, it likely picked a nested loop or index scan that is now catastrophic |
| 1,000,000 | 5 | Planner is guessing high.  It may have chosen a Seq Scan or hash join it did not need |

A big misestimate means the planner optimized for a table that does not exist.
The usual causes are stale statistics (run `ANALYZE`), or correlated columns the
planner estimates independently.

## Seq scan vs index scan

- **Seq Scan** reads the whole table.  Correct when you need most of the rows or
  the table is small; a problem when you select a few rows from a big table.
- **Index Scan** walks an index to find matching rows, cheap for selective
  lookups.
- **Index Only Scan** answers from the index alone *when the visibility map says
  the heap page is all-visible*. For pages it does not, it still visits the heap, check the `Heap Fetches:` line.  A high count means you are paying for a plain
  index scan, which is common on a recently-updated table.
- **Bitmap Index Scan + Bitmap Heap Scan** sits in between, good when a query
  matches a moderate fraction of rows.

`BUFFERS` tells you where the pages came from: `shared hit` = already in
PostgreSQL's shared buffers; `shared read` = missed those buffers (an OS
`read()`, which may still be served from the OS page cache, not the disk).  A
huge `shared read` count still matters: the plan is touching far more data than
a well-indexed one would.

## Why does aI-generated code write plan-blind SQL?

Your AI coding agent (Claude Code, Cursor, Copilot) can see your schema and your
query, but it never sees the **plan**. It does not know that `orders` has 40
million rows, that `customer_id` is not indexed, or that a join will misestimate
and collapse into a nested loop.  It writes SQL that is correct and idiomatic, and then the planner does something the agent had no way to anticipate.  The plan
is a runtime artifact of your data distribution and statistics, not a property of
the source.

## How do I use plans routinely?

- Pull a plan for any query flagged as slow, not just when something breaks.
- Compare estimated to actual first; chase the largest gap.
- Read the buffer counts to separate a CPU problem from an I/O problem.  PostgreSQL 18 prints them automatically with `ANALYZE`; on 17 and earlier add `BUFFERS` explicitly.
- Re-run `ANALYZE` on the table before concluding an index is missing, stale
  stats fake a lot of "missing index" symptoms.

## How DBGorilla helps

DBGorilla connects read-only and gives your AI coding agent (Claude Code, Cursor)
the real plan for a query, the estimated-vs-actual rows, the scan types, the
buffer counts, so it can explain *why* the query is slow in plain language
instead of guessing from the SQL. It reads and explains; it does not run writes
against production. [Get started free →](https://app.dbgorilla.com/signup)

## FAQ

**What is the difference between EXPLAIN and EXPLAIN ANALYZE?**
`EXPLAIN` shows the estimated plan without running the query; `EXPLAIN ANALYZE`
executes it and adds real timing and actual row counts so you can compare
estimate to reality.

**Why do estimated rows differ from actual rows?**
Estimates come from statistics gathered by `ANALYZE`. They drift when stats are
stale or when predicates are correlated in ways the planner cannot model.  A large
gap is the classic bad-plan signal.

**Is a Seq Scan always bad?**
No.  It is correct for small tables or queries that read most rows.  It is a problem
when you select a few rows from a large table and an index scan would be cheaper.

**What does `loops` mean?**
How many times the node ran (often the inner side of a nested loop).  Actual rows
and time are per loop, multiply by `loops` for the node's true total.

## Related

- [How to identify slow queries in PostgreSQL](https://www.dbgorilla.com/learn/postgres/how-to-identify-slow-queries-in-postgresql/)
- [Why did my PostgreSQL query suddenly get slow?](https://www.dbgorilla.com/learn/postgres/why-did-my-postgres-query-suddenly-get-slow/)
- [What pg_stat_statements tells you (and what it does not)](https://www.dbgorilla.com/learn/postgres/what-pg-stat-statements-tells-you/)
- Product: [DBGorilla docs](https://www.dbgorilla.com/docs/)