PostgreSQL EXPLAIN ANALYZE: What I Actually Look At in Query Plans

Every slow query I have ever chased started the same way: someone guessing. "Probably needs an index." "The join is too big." "Postgres is slow today." Guessing is how you add three indexes, change nothing, and learn nothing.

EXPLAIN ANALYZE ends the guessing. It runs the query and tells you exactly where the time went. Here is how I read one, in the order I actually read it, and what each part is telling me.

One warning first, because it bites people: ANALYZE executes the statement. On a SELECT that is fine. On an INSERT, UPDATE, or DELETE, wrap it in a transaction and roll back:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status = 'x' WHERE id = 1;
ROLLBACK;

Forget the rollback once and you will remember it forever.

1. Always add BUFFERS

Plain EXPLAIN ANALYZE tells you time. EXPLAIN (ANALYZE, BUFFERS) tells you time plus where the data came from:

Buffers: shared hit=42 read=8190

hit means the page was already in Postgres's cache. read means it went to disk. A query with 8000 reads and 42 hits is not slow because the plan is bad. It is slow because nothing it needs is cached. That is a completely different problem from a bad join, and it changes everything you do next. Without BUFFERS you cannot tell them apart, so I never run a plan without it.

If you want the disk time broken out too, track_io_timing needs to be on, but the hit/read split alone answers most questions.

2. Read bottom-up, and multiply by loops

A plan is a tree. Postgres executes the leaves first and the results flow upward, so the most expensive thing is usually near the bottom, not the top. Start at the bottom.

Two mechanics people get wrong:

Time is cumulative. The actual time on a node includes all its children. To get what a node itself cost, subtract the children. The node with the biggest exclusive time is where your query is actually dying.

Loops multiply. This line is the classic killer:

->  Index Scan using orders_user_id_idx on orders  (actual time=0.012..0.031 rows=3 loops=184200)

That index scan looks cheap: 0.03 milliseconds. But it ran 184,200 times, once per outer row. That is 184,200 tiny index probes, which is 184,200 rounds of random I/O. The per-loop number lies to you. The total is what you pay.

Whenever I see a high loop count on an inner node, I already know the shape of the fix before reading further: the outer side is too big for a nested loop, or the inner side needs to be cheaper.

3. Estimated rows vs actual rows: the gap is the signal

Every node shows two row counts: what the planner guessed (rows=) and what actually came back (actual rows=). The single most informative number in the whole plan is the ratio between them.

Seq Scan on events  (actual rows=4 loops=1)
   Filter: (created_at > '2026-10-01')
   Rows Removed by Filter: 987331

The planner thought this filter would match a lot of rows. It matched 4. When the estimate is off by orders of magnitude, the planner picks the wrong strategy for the wrong data shape, and everything downstream is built on that lie.

Do not tune the query first. Fix the estimate. Nine times out of ten it is stale statistics: run ANALYZE on the table and look again. If the stats are fresh and the gap persists, the query is shaped in a way statistics cannot model: a function applied to the column (WHERE lower(email) = ...), a LIKE '%...%' with a leading wildcard, or a predicate the planner has no stats for. Then you change the query shape or add an expression index, not another prayer.

4. Seq Scan is not automatically the villain

A sequential scan on a 200-row lookup table is the correct plan. Postgres knows it, and you should let it. The seq scan becomes the villain in exactly one situation: it is scanning a large table and throwing almost all of it away.

Rows Removed by Filter is the field that tells you. If a seq scan reads a million rows and the filter removes 999,996 of them, that is either a missing index or an index the query cannot use. Check whether the filtered column has an index. If it does and Postgres still chose the seq scan, the estimate was probably wrong (see section 3) or the filter is not sargable, like a function call wrapping the column.

Small table, full scan: fine. Big table, filter discards everything: that is your problem.

5. Nested loops: the silent killer

Nested loop joins are perfect when the outer side is small. Ten outer rows, index probe each, done. They become catastrophic when the outer side grows and nobody notices, because the plan shape does not change, only the loop count does. A query that ran fine for a year starts timing out, and the plan looks "the same" except loops= went from 50 to 500,000.

My rule: if the outer side returns more than a few thousand rows, I want a hash join or a merge join, not a nested loop doing random I/O per row. The fix is usually upstream, make the outer side smaller (a better filter, a better estimate), rather than forcing the join type.

6. Hash joins, sorts, and work_mem

When a hash join or a sort does not fit in work_mem, Postgres spills to disk. The plan tells you plainly:

Sort Method: external merge  Disk: 18432kB

or for a hash join, multiple batches instead of one. Disk is orders of magnitude slower than memory, and this is the easiest big win in query tuning: SET LOCAL work_mem = '256MB' for that session or transaction and re-run. If the plan flips to an in-memory quicksort and the time collapses, you found it.

I do not raise work_mem globally for this. It is per-operation memory, and a global bump gets multiplied by every concurrent sort and hash in the system. Session-local, for the query that needs it, is the disciplined move.

7. Index-only scans and heap fetches

An index-only scan is Postgres's cheapest access path, but it has a catch: it still visits the table (the heap) for any row whose visibility is not confirmed in the visibility map. The plan shows this as Heap Fetches. A high number means the table is not vacuumed often enough and the "index-only" scan is quietly doing heap I/O anyway.

If you see an index-only scan with thousands of heap fetches on a table that updates a lot, the fix is not a better index. It is autovacuum tuning, or a manual VACUUM, so the visibility map actually reflects reality.

8. CTEs can be an optimization fence

On Postgres 12 and later, a CTE that is referenced once gets inlined into the main query like a subquery, and the planner can optimize across it. Before 12, every CTE was materialized: fully computed, stored in a temp table, then read. That fence still exists on demand with the MATERIALIZED keyword.

When I see a plan where a CTE materializes millions of rows and the outer query filters down to a hundred, I check whether inlining would let the planner push the filter inside. On modern Postgres this usually happens automatically, but a MATERIALIZED left over from an older version, or a CTE referenced twice, will still fence it. Small thing, big difference on large CTEs.

9. My 60-second routine for any slow query

This is the actual order I go in, every time:

1. Find the node with the largest exclusive time (subtract children, multiply loops). That is the bottleneck. Everything else is context.
2. On that node and its parents, compare estimated rows to actual rows. A big gap means fix statistics or query shape before touching anything else.
3. Check BUFFERS on the expensive nodes. Mostly reads means a caching problem, not a plan problem.
4. Look for disk: external merge, hash batches, temp files. That is work_mem, session-local.
5. Check the join strategy against the outer row count. Nested loop with a huge outer side is the usual suspect for queries that "used to be fast."

Most slow queries I have chased were not exotic. A wrong estimate, a missing index, or a nested loop that outgrew its welcome. The plan tells you which one in about a minute, if you read it bottom-up and trust the numbers over your first guess.

Comments

[DEV]

Dev-verify

tr.ee/dev-to