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, the planner’s guess, with actual rows. A large gap often points to a bad plan choice. Watch for aSeq Scanwhere an index scan should be, and useBUFFERSto compare shared-buffer hits with misses. On PostgreSQL 18, plainEXPLAIN ANALYZEalready includes the buffer counts.
EXPLAIN vs EXPLAIN ANALYZE
Section titled “EXPLAIN vs EXPLAIN ANALYZE”EXPLAINprints the plan the planner would choose, with estimates, and does not run the query.EXPLAIN ANALYZEexecutes the query and adds the actual timing and row counts.
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:
BEGIN;EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status = 'x' WHERE id = 1;ROLLBACK;How do I read a plan node?
Section titled “How do I read a plan node?”Each line is a node, with two pairs of numbers:
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=4cost=start..total, the planner’s cost estimate in arbitrary units, not ms. The first number is startup cost, and the second is total.rows=1(in thecost=...pair), the planner’s estimated row count.actual time=start..total, real time in milliseconds, cumulative and including child nodes.rows=1(in theactual...pair), the actual rows returned.loops=N, how many times the node ran. Actual rows and time are per loop, so multiply byloopsfor the node’s total.
Reading inside out, starting with the deepest and most indented node, helps trace where rows originate, but it does not show the execution order. Execution is demand-driven from the top: a parent pulls rows from its children as it needs them, and node behavior varies. A nested loop reruns 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.
Because a parent node’s cost and actual time include its children, do not
add per-node times. That double-counts. Read the query’s total runtime from the
root, or topmost, node.
The signal that matters: estimated vs actual rows
Section titled “The signal that matters: estimated vs actual rows”A useful first check 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 to 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 large misestimate means the planner optimized for a different row count.
The usual causes are stale statistics, which you can address with ANALYZE, or
correlated columns that the planner estimates independently.
Seq scan vs index scan
Section titled “Seq scan vs index scan”- Seq Scan reads the whole table. It is correct when you need most of the rows or the table is small, but it can be a problem when you select a few rows from a big table.
- Index Scan walks an index to find matching rows and is cheap for selective lookups.
- Index Only Scan answers from the index alone when the visibility map says
the heap page is all-visible. Otherwise, it still visits the heap. Check the
Heap Fetches:line. A high count means you are paying for heap visits, which is common on a recently updated table. - Bitmap Index Scan + Bitmap Heap Scan sits in between and works well when a query matches a moderate fraction of rows.
BUFFERS tells you where the pages came from: shared hit means the page was
already in PostgreSQL’s shared buffers; shared read means it was not in those
buffers. The latter uses an OS read(), which may still be served from the OS
page cache rather than disk. A large shared read count still matters because
the plan is touching more data than a well-indexed plan would.
Why does AI-generated code write plan-blind SQL?
Section titled “Why does AI-generated code write plan-blind SQL?”Your AI coding agent can see your schema and 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 be misestimated and turn into a nested loop. It
writes SQL that is correct and idiomatic, and the planner may then choose a plan
the agent could not anticipate. The plan depends on your data distribution and
statistics at runtime, neither of which appears in the source.
How do I use plans routinely?
Section titled “How do I use plans routinely?”- Pull a plan for any query flagged as slow, not only when something breaks.
- Compare estimated to actual rows first, then investigate 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, addBUFFERSexplicitly. - Run
ANALYZEon the table before concluding that an index is missing. Stale statistics can produce many “missing index” symptoms.
What is the difference between EXPLAIN and EXPLAIN ANALYZE?
EXPLAIN shows the plan the planner would use, with estimated costs and row counts, without running the query. EXPLAIN ANALYZE actually executes the query and adds the real execution time and actual row counts, so you can compare the estimate to reality.
Why do estimated rows differ from actual rows in a plan?
The planner estimates from table statistics gathered by ANALYZE. Estimates drift when statistics are stale, when predicates are correlated in ways the planner cannot model, or when data changed since the last ANALYZE. A large estimate-vs-actual gap is the classic signal of a bad plan.
Is a Seq Scan always bad?
No. A sequential scan is the right choice when a query reads most of a table or the table is small. It is a problem when the query selects a few rows from a large table and an index scan would have been far cheaper.
What does the loops value mean in EXPLAIN ANALYZE?
loops is how many times that node executed, usually because it is the inner side of a nested loop join. The reported actual rows and time are per loop, multiply by loops to get the true total for that node.