Every PostgreSQL developer eventually hits a wall: a query that worked fine in staging crawls in production. The knee-jerk reaction is to add an index. But indexes are not magic. In my first week as a database engineer at a fintech, I added a composite index on a 50-million-row table only to watch the query get slower. The senior DBA didn't yell. They just ran EXPLAIN ANALYZE and showed me exactly where I went wrong.
EXPLAIN ANALYZE is the debugging tool for query performance. It shows you not only the plan the optimizer chose, but also what actually happened when the query ran. This post walks through reading its output, spotting common issues, and fixing them — with real examples.
The Anatomy of EXPLAIN ANALYZE Output
Let's start with a simple query on a `orders` table with 1 million rows:
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'shipped' AND created_at > now() - interval '7 days';Seq Scan on orders (cost=0.00..20000.00 rows=50000 width=120) (actual time=0.023..185.342 rows=48231 loops=1)
Filter: ((status = 'shipped'::text) AND (created_at > (now() - '7 days'::interval)))
Rows Removed by Filter: 951769
Planning Time: 0.089 ms
Execution Time: 187.456 msEach line is a plan node. The top node is the outermost operation. Here, it's a sequential scan on `orders`. The numbers in parentheses are: estimated startup cost, estimated total cost, estimated rows, estimated row width. Then the actual values from execution: actual time (startup..total), actual rows, and loops.
The key insight: estimated rows (50000) vs actual rows (48231) is close — good statistics. But 187 ms for 50k rows is slow because it scanned 1 million rows and filtered 951,769 of them. That's a full table scan. For a 1M row table, this is a red flag.
Always check the 'Rows Removed by Filter' number. If it's orders of magnitude larger than the returned rows, you're reading a lot of data you don't need. That's a sign to add an index.
When Indexes Lie: A War Story
I once had a reporting query that joined three tables and took 30 seconds. The team had added indexes on all foreign keys. But EXPLAIN ANALYZE showed something odd:
Nested Loop (cost=0.42..15000.00 rows=1000 width=250) (actual time=0.050..28000.00 rows=900 loops=1)
-> Index Scan using idx_orders_user_id on orders o (cost=0.42..100.00 rows=1000 width=150) (actual time=0.030..0.050 rows=900 loops=1)
-> Index Scan using idx_users_id on users u (cost=0.00..14.00 rows=1 width=100) (actual time=31.00..31.00 rows=1 loops=900)Look at the inner Index Scan: loops=900. That means for each of the 900 rows from the outer scan, PostgreSQL did another index lookup. That's 900 index scans, each taking 31 ms. The actual time for the inner node is 31 ms per loop, but summed across loops it's 27,900 ms. The problem? The index `idx_users_id` was on `user_id`, but the join was on `users.id`. The index was on the wrong column!
The fix was simple: drop the index and create one on `users.id`. The query dropped to 200 ms.
Always check the loops count on nested loop inner nodes. If loops > 100 and each loop takes > 1 ms, you have an N+1 problem in your database.
Reading the Buffers: EXPLAIN (ANALYZE, BUFFERS)
Time is not the only metric. I/O matters. Use `EXPLAIN (ANALYZE, BUFFERS)` to see how many buffers (8KB pages) each node read, hit, or dirtied.
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'shipped';Seq Scan on orders (cost=0.00..20000.00 rows=50000 width=120) (actual time=0.023..185.342 rows=48231 loops=1)
Buffers: shared hit=1000 read=500
Planning Time: 0.089 ms
Execution Time: 187.456 msHere, 1000 buffers were found in shared buffers (cache hits), and 500 had to be read from disk. If `read` is high, your working set doesn't fit in memory. Consider increasing `shared_buffers` or adding an index to reduce the number of buffers touched.
Buffer Analysis in Practice
In a production incident, a query was taking 5 seconds. We ran with BUFFERS and saw `read=15000` — almost 120 MB read from disk. The table was 2 GB, but the query was scanning half of it. An index on the filter column reduced the read buffers to 100, and the query ran in 50 ms.
Common Patterns and Their Fixes
- arrow_rightSequential scan on a large table with a filter: Add an index on the filter column(s).
- arrow_rightNested loop with high loops: Check if the inner table's join column is indexed. If yes, check row estimate accuracy.
- arrow_rightHash Join with high actual rows vs estimated: Run ANALYZE on the tables involved.
- arrow_rightSort (with high memory usage): Consider an index on the ORDER BY columns.
- arrow_rightBitmap Heap Scan with many recheck conditions: The bitmap may be too coarse; consider a multi-column index.
The Most Overlooked Feature: EXPLAIN (ANALYZE, SUMMARY)
PostgreSQL 14 added `SUMMARY` option that shows planning and execution time breakdowns. It's not enabled by default. Add it to see the time spent in different phases.
EXPLAIN (ANALYZE, SUMMARY) SELECT * FROM orders WHERE status = 'shipped';Planning Time: 0.089 ms
Execution Time: 187.456 ms
Planning Time: 0.089 ms
Triggers: none
JIT:
Functions: 2
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 0.123 ms, Inlining 0.000 ms, Optimization 0.456 ms, Emission 0.789 ms, Total 1.368 msIf JIT time is high (say > 10% of execution), consider disabling JIT for complex queries: `SET jit = off;` and test.
EXPLAIN (ANALYZE, BUFFERS, SUMMARY) is your best friend for deep dives. I alias it as `\ex` in my psqlrc: `\set ex 'EXPLAIN (ANALYZE, BUFFERS, SUMMARY)'`.
Putting It All Together: A Real Debugging Session
A user reported a dashboard query timing out. The query joined `events` (50M rows) with `users` (1M rows) and filtered by `events.type = 'purchase'`. We ran:
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name, count(*)
FROM events e
JOIN users u ON e.user_id = u.id
WHERE e.type = 'purchase'
GROUP BY u.name;HashAggregate (cost=25000.00..25000.10 rows=10 width=68) (actual time=12000..12000.12 rows=5000 loops=1)
Group Key: u.name
-> Hash Join (cost=5000.00..24000.00 rows=100000 width=68) (actual time=100.00..11000.00 rows=80000 loops=1)
Hash Cond: (e.user_id = u.id)
-> Seq Scan on events e (cost=0.00..15000.00 rows=50000 width=4) (actual time=0.023..8000.00 rows=80000 loops=1)
Filter: (type = 'purchase'::text)
Rows Removed by Filter: 49920000
Buffers: shared hit=100 read=40000
-> Hash (cost=3000.00..3000.00 rows=100000 width=68) (actual time=50.00..50.00 rows=1000000 loops=1)
Buckets: 131072 Batches: 1 Memory Usage: 10MB
-> Seq Scan on users u (cost=0.00..3000.00 rows=100000 width=68) (actual time=0.010..20.00 rows=1000000 loops=1)
Buffers: shared hit=100 read=500What did we see? The sequential scan on `events` read 40000 buffers (320 MB) and filtered 49.92 million rows. The estimate was 50000 rows but actual was 80000 — a 1.6x difference, not terrible. But the real issue: the `Hash Join` built a hash table for 1 million users (10 MB), which is fine. The bottleneck was the sequential scan on `events`. We needed an index on `events.type`.
After creating `CREATE INDEX idx_events_type ON events (type);`, the plan changed to an index scan, reducing execution time from 12 seconds to 200 ms.
The Missing Index That Cost $10k
- 14:00Production alert: billing report query timeout > 30s
- 14:05Run EXPLAIN (ANALYZE, BUFFERS) on the query
- 14:06Notice Seq Scan on invoices table (10M rows) with filter on status
- 14:07Check existing index — it's on (status, created_at) but the query uses status alone
- 14:08Add index on (status) only, re-run query
- 14:09Query drops to 2 seconds
- 14:10Identify that the composite index was not used because of column order — status is not the leading column in the query's filter
Lesson
Always verify that your indexes match the query's WHERE clause order. A composite index with the wrong leading column is useless.
Conclusion
EXPLAIN ANALYZE is not a tool you use only when things break. It's a diagnostic you should run on every new query before deploying. Take the output, look for sequential scans on large tables, check row estimate accuracy, and watch for high loops in nested loops. The 10 minutes you spend reading the plan can save hours of debugging later.
Next time someone says "add an index," ask them to run EXPLAIN ANALYZE first. The plan will tell you exactly where to put it.
Frequently asked questions
What is the difference between EXPLAIN and EXPLAIN ANALYZE in PostgreSQL?
EXPLAIN shows the query planner's estimated costs and row counts without executing the query. EXPLAIN ANALYZE actually runs the query and shows actual timings, row counts, and loops. Always use ANALYZE for real performance debugging, but be careful with write queries — use EXPLAIN (ANALYZE, BUFFERS) inside a transaction that rolls back.
How do I interpret the cost numbers in EXPLAIN ANALYZE?
Cost is an arbitrary unit based on I/O and CPU estimates. The first number (e.g., 0.00) is startup cost, the second (e.g., 431.34) is total cost. Compare relative costs across nodes — the most expensive node is your bottleneck. Actual time in milliseconds is more actionable.
Why does EXPLAIN ANALYZE show a sequential scan when I have an index?
PostgreSQL uses indexes only when they are cheaper than a sequential scan. If the query retrieves a large percentage of rows (say >10%), a sequential scan is faster due to random I/O overhead. Also, check if the index is on the correct columns or if your query uses functions that prevent index usage.
What does 'rows=10 width=32' mean in the output?
'rows' is the estimated number of rows returned by that plan node; 'width' is the estimated average width in bytes of each row. If actual rows (from ANALYZE) differ greatly from estimated rows, your table statistics are outdated — run ANALYZE.