EXPLAIN triage, as a procedure

It is three in the morning and something is slow. Here is the order to look in, and what each answer rules out.

10 min · runs against the events dataset

Run EXPLAIN ANALYZE and read it in this order. Each step rules something out, which is what makes it a procedure rather than a hunt.

*Find the node with the most pages.* Not the deepest, not the widest — the most expensive. Everything above it is downstream of a decision made there.
*Compare its estimate to its actual.* Within about 2x, the planner was reasoning correctly and the plan is the best one available for that data. Past 20x, the plan was built on a false premise and fixing the estimate is the fix.
*If the estimate is wrong, ask why.* Stale statistics, or correlated predicates. ANALYZE costs nothing and rules out the first.
*Check `loops`.* Anything above one means something above re-ran it. If loops is in the hundreds, that is an N+1 and no index will save you.
*Check `Rows Removed by Filter`.* A scan reading four million rows to return nine is asking for an index — and if there is one it is not being used, which is usually a function wrapped around the column.
*Only then think about the query.* Most slow queries are a plan problem, not a SQL problem, and rewriting the SQL before understanding the plan is how people spend an afternoon on the wrong thing.

Walk the six steps down this one. Which node, what is its estimate against its actual, and why?

EXPLAIN ANALYZE SELECT e.id FROM events e WHERE e.kind = 'error' AND lower(e.email) LIKE 'actor1%' ORDER BY e.happened_on

plan

node	est	actual	loops	pages
SortSort Key: e.happened_onSort Method: quicksort Memory: 2kB	32	23	1	0
→ Bitmap Heap Scan on eRecheck Cond: (kind = 'error')Filter: (lower(e.email) LIKE 'actor1%')Rows Removed by Filter: 59	32	23	1	82
→ Bitmap Index Scan on events_kind_happened_idxIndex Cond: (kind = 'error')	647	827.9x under	1	4

And a case worth naming: sometimes the planner is right and the query is wrong. If the estimate matches the actual and the plan is the cheapest available, the database is doing the best possible job of answering a question that is too expensive to ask. That is not a tuning problem, it is a product problem, and no index will fix it.

The reason to do this in order is that each step is cheap and rules out a whole class of cause. Jumping to step six means rewriting a query whose problem was a missing ANALYZE, and discovering that an hour later. Working down the list means the expensive thinking happens last, on the one question the plan has already told you is the one that matters — which is what makes a procedure worth having at three in the morning, when judgement is the thing in shortest supply.

← Migrations that lock, and the ones that do not A query an agent wrote →