EXPLAIN ANALYZE: Reading a Plan Without Guessing

The fastest way to fix a slow query is to stop guessing. EXPLAIN ANALYZE runs the query and reports the actual plan it used, with real row counts and real timings. Most engineers I work with under-use it because the output looks intimidating. It is not — once you know which two numbers to read first.

1. The two numbers that matter

On every node of the plan, Postgres prints both the estimated rows and the actual rows:

Seq Scan on orders  (cost=0.00..18900.00 rows=120 width=64)
                    (actual time=0.014..42.318 rows=58231 loops=1)

The planner thought it would see 120 rows. It saw 58,231. A two-orders-of-magnitude misestimate is the single most common reason Postgres picks a stupid plan — it nested-loops what should have been hash-joined, it index-scans what should have been a seq-scan.

First question on any slow query: are the estimates within 10× of the actuals? If no, fix that before anything else. Usually the fix is ANALYZE on the table (often missed after a bulk load), increasing statistics target on a skewed column, or adding an extended statistic for correlated columns.

2. Use BUFFERS — always

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT ...;

BUFFERS tells you how much of the work was cache-resident vs read from disk. A 200ms query that reads 20,000 buffers from disk is a different problem from a 200ms query that reads 20,000 buffers all from cache. The fix for the first is an index or a covering index. The fix for the second is making the query do less.

3. The four scan types in one paragraph

• Seq Scan — read every page. Correct when the query touches more than ~5–10% of the table.
• Index Scan — walk the index, then fetch the row. Good for selective predicates.
• Index Only Scan — the index has every column the query needs (often via a covering index INCLUDE(...)). Beats Index Scan by a wide margin when the table is large.
• Bitmap Index Scan — for medium selectivity (a few thousand matching rows). Postgres builds a bitmap of pages, then reads them in physical order.

4. Join shapes

The planner picks one of three:

• Nested Loop — for each row on the outer side, look up the inner side. Great when the outer is tiny; catastrophic when both sides are large.
• Hash Join — build a hash of the smaller side, probe with the larger. The default when both sides are non-trivial and the smaller side fits in work_mem.
• Merge Join — sort both sides on the join key and walk them in lockstep. Wins when both inputs are already sorted (an index supplies the order for free).

A nested loop with the outer side being a misestimated seq scan is the most common "why is this query 500× slower than it should be" pattern. Fix the estimate, the planner picks a hash join, the query is fast again.

5. work_mem and spills

Sorts and hash joins want to live in memory. When the working set exceeds work_mem they spill to disk and slow down brutally. The plan tells you:

Sort Method: external merge  Disk: 412928kB

That is 400 MiB of temp file activity. Raise work_mem for that query (or for that session) and the spill disappears. Beware: work_mem is per-node, per-session, not per-query — bumping the global default can blow up a busy server.

6. A workflow that works

1. Run EXPLAIN (ANALYZE, BUFFERS) and look at the topmost node with a misestimate.
2. If the misestimate is on a single column, run ANALYZE and try again. If still off, raise statistics target.
3. If two columns are correlated, add CREATE STATISTICS with dependencies.
4. If a join is using nested loop on big rows, look one level up — the misestimate is feeding it.
5. If everything looks correct and you are still slow, you have a missing index or a covering index opportunity.

The mental model

Postgres makes decisions on rows-per-cost. When the row count it predicts is wrong, the cost it predicts is wrong, and the wrong plan wins. Ninety percent of "why is Postgres so dumb" turns out to be "the statistics are stale." Read the plan, find the misestimate, fix the statistics. The plan changes itself.