You Asked For Rows, Not For a Method
A query names the answer and says nothing about how to get it. Everything left unsaid is decided by a program you did not write, in a few milliseconds, on every request.
A query describes the rows you want. It contains no instruction about how to
find them, which is the whole point of the language and is also why a query can
be a hundred times slower than it needs to be without looking any different.
What the query actually says
Take an ordinary request: the orders placed last month by customers in one
country, with the customer name, sorted by date.
That names a set of rows. It says which tables the rows come from, which
conditions they satisfy, how the tables relate, what to return and in what
order. Those are all properties of the answer.
| fixed by the query | decided by the database | roughly how many options | affects the answer | |
|---|---|---|---|---|
| which rows are in the an | 1 | 0 | 0 | 1 |
| the order of the output | 1 | 0 | 0 | 1 |
| how each table is read | 0 | 1 | 4 | 0 |
| how each pair is combine | 0 | 1 | 3 | 0 |
| the order the tables are | 0 | 1 | 6 | 0 |
| where the sorting happen | 0 | 1 | 3 | 0 |
The useful habit is to read a query and ask what it did not say. It did not say
to use an index. It did not say which table to start from. It did not say to
sort at the end rather than relying on an index that was already in order. Each
of those is a decision with a cost, and the query is silent on all of them.
The decisions left open
The open decisions fall into four kinds. For each table, how to get the rows:
scan it, or seek into one of its indexes, and if several indexes apply, which.
For each pair of inputs, how to combine them, which is a choice between three
methods with very different cost shapes. For the set of tables, the order to
combine them in. And for sorting and grouping, where in the plan to do it,
since an input that is already sorted makes it free.
None of these changes the answer. All of them change the time.
- number of distinct plans
- number of tables in the query
- join methods available, three in most systems
- access paths per table, a scan plus the usable indexes
How many plans there are
Two consequences follow immediately and both shape everything in this course.
The planner cannot look at every plan, so it uses a search that prunes, which
means the best plan is sometimes not considered at all. And the planner must
compare plans without running them, which means comparing estimates, which
means the quality of the estimates is the quality of the plans.
What is actually at stake
It would be reasonable to assume the spread between a good plan and a bad one
is tens of per cent. It is not.
| step | step | plan chosen when the estimate was right | plan chosen when the estimate was wrong | what happened |
|---|---|---|---|---|
| 1 | start from | customers in one country, 1200 rows by i | all orders of last month, 4.1 million ro | The first decision sets everything after it. Starting from the small filtered side keeps every later step small. |
| 2 | then combine with | their orders, by index seek, 9400 rows | the customer table, by hash, 4.1 million | The second plan is now carrying four million rows into a join that will discard almost all of them. |
| 3 | then filter to | last month, 820 rows | one country, 9400 rows | Both arrive at the same place. One of them read four hundred times more to get there. |
| 4 | rows read in total | 11400 | 8300000 | Same query text, same answer, same database. The difference is one decision made from one wrong number. |
That is the shape of the subject. A planner is a small compiler with a few
milliseconds, choosing among millions of possibilities, using numbers it cannot
verify. It usually gets it right. When it does not, the result is not a little
slower, and the fix depends entirely on which part of that sentence broke.
The next lesson takes the most important of the open decisions, how two tables
are combined, and derives the cost of each method from the sizes of the inputs.
Recap
- A query is a description of the answer, so every question of method is left open, and the database has to settle all of them before a single row is read.
- The open decisions are the access path for each table, the join method for each pair, the order the joins happen in, and where sorting and grouping are done.
- The number of possible plans grows faster than anything else in the system, and the spread between the best and worst of them is routinely a factor of several hundred.
This 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