It Has Never Looked at Your Data
Last timeThree Ways to Join
Every decision in a plan rests on how many rows each step will produce. The planner works that out from a few hundred numbers gathered hours ago, and here is exactly how.
Everything in the previous lesson came down to sizes. A loop was chosen because
one side was small; a hash was sized for the input it expected. This lesson is
about where those numbers come from, and the answer is that they are guesses
made from a very small summary.
What it actually knows
The planner does not read the table. Reading the table to find out how long
reading the table would take is obviously absurd, so instead a background job
samples the table occasionally and records a summary.
Per table: the number of rows and the average row width. Per column: how many
distinct values there are, how often the column is null, a list of the most
common values with their frequencies, and a histogram dividing the rest of the
range into buckets holding roughly equal numbers of rows.
That is a few hundred numbers per column, standing in for a table of billions
of rows, and gathered from a sample of perhaps thirty thousand of them.
| lower bound | upper bound | rows in the bucket | rows at or below the upp | |
|---|---|---|---|---|
| bucket 1 | 0 | 12 | 94000 | 94000 |
| bucket 2 | 12 | 29 | 94000 | 188000 |
| bucket 3 | 29 | 58 | 94000 | 282000 |
| bucket 4 | 58 | 140 | 94000 | 376000 |
| bucket 5 | 140 | 9800 | 94000 | 470000 |
Estimating one filter
With that summary, a filter is estimated in one of three ways.
If the filter asks for equality against a value in the most common list, the
frequency is read off directly and the estimate is excellent. If it asks for
equality against anything else, the planner assumes the remaining values are
evenly spread and estimates one over the number of distinct values.
The lesson stops here
5 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