How do you read a query execution plan (EXPLAIN/EXPLAIN ANALYZE) and find the actual bottleneck?

#EXPLAIN tells you the plan; EXPLAIN ANALYZE tells you the truth

EXPLAIN asks the query planner what it intends to do and shows you its cost estimates, without running anything. EXPLAIN ANALYZE actually executes the query — for real, including any side effects if it's a mutating statement — and adds actual timings and row counts alongside the estimates. The gap between those two numbers, estimated vs. actual, is usually the single most useful thing on the page: when they agree, the planner had good information; when they don't, the planner made a decision based on a guess that turned out to be wrong, and everything downstream of that node inherited the mistake.

#Reading the shape of the plan

A plan is a tree of nodes rendered as indented text. The execution order is bottom-up and inside-out: the most deeply indented nodes run first, and their output feeds the node directly above them, up to the root node at the top, which is what the client actually receives. Each node line carries the same shape:

NodeType  (cost=startup..total rows=N width=W) (actual time=startup..total rows=N loops=L)

• cost=startup..total — the planner's estimated cost in arbitrary units (roughly, page fetches), not milliseconds. startup is the cost before the node can produce its first row; total is the cost to produce all of them.
• rows=N (in the cost parens) — the planner's estimated row count out of this node.
• actual time=startup..total — real milliseconds, present only with ANALYZE.
• rows=N (in the actual parens) — the real row count, per execution.
• loops=L — how many times this node executed. A node nested inside a Nested Loop can run once per row of the outer side — its actual time and rows are per loop, so its real total contribution is actual time * loops, not the number printed.

#The checklist for finding the actual bottleneck

1. Compare estimated vs. actual rows at every node, not just the top. A large gap (an order of magnitude or more) means the planner's statistics are stale or the predicate is hard to estimate (correlated columns, a function applied to a column) — this is often the root cause of a bad plan higher up, because a wrong row estimate leads the planner to pick the wrong join algorithm.
2. Look for Seq Scan on a large table with a large Rows Removed by Filter. That's a full scan discarding almost everything it read — a strong candidate for a missing index on the filtered column.
3. Watch loop counts on the inner side of a Nested Loop. A nested loop is fine when the outer side returns few rows and the inner side is an indexed point lookup; it's a problem when the inner side is itself a scan that now runs once per outer row.
4. Check Sort nodes for Sort Method: external merge Disk:. That means the sort didn't fit in work_mem and spilled to disk — often fixable by raising work_mem for the session/query or reducing the row count earlier in the plan.
5. Run EXPLAIN (ANALYZE, BUFFERS) to see shared hit (served from cache) vs. shared read (physical I/O). A node dominated by reads is an I/O problem — more memory or a smaller working set helps; a node dominated by hits but still slow is a CPU/algorithm problem — that's where the join type or function calls matter more.
6. Compare Planning Time to Execution Time. If planning time is unexpectedly large relative to a fast execution, the issue may be too many partitions, too many indexes to consider, or expensive planner statistics — not the execution plan at all.

#A worked example

EXPLAIN ANALYZE
SELECT o.id, o.total_cents, o.created_at
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.email = 'jane.doe@example.com'
ORDER BY o.created_at DESC
LIMIT 20;

Before an index on customers.email:

Wrap long lines
Limit  (cost=18453.21..18453.26 rows=20 width=24) (actual time=142.318..142.324 rows=3 loops=1)
  ->  Sort  (cost=18453.21..18455.71 rows=1000 width=24) (actual time=142.316..142.318 rows=3 loops=1)
        Sort Key: o.created_at DESC
        Sort Method: quicksort  Memory: 25kB
        ->  Nested Loop  (cost=0.43..18429.85 rows=1000 width=24) (actual time=45.201..142.291 rows=3 loops=1)
              ->  Seq Scan on customers c  (cost=0.00..8459.00 rows=1 width=8) (actual time=45.180..142.240 rows=1 loops=1)
                    Filter: (email = 'jane.doe@example.com'::text)
                    Rows Removed by Filter: 499999
              ->  Index Scan using idx_orders_customer_id on orders o  (cost=0.43..9950.00 rows=1000 width=24) (actual time=0.020..0.038 rows=3 loops=1)
                    Index Cond: (customer_id = c.id)
Planning Time: 0.412 ms
Execution Time: 142.401 ms

Reading bottom-up: the Seq Scan on customers is the entire problem. It's estimated to return 1 row, found 1 row (the estimate happened to be right here), but it had to read and discard 499,999 rows to find it — that's Rows Removed by Filter: 499999, and it alone accounts for 142.24 of the query's 142.4 total milliseconds. The Index Scan on orders underneath the nested loop is fine (0.038ms) — it's already using idx_orders_customer_id. The fix is a missing index, not a rewrite of the join.

After adding CREATE INDEX idx_customers_email ON customers(email);:

Wrap long lines
Limit  (cost=8.60..8.65 rows=20 width=24) (actual time=0.045..0.048 rows=3 loops=1)
  ->  Sort  (cost=8.60..8.61 rows=3 width=24) (actual time=0.044..0.045 rows=3 loops=1)
        Sort Key: o.created_at DESC
        Sort Method: quicksort  Memory: 25kB
        ->  Nested Loop  (cost=0.86..8.58 rows=3 width=24) (actual time=0.030..0.038 rows=3 loops=1)
              ->  Index Scan using idx_customers_email on customers c  (cost=0.43..4.45 rows=1 width=8) (actual time=0.015..0.016 rows=1 loops=1)
                    Index Cond: (email = 'jane.doe@example.com'::text)
              ->  Index Scan using idx_orders_customer_id on orders o  (cost=0.43..4.10 rows=3 width=24) (actual time=0.010..0.018 rows=3 loops=1)
                    Index Cond: (customer_id = c.id)
Planning Time: 0.198 ms
Execution Time: 0.089 ms

Same query, same join, same result set — 142ms became 0.089ms because the one node doing a full scan now does an index lookup. Nothing else in the plan needed to change; the planner picked the same join strategy both times, it just had a cheap way to feed it once the index existed.

#A note on portability

This walkthrough uses PostgreSQL?'s EXPLAIN ANALYZE format because it's the most detailed and the most commonly asked about, but the reading strategy transfers: MySQL's EXPLAIN ANALYZE (8.0.18+) prints a similar cost/actual tree; MySQL's older EXPLAIN (no ANALYZE) gives only estimates and a rows/Extra column, where Using filesort and Using temporary are the rough equivalents of "external sort" and a poor row estimate, respectively. The specific syntax changes; "find the node where the estimate and reality disagree, or where a scan reads far more than it returns" does not.

🧠 Check your understanding
0/2 · 0/2 answered
1. In an EXPLAIN ANALYZE plan, a node shows `actual time=2.500..2.510 rows=50 loops=800`. What is that node's real total contribution to the query's execution time?
2.51 ms, since that is the actual time shown50 ms, because it returned 50 rows800 ms, because loops represents milliseconds directlyAbout 2,008 ms, because actual time is per loop and must be multiplied by the loop count
2. A plan shows `Seq Scan on events (cost=0.00..95000.00 rows=1200 width=40) (actual time=0.015..980.221 rows=3 loops=1)` with `Filter: (event_type = 'checkout')` and `Rows Removed by Filter: 4998000`. What does this most strongly suggest?
A large fraction of the table is being read and discarded to find very few matching rows — a strong candidate for an index on event_typeThe table needs to be replicated to another regionThe query should be rewritten to avoid using a WHERE clauseThe planner's estimate (1200) is reasonably close to reality (3), so no action is needed