Skip to content
K P

3 min read

Reading the Query Plan

Postgres will tell you exactly why your query is slow, in a format designed to be ignored. Four things to look at, in order.

Almost every slow query I have fixed was fixed by reading the plan properly, and almost every hour I wasted before that was spent guessing. EXPLAIN ANALYZE is not a diagnostic of last resort. It is the first thing to run.

The output is genuinely hostile to read — nested, verbose, and printed inside out. But you only ever need four things from it, and they are always in the same places.

One: use ANALYZE, and BUFFERS

explain (analyze, buffers, format text)
select ...;

Plain EXPLAIN gives you the planner's guess. ANALYZE actually runs the query and reports what happened. BUFFERS tells you how many pages came from cache versus disk, which is the difference between "this query is slow" and "this query is slow the first time".

The one caution: ANALYZE really executes. Wrap writes in a transaction you roll back.

Two: find the row-count lie

Every node prints two numbers:

Seq Scan on memories  (cost=0.00..18234.00 rows=412 width=64)
                      (actual time=0.02..92.41 rows=284119 loops=1)

Estimated 412 rows. Got 284,119. That gap is the whole bug. The planner chose a sequential scan and a nested loop above it because it believed there were four hundred rows; with a quarter of a million, both choices are catastrophic.

Find the deepest node where estimate and actual diverge by more than an order of magnitude. Everything above it is a bad decision made for a good reason, and fixing the leaf usually fixes the tree.

Bad estimates almost always mean stale or missing statistics:

analyze memories;
alter table memories alter column tenant_id set statistics 1000;

Three: read the times inside out

Timings are cumulative — a node's actual time includes all its children. To find where the time actually went, subtract.

And note loops. A node showing actual time=0.8..1.2 rows=3 loops=94000 did not take one millisecond. It took ninety-four seconds. This is the single most commonly misread number in the entire output, and it is how a nested loop hides.

Four: know which three node types matter

  • Seq Scan on a large table where you expected an index. Either the index does not exist, or the predicate cannot use it — a function on the column, a type mismatch, a leading wildcard.
  • Nested Loop with a large outer row count. Fine for three rows, ruinous for three hundred thousand.
  • Sort or Hash with Disk: in the buffers line. It spilled. Sometimes the fix is genuinely one work_mem setting.

Everything else is usually a consequence of one of those three.

The habit worth building

Run EXPLAIN ANALYZE on queries that are fast, occasionally, while nothing is wrong. You learn what a healthy plan looks like for your schema, and the day one goes bad you will recognise the shape of it immediately instead of reading four hundred lines of tree for the first time under pressure.