# What Is EXPLAIN ANALYZE? — Reading a PostgreSQL Query Plan

Canonical: https://commareports.com/glossary/explain-analyze
Published: 2026-11-09

> EXPLAIN ANALYZE runs a PostgreSQL query and prints the plan with real times and row counts. What cost, rows, loops and actual time mean, why a node's time includes its children, the signs of a bad plan, and how to share one.

# EXPLAIN ANALYZE

**`EXPLAIN ANALYZE` runs a PostgreSQL query and prints the plan the
planner chose, with the real time, row count and loop count of every
step.** Plain `EXPLAIN` shows only the plan and its estimates. Adding
`ANALYZE` executes the statement and shows what actually happened — the
first thing to reach for when a query is slow.

```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT c.id, sum(o.total)
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= '2026-10-01' AND o.status = 'paid'
GROUP BY c.id ORDER BY 2 DESC LIMIT 10;
```

## Reading one line

```text
->  Seq Scan on orders o  (cost=0.00..41250.00 rows=250000 width=14) (actual time=0.019..512.774 rows=231804 loops=1)
      Filter: ((created_at >= '2026-10-01'::date) AND (status = 'paid'::text))
      Rows Removed by Filter: 1768196
```

| Part                         | Meaning                                                                    |
| ---------------------------- | -------------------------------------------------------------------------- |
| `->` and indentation         | A plan node, feeding the node it is indented under                         |
| `cost=0.00..41250.00`        | Planner estimate: startup cost .. total cost, in abstract units            |
| `rows=250000` (first)        | Rows the planner expected, per loop                                        |
| `width=14`                   | Estimated average row size in bytes                                        |
| `actual time=0.019..512.774` | Milliseconds to first row .. to last row, per loop, **including children** |
| `rows=231804` (second)       | Rows actually returned, per loop                                           |
| `loops=1`                    | How many times the node ran                                                |
| `Rows Removed by Filter`     | Rows read and thrown away by the `Filter` condition                        |

The plan reads inside out: the most indented nodes run first and pass
rows up to their parent. The top node's time is close to the query's
`Execution Time`.

## The two traps

**Time includes the children.** A `Hash Join` showing 701 ms contains
the scans beneath it. To find where time is spent, subtract the
children's time from the parent's — that is the node's _self time_.

**Time and rows are per loop.** Inside a nested loop, the inner side
runs once per outer row. `actual time=0.05 rows=1 loops=20000` is one
second and 20,000 rows, not 0.05 ms. Parallel plans add a twist: under a
`Gather`, loops count the leader plus every worker and time is an
average per process.

## Signs of a bad plan

| You see                                                      | It usually means                                                                  |
| ------------------------------------------------------------ | --------------------------------------------------------------------------------- |
| `Seq Scan` with a huge `Rows Removed by Filter`              | Whole table read to keep a few rows — an index on the filter columns may help     |
| Estimated `rows` 10× or more off from actual                 | Stale or thin statistics — run `ANALYZE`, or raise the column's statistics target |
| `Sort Method: external merge  Disk: …`                       | The sort outgrew `work_mem` and spilled to disk                                   |
| `Hash` with `Batches:` greater than 1                        | The hash table outgrew `work_mem` and was split on disk                           |
| Inner side of a `Nested Loop` with `loops=` in the thousands | The planner expected few outer rows; a hash join would likely be faster           |
| `Index Only Scan` with many `Heap Fetches`                   | The visibility map is stale — `VACUUM` the table                                  |
| `Heap Blocks: … lossy=`                                      | The bitmap ran out of `work_mem` and rechecks whole pages                         |

## Options worth knowing

- **`BUFFERS`** — shared hit/read/dirtied/written and temp pages per
  node. Default with `ANALYZE` since PostgreSQL 18.
- **`FORMAT JSON`** — the same plan as structured JSON for tools.
- **`TIMING OFF`** — keeps row counts but skips per-node timing, which
  lowers overhead on queries with millions of rows.
- **`SETTINGS`** — lists planner settings that differ from the defaults.
- **`auto_explain`** — logs the plan of every query slower than
  `auto_explain.log_min_duration`, so you catch slow queries in
  production without reproducing them.

## Reading and sharing a plan

A 60-line plan is hard to scan and harder to discuss in a chat thread,
where the indentation that holds it together disappears. Paste it into
Comma's free
[Postgres EXPLAIN visualizer](/tools/postgres-explain-viewer): it builds
the tree with each node's self time, ranks where the time goes, and
flags the problems above — in your browser, with nothing uploaded. Then
publish the report as a link for the teammate who owns the table, and
publish the plan again after the fix to keep the before and after as
revisions of the same report.

## Related

- [Postgres EXPLAIN visualizer](/tools/postgres-explain-viewer) — paste a plan, get the report
- [Flame graph](/glossary/flamegraph) — the other way to see where time goes
- [HTML report](/glossary/html-report) — what a shareable report is
- [Glossary](/glossary) — every term