Reading Query Plans: EXPLAIN ANALYZE Without the Guesswork

An endpoint takes four seconds. The query looks reasonable, so you add an index on the column in the WHERE clause. Still four seconds. You add another. Now writes are slower and reads aren't better.

You are optimizing blind. The database already knows exactly what it did and how long each part took. You just have to ask.

Why Plans Exist

SQL is declarative: you describe the result, and the planner decides how to get it. For any non-trivial query there are many valid strategies, and the planner picks one by estimating costs from statistics: roughly how many rows a table has, and how values are distributed.

Good statistics, good plan. Stale statistics, four-second endpoint.

1-- Estimates only. It's a prediction.
2EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
3
4-- Runs the query, reports real timings and real row counts.
5EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Reading One

1Seq Scan on orders  (cost=0.00..2891.00 rows=95 width=64)
2                    (actual time=0.021..38.442 rows=112 loops=1)
3  Filter: (customer_id = 42)
4  Rows Removed by Filter: 119888
5Execution Time: 38.501 ms

A plan is a tree of nodes. Read it inside-out: the most-indented nodes run first and feed the node above.

• cost: the planner's estimate in arbitrary units. It means nothing on its own, it exists so plans can be compared to each other.
• actual time: real milliseconds, written as first row..last row.
• rows=95 vs rows=112: estimated versus actual. This pair is the most useful thing on the line.
• loops: how many times the node ran.

Above, Rows Removed by Filter: 119888 tells the story alone: 120,000 rows read to return 112.

The Four Problems You'll Actually Find

A Seq Scan That Should Be an Index Scan

Seq Scan reads the whole table. That's genuinely right for small tables or queries returning most rows. But Seq Scan plus a large Rows Removed by Filter is a haystack read for one needle. Add the index and the same query drops from 38ms to 0.12ms, and Index Cond replaces Filter.

An Index That Exists But Isn't Used

Usually one of four things:

The column is wrapped in a function. An index stores created_at, not DATE(created_at):

1-- Index can't be used
2WHERE DATE(created_at) = '2026-09-03'
3
4-- Index works: the column is bare on the left
5WHERE created_at >= '2026-09-03' AND created_at < '2026-09-04'

A leading wildcard. LIKE '%shehadeh' can't use a B-tree. The index is sorted by prefix and you didn't give it one. A type mismatch, where a cast defeats the index. Or the table is small, and a sequential scan genuinely wins.

Estimates That Are Wildly Wrong

1Nested Loop  (cost=0.85..24.32 rows=3 width=128)
2             (actual time=0.03..2841.55 rows=48211 loops=1)

The planner expected 3 rows and got 48,211. It chose a nested loop because looping 3 times is cheap. Looping 48,211 times is not. With honest numbers it would have picked a hash join and finished in milliseconds.

Usually the statistics are stale, and ANALYZE orders; refreshes them. If two columns are correlated, the planner assumes independence and multiplies their selectivities into near-zero; CREATE STATISTICS teaches it the correlation.

The N+1, Seen From the Database

1Nested Loop  (actual time=0.05..1902.44 rows=1043 loops=1)
2  ->  Seq Scan on users  (actual time=0.01..0.18 rows=200 loops=1)
3  ->  Index Scan using idx_orders_user on orders
4        (actual time=9.4..9.5 rows=5 loops=200)

loops=200. The inner scan is fast, but it ran 200 times, so it cost 1.9 seconds. Each query looks fine in your logs. The total is what's killing you.

Turn on Buffers

Timings say it was slow. BUFFERS says why.

1EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;
2-- Buffers: shared hit=12 read=1436

shared hit is memory (nearly free), shared read is disk (expensive), and temp read/written means a sort spilled because work_mem was too small. 1,436 disk reads for 112 rows is the missing-index story again, measured in I/O. It is also why the query is fast on your laptop and slow in production.

A Practical Loop

Find what matters first. Sort pg_stat_statements by total time, not average. A 900ms query running twice a day matters less than a 12ms query running a million times.

Then for each: EXPLAIN (ANALYZE, BUFFERS) it, find the node with the largest actual time (multiplied by loops), decide which of the four problems it is, change one thing, and re-run. Adding three indexes and seeing an improvement teaches you nothing about which one worked.

For anything non-trivial, add FORMAT JSON and paste the result into a plan visualizer. On a twelve-node plan that's a thirty-second read instead of a five-minute one.

The Takeaway

• Read inside-out; the deepest node runs first.
• Compare estimated vs. actual rows. A huge gap is the root cause more often than anything else.
• Multiply actual time by loops. Always.
• Filter with a large Rows Removed is work you paid for and threw away.
• Turn on BUFFERS and treat shared read as the real cost.

The database is not a black box. It will tell you exactly where the time went, for free. Ask before you index.