Forty seconds: your first EXPLAIN ANALYZE

The report that hung

12 min

O que você vai aprender

to see a query's path after Enter: the → the planner → the executor
to understand that SQL describes the result, while the machine picks the route to it
to feel a slow query with your own hands — on a log of a hundred and eighty thousand rows
to open a query's blueprint — its plan — BEFORE fixing anything: that is what the EXPLAIN command is for, and it is where we start

Chapter 1 — "Forty seconds"

Morning on Vault-9 begins with the life-support report: the deck-by-deck summary goes to the commandant at six hundred hours. Today it did not go.

You open the log: a query that had assembled instantly for months took forty seconds. The station has not changed. The query has not changed. The archive has: the life-support log has added another quarter of a million readings — and the old queries have started to crawl.

Every cadet's first reflex: "slow means it needs an index". Stop it there. An index laid down blind is medicine prescribed without a diagnosis: sometimes it helps, sometimes it does nothing, and sometimes it makes things worse.

QUERY: Forty seconds is not a breakdown. It is a symptom. Let us go down — I will show you where the station decides which way your query travels.

QUERY opens a hatch you had taken for scenery. Below the living decks lies the engine room: a hum, warmth and even rows of machinery. This is where every query of yours turns into a route through the data. And today you will see that route with your own eyes for the first time.

A floor hatch stands open in a quiet deck: ember light and steam pour up, an unfinished report and a stopped clock sit on a desk, the cadet climbs down the ladder into the engine room, the robotic cat waiting on the bottom rung and a clockwork brass parrot perched on the rail. — Forty seconds isn't a breakdown, it's a symptom. The cause lives one deck below, where the route of a query gets decided.

What happens to a query after Enter

SQL is a declarative language: you describe what you want to get, and not a word about how to get it. The "how" is decided by the database, in three steps:

The reads the text of the query and turns it into a structure: which tables, which conditions, which columns.
The planner goes through the possible routes — read the table whole or take an index, in what order to join the tables, by which algorithm — and picks the cheapest one by its own cost model.
The executor travels the chosen route and collects the rows.

One and the same question can be executed by dozens of routes, and the difference between them is not percentages but orders of magnitude: the same result in milliseconds or in minutes.

The planner is the machine we have come down to. It cannot see your intentions — only the query, the schema and its own maps of the data. This whole course is about understanding its decisions and influencing them.

QUERY: I call it the navigator. A navigator is never "stupid" — the charts get stale and the orders get illegible. Both of those can be fixed.

The path of a query: the parser reads the text, the planner picks the route, the executor drives it

Feel the forty seconds

Here is a scaled-down copy of that very report: for each sensor of the first five decks, how many readings it has sent in all. The ls_readings log holds a hundred and eighty thousand rows, and for EVERY one of the two hundred sensors the query leafs through the whole of it again.

Run it and keep your eyes on the timer:

Two hundred sensors — and two hundred full passes over the log. In the station's real report there were thousands of such passes: that is where the forty seconds came from.

The blueprint before the run

The seconds on the timer say "bad", but they do not say why. To see the cause, the machine has the EXPLAIN command: show me the route the navigator chose — without executing the query.

EXPLAIN <your query>;

Back comes a plan — a tree of operations, the blueprint of the route. The word is worth memorising at once: we have no other command that shows the navigator's decision. If you took the basic course, you have met such a blueprint in Vault-9 already; if not, never mind — here we take it apart from scratch.

Open the blueprint of the report that hung:

EXPLAIN (from the English explain) is the command that asks the database to explain HOW it intends to execute a query. It is written before an ordinary query; the query itself is not executed, so the command is safe and instant even on a heavy report. Back comes a plan — the list of steps the machine would take: what to read, what to join it with, what to sort.

The same query under EXPLAIN. Instant: the query was NOT executed — the machine only showed the route. Find the line SubPlan in the tree and Seq Scan on ls_readings under it: that is "leaf through the whole log", written in the machine's own language. Skip the JIT: lines at the end for now — that is how the machine notes that it compiled parts of an expensive query; we will come back to them when we reach memory and the executor.

What you have just seen

Two things worth taking away from this blueprint today:

Seq Scan on ls_readings — a sequential read of the log: all hundred and eighty thousand rows, row after row. That is no crime in itself — sometimes a full pass really is the cheapest thing there is.
SubPlan — a the executor launches afresh for every row of the outer query. A full pass × two hundred sensors — there is the arithmetic of the report that hung.

Notice what we did NOT do: we created not a single index. We do not yet know whether an index is what is needed here — perhaps the query has to be rewritten, and perhaps both. A diagnosis is made from the blueprint, not from the symptom.

QUERY: Remember today's order of operations. The blueprint first. The conclusions after. Cadets who do it the other way round sow the archive with indexes like weeds — and the reports crawl all the same.

In the next lesson we take apart the numbers on the blueprint: what cost is, why it is parrots rather than milliseconds, and how to read a tree that executes bottom-up.

Check yourself

A report has become slow. What does an engineer do FIRST?