The City Already Told You the Postcode
Last timeWhen the Guess Is Wrong
The planner multiplies the shares of two filters as though neither says anything about the other. When the columns are related, that single assumption is where most bad plans begin.
The last lesson said that errors multiply up a plan. This one is about the
single most common source of the error at the bottom, which is two filters on
columns that are not telling the database two different things.
The rule being applied
A table with two filters is estimated by multiplying their shares. Nothing more
sophisticated happens, because nothing in the summary of a table records any
relationship between two of its columns: the summary has a histogram per column
and no notion that two columns might be about the same thing.
- the hat marks the estimate, and the whole of this lesson is about the distance between it and the quantity on the right
- the assumed combination: the table size times the share passing the first filter times the share passing the second, as though the two filters were unconnected
- the truth, where c is the share of the rows that already passed the first filter which also pass the second, a different quantity entirely from b
It is worth being clear that the rule is not a mistake. For two genuinely
unrelated columns, the shares multiply exactly, and most pairs of columns in
most tables are unrelated enough for it to be fine. The trouble is that the
related pairs are also the pairs people filter on together.
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