TopolearnGuessing at slow queries wastes days; the plan tells you what is actually happening.
EXPLAIN shows the plan the optimiser chose: a tree of nodes, each producing rows for its parent. EXPLAIN ANALYZE actually runs the query and adds real timings and row counts. Because it executes the statement, wrap data-modifying queries in BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;.
Each node line looks like:
Index Scan using orders_customer_id_idx on orders
(cost=0.43..8.45 rows=3 width=48) (actual time=0.020..0.024 rows=2 loops=1)cost=startup..totalis in arbitrary planner units (roughly, sequential page reads). Use it to compare plans, not as milliseconds.rowsis the estimate;actual ... rowsis reality.actual timeis in milliseconds, per loop: multiply byloops.- Child nodes are indented below their parent; execution data flows upward.
Common nodes: Seq Scan (read the whole table), Index Scan, Index Only Scan (answered from the index alone), Bitmap Heap Scan (collect many matches from an index, then read pages in order), Nested Loop, Hash Join, Merge Join, Sort, HashAggregate.
How to read a slow plan:
1. Find the nodes where actual time jumps. 2. Compare estimated and actual rows. An estimate off by 100x usually means the planner chose a bad join method or order; the cause is often stale or insufficient statistics. 3. Look for Seq Scan on large tables behind a selective filter (missing index), Rows Removed by Filter in the millions, or a Sort spilling to disk (Sort Method: external merge).
Add BUFFERS (EXPLAIN (ANALYZE, BUFFERS)) to see how many pages came from cache versus disk. Tools like explain.dalibo.com visualise large plans.
Example
EXPLAIN ANALYZE
SELECT c.name, SUM(o.total)
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.ordered_at >= '2026-03-01'
GROUP BY c.name;
-- HashAggregate (cost=245.1..247.6 rows=200 width=64)
-- (actual time=4.91..4.95 rows=180 loops=1)
-- Group Key: c.name
-- -> Hash Join (cost=38.5..236.3 rows=1760 width=38)
-- (actual time=0.61..4.12 rows=1802 loops=1)
-- Hash Cond: (o.customer_id = c.id)
-- -> Seq Scan on orders o (cost=0.0..193.0 rows=1760 width=14)
-- (actual time=0.01..3.02 rows=1802 loops=1)
-- Filter: (ordered_at >= '2026-03-01 00:00:00+00')
-- Rows Removed by Filter: 8198
-- -> Hash (actual time=0.55..0.55 rows=1000 loops=1)
-- -> Seq Scan on customers c (...)
-- Planning Time: 0.31 ms
-- Execution Time: 5.10 ms
-- Safely analyse a write
BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'shipped' WHERE id = 102;
ROLLBACK;Remember
- EXPLAIN shows the chosen plan; EXPLAIN ANALYZE runs it and reports reality
- Costs are relative units; actual time is ms per loop
- Big gaps between estimated and actual rows point to statistics problems
- EXPLAIN ANALYZE executes writes; wrap them in a rolled-back transaction
Watch out for
- Running EXPLAIN ANALYZE on a DELETE in production and actually deleting the rows.