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 read the buffer counts (on PostgreSQL 18, plain EXPLAIN ANALYZE already includes them) for 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.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, 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 is not 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 rows	Actual rows	What it means
~ actual	~ actual	Planner is well-informed; trust the plan
1–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 big misestimate means the planner optimized for a table that does not 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 does not, it still visits the heap, check the Heap Fetches: line. A high count means you are 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 does not know that orders has 40 million rows, that customer_id is not 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.
• 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.
• 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 can help

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 cannot model. A large gap is the classic bad-plan signal.
• Is a Seq Scan always bad?

  No. It is correct for small tables or queries that read most rows. It is 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.

Related

• How to identify slow queries in PostgreSQL
• Why did my PostgreSQL query suddenly get slow?
• What pg_stat_statements tells you (and what it does not)
• Product: DBGorilla docs