Skip to content

How to read a PostgreSQL EXPLAIN ANALYZE plan

Last updated

View as Markdown

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 a Seq Scan where an index scan should be, and use BUFFERS to compare shared-buffer hits with misses. On PostgreSQL 18, plain EXPLAIN ANALYZE already includes the buffer counts.

  • EXPLAIN prints the plan the planner would choose, with estimates, and does not run the query.
  • EXPLAIN ANALYZE executes 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;

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=4
  • cost=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 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 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 rowsActual rowsWhat it means
~ actual~ actualPlanner is well-informed; trust the plan
1 to 101,000,000Planner is guessing low. It likely picked a nested loop or index scan that is now catastrophic
1,000,0005Planner 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 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.

  • 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, add BUFFERS explicitly.
  • Run ANALYZE on 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.