Skip to main content

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 see 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.
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?​

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 loops=1)
  • 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 isn't 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 rowsActual rowsWhat it means
~ actual~ actualPlanner is well-informed; trust the plan
1–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 didn't need

A big misestimate means the planner optimized for a table that doesn't 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 doesn't, it still visits the heap — check the Heap Fetches: line. A high count means you're 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 doesn't know that orders has 40 million rows, that customer_id isn't 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.
  • Add BUFFERS to separate a CPU problem from an I/O problem.
  • 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 →

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 can't model. A large gap is the classic bad-plan signal.

Is a Seq Scan always bad? No — it's correct for small tables or queries that read most rows. It's 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.