A query that runs in 12ms on your laptop can take 4 seconds in production, and the SQL text is identical in both cases. PostgreSQL EXPLAIN ANALYZE output tells you which of four things changed: row estimates, join strategy, memory, or table bloat. This guide walks through a real plan, the numbers that matter, and how to confirm the fix without guessing.
What does EXPLAIN ANALYZE actually execute?
EXPLAIN ANALYZE runs the query and returns the actual plan with per-node timing and row counts. Plain EXPLAIN only shows estimates and never touches your data, which matters when the statement is an UPDATE or DELETE you do not want to fire.
The distinction is not cosmetic. Estimates come from the planner's statistics; actuals come from execution. Every performance bug worth debugging is a divergence between the two, so you need both columns side by side. The official documentation covers the full option list at postgresql.org/docs/current/sql-explain.
Reach for these options in order:
EXPLAIN (ANALYZE, BUFFERS)shows shared hit and read blocks per node, which is how you catch cache misses hiding behind fast-looking timings. TheBUFFERSoption also requiresANALYZE.EXPLAIN (ANALYZE, BUFFERS, VERBOSE)adds output columns and schema qualification, useful when a join is ambiguous.EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)when you are feeding the plan to a visualizer. Reading JSON by eye is worse than text.- Inside a transaction, wrap destructive statements:
BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK;. The analyze runs the write and the rollback discards it.
Run EXPLAIN (ANALYZE, BUFFERS), not bare EXPLAIN ANALYZE. A node reading 40,000 blocks from disk and one hitting 40,000 blocks in shared buffers often show similar per-node times, because the buffer cache is warm. The block counts separate the two cases.
The four numbers that identify a slow query
Read the plan inside out, starting at the innermost node. Four values explain nearly every regression: estimated rows versus actual rows, the loop count on the parent node, the buffer counts, and whether the planner chose a nested loop where a hash join would win.
Here is a representative plan for an orders listing filtered by customer and sorted by date:
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total, o.created_at
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'BD'
AND o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 50;Limit (cost=48211.44..48211.56 rows=50 width=28)
(actual time=3184.201..3184.244 rows=50 loops=1)
Buffers: shared hit=9142 read=38211
-> Sort (cost=48211.44..48580.12 rows=147357 width=28)
(actual time=3184.201..3184.213 rows=50 loops=1)
Sort Key: o.created_at DESC
Sort Method: top-N heapsort Memory: 31kB
-> Hash Join (cost=1180.00..43021.88 rows=147357 width=28)
(actual time=41.882..3012.774 rows=203411 loops=1)
Hash Cond: (o.customer_id = c.id)
-> Seq Scan on orders o (cost=0.00..38200.00 rows=1200000 width=20)
(actual time=0.014..1840.223 rows=1198210 loops=1)
Filter: (status = 'pending'::text)
Rows Removed by Filter: 3790
Buffers: shared hit=8900 read=37200
-> Hash (cost=980.00..980.00 rows=16000 width=8)
(actual time=38.441..38.441 rows=15022 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 897kB
-> Seq Scan on customers c (cost=0.00..980.00 rows=16000 width=8)
(actual time=0.009..18.402 rows=15022 loops=1)
Filter: (country = 'BD'::text)
Rows Removed by Filter: 978
Planning Time: 0.412 ms
Execution Time: 3184.402 msThree things stand out immediately. The orders sequential scan reads 1,198,210 rows to return 203,411 pending ones, so the filter is not selective: 17 percent of the table matches. The hash join then produces 203,411 rows, all of which feed a sort that only needs 50 because of the LIMIT. And the buffer line shows 37,200 disk reads, so this is not a warm-cache artifact.
Estimates are close here (147,357 predicted versus 203,411 actual), which tells you the statistics are fine and the plan shape is the problem. When estimates are off by 100x or more, suspect stale statistics and run ANALYZE orders; before touching indexes. See ANALYZE for how the planner samples.
Nested loop versus hash join: when the planner is wrong
A nested loop is correct when the outer side is tiny and the inner side has an index on the join key. It becomes catastrophic when the planner underestimates the outer row count and rescans a large inner table once per row, which is why loop counts of 10,000 or more next to a sequential scan are a red flag.
| Join strategy | Best when | Failure mode | What to check in the plan |
|---|---|---|---|
| Nested loop | Outer side under a few hundred rows, inner side indexed | Rescans a large inner table per outer row | High loops= on the inner node with large actual rows |
| Hash join | Both sides large, equality join condition | Hash table exceeds work_mem, spills to Batches > 1 | Batches: greater than 1 and a Memory Usage near your work_mem cap |
| Merge join | Inputs already sorted, or output must be ordered | Costly extra sort on unsorted inputs | A Sort node directly beneath the join |
To force a comparison during debugging rather than trusting the planner, set enable_nestedloop = off within a session before running EXPLAIN ANALYZE. If the alternate plan is dramatically faster, the planner is misestimating and you should fix statistics or the query, not override the setting globally. Session-level SET values do not persist, so this is safe to try on a replica.
Fixing the query with a covering index
For the plan above, a composite postgresql index on the filter column and sort column lets PostgreSQL walk the index in order and stop after 50 rows instead of sorting 203,411. The index only works if the leading column matches the equality predicate and the sort column follows it.
CREATE INDEX CONCURRENTLY idx_orders_status_created_at
ON orders (status, created_at DESC)
INCLUDE (customer_id, total);CONCURRENTLY avoids holding a write lock for the duration of the build, which matters on a live table. It takes longer, cannot run inside a transaction block, and can leave an invalid index behind if it fails, so check pg_index.indisvalid after any failure rather than assuming success.
After creating the index, run ANALYZE orders; and re-run the plan. The expected change is the sequential scan and sort being replaced by an index scan that stops at 50 rows. Confirm with the buffer counts, not just wall-clock time: if shared read drops by an order of magnitude, the index is doing its job.
Re-run EXPLAIN ANALYZE after every change. A new index can be ignored if statistics are stale, and an index that helps one filter combination can hurt writes across the whole table. Compare buffer reads before and after, not just execution time.
Where EXPLAIN ANALYZE leads you astray
Two failure modes produce confusing plans that are not query problems at all. Both are common in Laravel applications that hold long-lived connections through a pooler.
The first is table bloat. Dead tuples left by heavy updates inflate the heap, so a sequential scan touches far more pages than the live row count suggests. Cross-check with a heap page count from pg_relation_size('orders') against the row count; if the ratio is far above what the row width implies, a VACUUM or autovacuum tuning issue is the real cause, and no index will fix it. The Buffers: shared read line rising while the filter is selective points this way.
The second is postgresql connection pooling. Timing a query through PgBouncer in transaction mode gives the same plan, but if the pool runs out of server connections, your application waits before the query even starts. The plan looks clean and the request is slow, because the latency lives in the pool queue, not the executor. Measure both: plan execution time and the round trip through the pool.
On postgresql jsonb, the same reading rules apply with one addition. A Filter node that evaluates a containment predicate such as data @> '{"status":"pending"}' is sequential by nature unless a GIN index exists on the column, and even then the planner needs up-to-date statistics on the expression. If the plan shows a sequential scan with an expensive JSONB filter and you have the index, run ANALYZE first. The JSONB documentation lists which operators a GIN index can serve, and an operator outside that set will never use the index no matter how the query is written.
If you are tuning Eloquent queries that generate these plans, the same estimate-versus-actual reasoning applies: the SQL indexing debugging guide walks through composite index selectivity, and fixing Eloquent N+1 covers the query count problem that no index fixes.
Frequently asked questions
Is EXPLAIN ANALYZE safe to run on production?
It executes the statement, so reads are safe and writes are not unless wrapped in a transaction you roll back. Long-running analytics queries can still consume CPU and I/O, so run them against a replica when one exists. Use EXPLAIN alone when you only need the estimated plan.
Why does the plan show an estimate of 1 row but an actual of 50,000?
The planner's statistics are stale or the predicate is correlated across columns, which a per-column histogram cannot capture. Run ANALYZE on the table first, then consider CREATE STATISTICS for correlated columns. Large estimate errors usually cause join strategy mistakes downstream.
What does Buffers: shared read mean?
It counts 8kB blocks the executor read from disk rather than from the shared buffer cache. High reads on a node mean that node is doing real I/O, regardless of how fast its reported time looks. Subtract hits from total to judge how much of the table stayed cached.