EXPLAIN, but Slowly

EXPLAIN output looks dense the first dozen times you read it, and then it looks familiar in a way that is more dangerous than the density. Operators start to skim the plan, anchor on a familiar pattern (a sequential scan, perhaps), and propose a fix. Sometimes the fix works. Often it works for the wrong reason, which is the worst kind of working.

In our Performance Tuning cohort we teach the slower habit of reading EXPLAIN ANALYZE outside-in: start with the topmost node and ask what shape its inputs are. Then read each child node and ask what shape its output should be. Only once the shape and cardinality estimates match what you would predict do you look at the timings.

The point is to catch the planner being wrong about cardinality before you blame the planner for being slow. A node that processed forty times more rows than the estimate suggested is rarely a tuning problem; it is usually a statistics problem. A node that processed exactly what was estimated and is still slow is a different conversation entirely.

The slow habit takes ten extra seconds per plan. Most participants find that those ten seconds save them an hour later in the week. Not always — sometimes the plan is exactly what it looks like and the obvious fix is the right one. Reading slowly is a way of earning the right to act quickly.