Query Plansexplainexplain analyzebuffersestimated rowsactual rows

Reading EXPLAIN ANALYZE

Read a plan from the innermost node outwards, compare estimated rows to actual rows at every node, and look for three tells — a huge Rows Removed by Filter, a high loops count, and an estimate that is off by an order of magnitude.

▶ Interactive Try queries Interview question See how this works internally →

Progress

What each line says

EXPLAIN prints the chosen plan with estimates; EXPLAIN ANALYZE runs it and adds actuals; BUFFERS adds page-cache hits and reads. Each node line shows (cost=startup..total rows=N width=W) — startup is the cost before the first row can be emitted, total is the cost for all rows, both in the abstract cost units, not milliseconds — and, with ANALYZE, (actual time=first..last rows=N loops=L).

loops matters: the actual rows and time are per loop. An inner node with rows=3 loops=10000 produced 30,000 rows and ran ten thousand times. Multiply before you judge.

A plan, annotated

Limit  (cost=145.19..145.20 rows=5) (actual rows=5 loops=1)
  ->  Sort  (cost=145.19..146.21 rows=410) (actual rows=410 loops=1)        ← pipeline breaker
        Sort Key: (count(*)) DESC
        ->  HashAggregate  (rows=410) (actual rows=410)                      ← memory ∝ groups
              Group Key: u.name
              ->  Hash Join  (rows=900) (actual rows=1442)                   ← estimate 900, actual 1442
                    Hash Cond: (u.id = o.user_id)
                    ->  Seq Scan on orders o  (rows=960) (actual rows=1442)
                          Filter: (status = 'paid')
                          Rows Removed by Filter: 1758                       ← more removed than kept
                    ->  Hash  (rows=900)
                          ->  Seq Scan on users u  (rows=900) (actual rows=900)

Read from the inside out

Execution starts at the deepest leaves and flows upward, so that is the order to read. Find the leaf that produces the most rows or takes the most time. Ask whether it should — a Seq Scan on a big table with a selective filter should be an Index Scan; an Index Scan returning half the table should be a Seq Scan. Then look at what happens to those rows on the way up: does a join multiply them? does a Sort materialise them? does a Limit at the top throw almost all of them away?

The three tells

Rows Removed by Filter ≫ rows kept: the scan read far more than it needed. A missing or unusable index. loops in the thousands: a correlated subquery or an unindexed nested loop. Rewrite as a join, or index the inner side. Estimated rows off by 10× or more: statistics are stale (ANALYZE), or the planner cannot see a correlation between columns (CREATE STATISTICS), or the predicate is something it cannot estimate (a function). Every decision above that node was made on wrong information.

And one about IO: with BUFFERS, shared read counts pages that came from disk and shared hit those from cache. A query whose reads dwarf its hits on every run has a working set larger than memory — an index that reduces pages touched, or more RAM, is the fix.

Cost is not time

Cost units are relative, calibrated so that one sequential page read is 1.0. A plan with cost 50,000 is not fifty seconds; it is fifty thousand times the cost of one page read, whatever that is on your hardware. Compare costs between plans of the same query, never across queries or machines. The actual time from ANALYZE is real, but it is a single run with a warm or cold cache — run it twice.

Key points

Read inside out; rows and time are per loop, multiply by loops.
Tells: Rows Removed by Filter ≫ kept (missing index), high loops (correlated subquery / unindexed loop), estimate off by 10× (statistics).
BUFFERS shows whether the working set fits in cache.
Cost is relative; actual time is real but a single sample.

Why is this query slow?

Six real complaints. Read the plan, commit to a cause, then compare the rewritten version. Two of the six are correctness bugs, not performance bugs.

The list page got slow as the table grew

“Showing one customer’s orders took 20 ms in January and 4 seconds now. Nothing changed in the code.”

SELECT id, total, status, created_at
FROM orders
WHERE user_id = 1
ORDER BY created_at DESC
LIMIT 20

(cost=59.38 rows=20) (actual rows=20 loops=1)

-> (cost=59.38 rows=960) (actual rows=149 loops=1)

Sort Key: created_at DESC

-> (cost=54.00 rows=960) (actual rows=149 loops=1)slowest

Filter: user_id = 1

Rows Removed by Filter: 3,051

Nodes

Pages read

Limit

Estimated rows: 20
Actual rows: 20
Estimated cost: 59.38
Time: 0.000 ms

#	id	total	status	created_at
1	2074	6909.22	shipped	2026-01-16 06:50:28
2	808	7182.89	shipped	2026-01-16 06:34:04
3	2024	7267.5	shipped	2026-01-14 05:05:12
4	2054	1473.91	pending	2026-01-13 05:21:07
5	1396	4860.09	shipped	2026-01-12 21:41:08

Showing 5 of 20 rows.

Scenario

1/3 · Plan

Try it in the playground

EXPLAIN ANALYZE
SELECT u.name, count(*) FROM orders o JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid' GROUP BY u.name ORDER BY 2 DESC LIMIT 5;

Click the nodes in the plan tab. →

When to use — and when not

Use it when

Every slow query, before touching anything.
Before and after every index you add.

Avoid it when

On a production write statement with side effects — EXPLAIN ANALYZE executes it. Wrap in BEGIN … ROLLBACK.

Failure modes

Reading the top node and stopping.
Judging an inner node without multiplying by loops.
Adding an index before reading the plan, then adding another when the first did nothing.