Comma

EXPLAIN ANALYZE

EXPLAIN ANALYZE runs a PostgreSQL query and prints the plan the planner chose, with the real time, row count and loop count of every step. Plain EXPLAIN shows only the plan and its estimates. Adding ANALYZE executes the statement and shows what actually happened — the first thing to reach for when a query is slow.

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.id, sum(o.total)
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= '2026-10-01' AND o.status = 'paid'
GROUP BY c.id ORDER BY 2 DESC LIMIT 10;

Reading one line

->  Seq Scan on orders o  (cost=0.00..41250.00 rows=250000 width=14) (actual time=0.019..512.774 rows=231804 loops=1)
      Filter: ((created_at >= '2026-10-01'::date) AND (status = 'paid'::text))
      Rows Removed by Filter: 1768196
Part Meaning
-> and indentation A plan node, feeding the node it is indented under
cost=0.00..41250.00 Planner estimate: startup cost .. total cost, in abstract units
rows=250000 (first) Rows the planner expected, per loop
width=14 Estimated average row size in bytes
actual time=0.019..512.774 Milliseconds to first row .. to last row, per loop, including children
rows=231804 (second) Rows actually returned, per loop
loops=1 How many times the node ran
Rows Removed by Filter Rows read and thrown away by the Filter condition

The plan reads inside out: the most indented nodes run first and pass rows up to their parent. The top node's time is close to the query's Execution Time.

The two traps

Time includes the children. A Hash Join showing 701 ms contains the scans beneath it. To find where time is spent, subtract the children's time from the parent's — that is the node's self time.

Time and rows are per loop. Inside a nested loop, the inner side runs once per outer row. actual time=0.05 rows=1 loops=20000 is one second and 20,000 rows, not 0.05 ms. Parallel plans add a twist: under a Gather, loops count the leader plus every worker and time is an average per process.

Signs of a bad plan

You see It usually means
Seq Scan with a huge Rows Removed by Filter Whole table read to keep a few rows — an index on the filter columns may help
Estimated rows 10× or more off from actual Stale or thin statistics — run ANALYZE, or raise the column's statistics target
Sort Method: external merge Disk: … The sort outgrew work_mem and spilled to disk
Hash with Batches: greater than 1 The hash table outgrew work_mem and was split on disk
Inner side of a Nested Loop with loops= in the thousands The planner expected few outer rows; a hash join would likely be faster
Index Only Scan with many Heap Fetches The visibility map is stale — VACUUM the table
Heap Blocks: … lossy= The bitmap ran out of work_mem and rechecks whole pages

Options worth knowing

  • BUFFERS — shared hit/read/dirtied/written and temp pages per node. Default with ANALYZE since PostgreSQL 18.
  • FORMAT JSON — the same plan as structured JSON for tools.
  • TIMING OFF — keeps row counts but skips per-node timing, which lowers overhead on queries with millions of rows.
  • SETTINGS — lists planner settings that differ from the defaults.
  • auto_explain — logs the plan of every query slower than auto_explain.log_min_duration, so you catch slow queries in production without reproducing them.

Reading and sharing a plan

A 60-line plan is hard to scan and harder to discuss in a chat thread, where the indentation that holds it together disappears. Paste it into Comma's free Postgres EXPLAIN visualizer: it builds the tree with each node's self time, ranks where the time goes, and flags the problems above — in your browser, with nothing uploaded. Then publish the report as a link for the teammate who owns the table, and publish the plan again after the fix to keep the before and after as revisions of the same report.

Related