A Factor of Ten at the Bottom Is a Factor of a Thousand at the Top
Last timeGuessing How Many Rows
Estimation errors do not stay where they are made. Each join multiplies the errors of its inputs, so a small mistake low in a plan becomes a catastrophic one higher up.
The previous lesson made estimates from a summary. This one is about what
happens to the error in those estimates as they travel up a plan, and the
answer is that it does not stay the same size.
How a join is estimated
A join takes two inputs and produces some rows. To cost the operation above it,
the planner must estimate how many.
The standard rule is to multiply the two input sizes and divide by the number of
distinct values of the join key on the side that has more of them. It assumes
every key on the smaller side finds a match, and that matches are spread evenly.
- estimated rows out of the join
- estimated rows in from the left input, itself an estimate
- estimated rows in from the right input, also an estimate
- distinct values of the join key on the left
- distinct values of the join key on the right
The errors multiply
Now stack the joins. Each join takes two estimates, each carrying its own error
factor, and produces one estimate carrying the product of them.
The lesson stops here
2 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