Everyone knows to run EXPLAIN ANALYZE. Rather fewer people know how to read the output, which is dense, nested, and — the part that trips people up — read from the inside out rather than top to bottom.
Here is the short version worth internalising.
Run it with the useful flags
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT o.id, c.name FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > now() - interval '7 days';ANALYZE actually executes the query and reports real timings rather than estimates. BUFFERS tells you how much data came from cache versus disk, which is the difference between a query that is slow and a query that is slow right now. SETTINGS shows any non-default planner configuration, which occasionally explains everything.
One warning: ANALYZE runs the query. On an UPDATE or DELETE, wrap it in a transaction you roll back.
Read it inside out
The plan is a tree. The most indented nodes execute first and feed their parents; the top line is the last thing that happens, not the first. So you start at the deepest node and work outwards.
Nested Loop (cost=0.29..4231.55 rows=1 width=64) (actual time=0.04..892.3 rows=48213 loops=1)
-> Seq Scan on orders o (cost=0.00..4180.00 rows=1 width=8)
(actual time=0.02..48.1 rows=48213 loops=1)
Filter: (created_at > (now() - '7 days'::interval))
Rows Removed by Filter: 951787
-> Index Scan using customers_pkey on customers c (actual time=0.01..0.01 rows=1 loops=48213)
Index Cond: (id = o.customer_id)The four numbers that matter
rows estimated vs rows actual. This is the one to check first, every time. Above, the planner expected 1 row from orders and got 48,213. That single misjudgement caused everything else: believing it had one row, it chose a Nested Loop, which then executed the inner index scan 48,213 times. A large gap here is the root cause of most bad plans, and the fix is usually stale statistics — run ANALYZE orders — or a correlation the planner cannot see, which may need extended statistics.
actual time. Note it is cumulative and per-loop. A node showing 0.01ms with loops=48213 cost roughly half a second in total, not a hundredth of a millisecond. Multiply before concluding a node is cheap.
Rows Removed by Filter. Nearly a million rows read and discarded, above. That is the signature of a missing index: the database is doing the filtering that an index should have done before the read.
Buffers. shared hit is cache, shared read is disk. A query that is fast in testing and slow in production often has an identical plan and a completely different hit-to-read ratio.
What the scan types tell you
Seq Scan reads the whole table. Not automatically bad — for a small table, or when returning a large fraction of rows, it is genuinely faster than an index. It is bad when paired with a selective filter and a big Rows Removed count.
Index Scan walks the index and fetches matching rows from the heap. Good for selective queries.
Index Only Scan answers entirely from the index without touching the table. The fastest option, available when every column you select is in the index. Watch the Heap Fetches figure — if it is high, your visibility map is stale and a VACUUM will help.
Bitmap Heap Scan sits between the two: it collects matching locations, sorts them, then reads the heap in physical order. Postgres chooses it when a query is selective enough to use an index but returns too many rows for repeated random access.
Join types, briefly
Nested Loop runs the inner side once per outer row. Excellent when the outer side is tiny, catastrophic when the estimate was wrong — as above. Hash Join builds a hash table from the smaller side; the usual choice for joining two substantial tables. If you see Batches: 4 rather than 1, the hash exceeded work_mem and spilled to disk, and raising work_mem for that session may transform the query. Merge Join requires both inputs sorted and shines when they already are.
What to ignore
The cost numbers are arbitrary planner units, not milliseconds. They are useful for comparing two plans for the same query and meaningless as an absolute measure. When ANALYZE gives you real timings, read those instead.
And do not tune a plan from a single execution. Run it a few times so caching stabilises, and check it against production-like data volumes — a plan chosen against ten thousand rows is often the wrong plan at ten million. Tracking which queries deserve this attention in the first place is a separate job, and pg_stat_statements is where it starts; our database observability guide covers that side.