A blueprint with no run: EXPLAIN

Your second morning in the engine room. Yesterday's blueprint of the report that hung is still up on the holo-panel by the hatch — a tree of lines speckled with numbers your eyes politely went round at the time.

Today you are studying the navigator's instrument panel and notice something odd: there is not a single clock on it. Scales, counters, graduations — and not a second anywhere.

QUERY: Well spotted. The navigator wears no watch: when it chooses a route the run has not started yet — there is nothing to measure. It measures work: how many pages to leaf through, how many rows to feel. The units of that work are conventional — the navigator has no ruler of its own — so cadets call them parrots: like the boa in the old cartoon, measured in parrots for want of a metre. A parrot converts neither into seconds nor into bytes; it is good for exactly one thing — comparing two routes against each other. From here on in the course the word "parrots" means only that: the conventional units of cost.

Let us learn to read those scales. Open any plan and the first thing to catch the eye is cost. That is where we start.

A node's passport: three readings on the instrument

A plan is a tree of nodes, and every node has the same passport format:

Node  (cost=startup..total rows=estimate width=bytes)

• cost=startup..total — the node's price in conventional units, and there are TWO numbers here. Startup — how much work it takes to hand out the first row. Total — to hand out the last one. Why two you will see in a few minutes, when a node whose two figures nearly coincide appears on the blueprint.
• rows — how many rows the node is forecast to hand upwards. A forecast, not a fact: the query was not executed.
• width — the average width of one such row in bytes. rows × width is how much data will flow up the tree.

About cost remember the main thing: it is parrots, not milliseconds. The unit is not time but conventional work: the planner assigns a price to every action — turn a page, check a row — and adds them up. It needs that price for exactly one thing: comparing routes AGAINST EACH OTHER and taking the cheapest. Parrots cannot be converted into seconds: on other hardware the same price turns into a different time.

You read a tree top-down, and it executes bottom-up — you remember that from Vault-9. So the lower storeys are busy reading tables and the upper ones work on rows already fetched; what their instruments show meanwhile you will see on the second blueprint.

Open the passport of the simplest route there is — a filter over the log:

Reading the instruments

cost=0.00..3763.00. Startup is zero: a sequential pass hands out the first matching row straight away, it has nothing to accumulate. Total is 3763 parrots for the whole log. What that price is made of we take apart in a couple of lessons, when we open the map of input and output; for now it is simply the route's price tag.

The Filter one line below. A filter is not a node of its own: the scan reads all hundred and eighty thousand rows in a row and lets only the warn ones out. Which is why rows is not "how many it will read" but "how many it will hand upwards".

rows. The blueprint says three thousand and a bit; run the cell again and the figure will almost certainly shift: it wanders from run to run. And so it should: the planner did not recount the warn rows — it looked into the statistics, a sampled census of the archive, and the sandbox rebuilds the log and the census on every run, polling slightly different rows each time. In fact there are exactly 3600 warn readings in the log: the forecast misses by a couple of hundred rows — which is more than accurate enough for choosing a route.

width=21. The average result row is 21 bytes: exactly the four columns you asked for. Ask for SELECT * and the width grows, and more bytes flow up the tree.

QUERY: The rows forecast is the most important reading on the whole panel. While it stays honest, the navigator picks good routes. When it lies, forty seconds begin. Remember that pair — the forecast and the fact: we will come back to it.

Now make the navigator fetch a second mechanism: ask for the same list, but in order of value.

The tree grows

ORDER BY did not change the scan's route: the same price 0.00..3763.00, the same width=21. The rows forecast is slightly different again — but that is the same wandering census from the previous cell, not an effect of the ORDER BY. What has appeared above the scan is a new storey: Sort, with the arrow -> before its child scan. The top says "sort whatever the scan hands out"; execution, as always, starts from the bottom: the scan fetches the warn rows and Sort lines them up.

Now look at Sort's cost pair — the whole point of the two numbers is in it. The first figure is almost equal to the second: getting on for four thousand parrots BEFORE the first row is handed out. A sort cannot hand up even one row until it has swallowed them all: what if the scan's last row turns out to be the smallest? A scan is a stream node, its rows flow at once; Sort is a dam node, it accumulates. The startup..total pair is what tells you whether you are looking at a stream or a dam.

And one more reading habit: a node's price includes the price of its children. Of Sort's four thousand parrots the greater part — 3763 — was inherited from the scan; the sorting's own work costs little: three and a half thousand rows is not the volume that makes it sweat.

You can read a blueprint now. But remember: so far the machine has not really read a single page of the log — all of this is the navigator's forecasts. In the next lesson we send the query on a real run — EXPLAIN ANALYZE — and a stopwatch appears on the instrument panel for the first time: the estimate meets the fact.