Beginner: your first EXPLAIN ANALYZE
Put EXPLAIN in front of any query to see the plan the optimizer would use, without running it. Add ANALYZE to actually execute the query and get real timing alongside the estimates:
EXPLAIN ANALYZE
SELECT id, total FROM orders WHERE customer_id = 4471;
Index Scan using idx_orders_customer_id on orders
(cost=0.42..8.85 rows=6 width=12)
(actual time=0.021..0.028 rows=6 loops=1)
Index Cond: (customer_id = 4471)
Planning Time: 0.112 ms
Execution Time: 0.051 msINSERT, UPDATE, or DELETE in it. For a write query you don't want to commit, wrap it in a transaction and roll back afterward.Reading the plan in the right order
Plan nodes are printed top to bottom in the order you might read a sentence, but that is not execution order. Execution actually starts at the innermost, most indented node — the leaves — and flows upward. The topmost line is the last operation to run and is what actually produces your final result set.
Beginner: identifying scan types
The first word of most leaf nodes tells you how the data was accessed. This determines whether the query is touching the whole table or just the rows it needs — the full breakdown, including when each is chosen, is in Index Scan vs Table Scan.
| Node | Meaning |
|---|---|
Seq Scan | Reads the whole table |
Index Scan | Uses an index, then fetches matching rows |
Index Only Scan | Answers entirely from the index — no table fetch |
Bitmap Heap Scan | Builds a row map from an index, then reads the table by that map |
Intermediate: what the cost numbers mean
Every node carries a cost pair like cost=0.42..8.85. The first number is the estimated startup cost — the cost before the first row can be produced. The second is the estimated total cost to produce every row. These are arbitrary units for comparing plans against each other, calibrated so that reading one storage page costs roughly 1.0 — they are not milliseconds, and shouldn't be read as one.
rows=6 is the estimated row count that node will output, and width=12 is the estimated average row size in bytes. With ANALYZE, you additionally get actual time=0.021..0.028 (real startup and total time in milliseconds) and actual rows=6 loops=1 — the truth, measured.
Intermediate: reading join nodes
A join node has two child nodes feeding into it, and its algorithm name tells you how it's combining them — full detail on when each is chosen is in How SQL Optimizers Actually Work.
Hash Join (rows=1245 width=48)
Hash Cond: (o.customer_id = c.id)
-> Seq Scan on orders o (rows=2401880)
-> Hash (rows=1245)
-> Index Scan using idx_customers_region
on customers c (rows=1245)
Index Cond: (region = 'West')Read bottom-up within the join: the indexed customer scan runs first and builds a small hash table (1,245 rows), then every order is streamed through and probed against that hash table. This is why join order matters — building the hash from the smaller, already-filtered side keeps the whole join cheap.
Advanced: estimated vs actual rows
This is the single highest-value habit in reading a plan. Compare the estimated rows= against the actual rows= from ANALYZE at each node. When they're close, the optimizer had good information and likely chose a sound plan. When they're off by orders of magnitude, everything built on top of that node — every join order and algorithm choice above it — was decided using a wrong number.
Seq Scan on orders (rows=12 width=42)
(actual time=0.02..612.40 rows=1841902 loops=1)
Filter: (status = 'pending')Estimated 12 rows, actually returned 1.8 million — off by five orders of magnitude. That's the signature of stale statistics: whatever plan was chosen above this node was built on a number that was wrong by a factor of over 150,000. The fix is almost always to refresh statistics (ANALYZE the table) rather than to hand-tune the query.
Advanced: EXPLAIN with BUFFERS
Timing alone can be misleading — a query can look fast purely because everything it touched was already sitting in memory from a previous run. Adding BUFFERS reveals the actual I/O behind that timing:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 4471;
Index Scan using idx_orders_customer_id on orders
(actual time=0.021..0.028 rows=6 loops=1)
Buffers: shared hit=4shared hit=4 means all 4 pages needed came straight from cache — no disk read at all. If you instead saw shared hit=1 read=3, three of those pages had to come from disk, which is where most of the real-world latency in a "slow first run, fast second run" query actually comes from.
Before/after: the plan changes when the query does
The non-sargable predicate from Why SQL Queries Are Slow, seen through EXPLAIN ANALYZE directly — same intent, same table, rewritten to be sargable.
WHERE YEAR(order_date) = 2026
Seq Scan on orders
(rows=12000)
(actual rows=203841 loops=1)
Filter: (EXTRACT(year FROM order_date)
= 2026)WHERE order_date >= '2026-01-01'
AND order_date < '2027-01-01'
Index Scan using idx_orders_date
(rows=198442)
(actual rows=203841 loops=1)
Index Cond: (order_date >= ...)~11.9x faster notice the estimate also snapped much closer to actual once the predicate became sargable
Key takeaways
- EXPLAIN shows estimates only; EXPLAIN ANALYZE actually runs the query and adds real timing.
- Read plans from the innermost node outward — that's the true execution order.
- Cost numbers are relative units for comparing plans, not milliseconds.
- A large gap between estimated and actual rows is the clearest sign of a bad plan.
- BUFFERS reveals whether a fast query is fast because of caching, not because of the query itself.