How to Read a Plan
Last timePutting Two Tables Together
A plan looks like an intimidating wall of text and is really a small tree with four numbers per node. Knowing which four and in which direction to read turns it into a diagnosis.
A plan is the only honest account of what the database did, and it is written in
a format that discourages reading. The format is actually simple: a tree, drawn
with indentation, with four numbers that matter on each line.
Start at the bottom
Each line's children are the lines indented beneath it, and children run before
their parent. So the deepest, most indented lines are where execution begins:
they touch tables, and everything above them consumes what they produce.
This is why a plan is read from the bottom up. The top line's time is simply the
query's total, which the timer already told you. The bottom lines tell you how
many rows entered the machine and from where, and almost every diagnosis starts
there.
The two row counts
With timing enabled, every step reports two row counts: the number it expected
to produce, decided before the query ran, and the number it actually produced.
Comparing them at every step, from the bottom up, is the most valuable thing you
can do with a plan. A factor of two means nothing. A factor of a hundred means
the plan above that step was chosen on a false premise, and because the counts
are computed upwards, the lowest step where they diverge is the only independent
error in the plan. Everything above it is a consequence.
The lesson stops here
4 more paragraphs to go
You have read the opening. The rest of the argument, the problems that check whether it landed, and the lines worth keeping at the end all come with a plan.
The first lesson of every course in the library reads the whole way through, free, so you can see exactly what the rest of them are.
See the planThe contentsThis is the reading half
Starting the course gives you your own copy of it. Every idea on every page has problems standing under it, marked with a reason rather than a tick, and any sentence you do not believe can be opened and argued with. None of that can happen on a page nobody owns.
The contents