Forty seconds: your first EXPLAIN ANALYZE

The estimate against the fact

12 min

What you'll learn

to find the plan's main pair of numbers: rows in the estimate and rows in the fact
to understand where the planner's forecasts come from: the statistics gathered by the ANALYZE command
to read the signal "the charts are stale": an estimate and a fact an order of magnitude apart
to age the statistics with your own hands — and watch the navigator go blind

After the watch with the buffers you come down to the engine room as you would come home: the hatch, the ladder, the steady hum. QUERY is waiting not by the planner but at a rack deep in the hall you have taken for a filing cabinet until now. Inside are charts. Not of decks or corridors: charts of the data. On the top sheet is the life-support log: how many rows it holds, which statuses turn up at every step, which almost never do. The navigator does not look into the data itself when laying a route: that would take longer than the run. It looks here — into the charts.

You recall yesterday's plan: every node carried two rows — one in the first pair of brackets, the other in the second. Yesterday they were on good terms: the navigator promised, and almost guessed right.

QUERY: The first is what the navigator expected from the charts. The second is what happened on the run. While those numbers stay close, the machine sees the archive as it is. Today's question: what happens when they diverge?

A tall instrument panel where every gauge has two needles under one glass — chalk-white forecast and ember-orange fact; on the left the pairs nearly coincide, on the right one pair has split to the end of the scale and cracked the glass, the robotic cat staring at it from the panel ledge and the parrot perched on that gauge's rim. — Every node carries two readings. While forecast and fact sit together the plan can be trusted; the gap is where the fault lives.

The estimate and the fact

Every plan node under EXPLAIN ANALYZE carries two pairs of brackets, and each has its own rows:

Seq Scan on ls_readings  (cost=0.00..3763.00 rows=<estimate> ...) (actual time=... rows=<fact> loops=1)

rows in the estimate — how many rows the node will return according to the planner's forecast. That number exists BEFORE execution: the parrots of cost are built out of it — the very price tag by which the navigator compares routes and takes the cheapest. You saw it under a plain EXPLAIN.
rows in the fact — how many rows the node actually returned. That number appears only after the run; the timing writes it in.

You already know each of them separately. Today's skill is checking one against the other: this is the plan's main pair of numbers, and you will be reading it to the end of the course.

Where does the forecast come from? The planner keeps statistics — a compact chart of every table: how many rows it holds, which values are frequent, which are rare. The chart is drawn by the command ANALYZE <table>: it goes over a random sample of rows and redraws it. Do not confuse it with EXPLAIN ANALYZE — the names coincide, but the tools differ: the ANALYZE command draws charts, while EXPLAIN ANALYZE executes a query and shows what came of it.

Hence the reading rule — keep it to hand for the whole course:

An estimate and a fact a few times apart is noise. An ORDER OF MAGNITUDE apart is a signal. A forecast from a random sample is not obliged to hit row for row, and being wrong by a factor of two is its daily bread. But if the navigator expected a thousand rows and twenty thousand arrived, the route was chosen from the wrong chart.

Let us check the station's charts. The sandbox re-sows the log before every run, and the sowing ends with an ANALYZE command — the statistics are as fresh as they get. Let us ask the log about faults:

Two brackets on every node: the planner’s forecast and the stopwatch’s fact — the fault lives in the gap

Look at the rows pair. The estimate is about nine hundred, its own on every run, a hundred or two either way: the charts are drawn from a random sample, so the forecast breathes a little. The fact is exactly rows=900. The discrepancy is pennies: the charts are fresh and the navigator sees the log as it is.

Charts go stale

Statistics are a , not a mirror. The ANALYZE command photographed the table — and from that second the chart starts falling behind the territory: the data changes, the snapshot does not, until the next ANALYZE. In a background process rebuilds the charts, but it comes on its own schedule, when enough changes have accumulated — and the data does not wait. Between "the archive has changed" and "the charts have been redrawn" there is always a window. Inside that window the planner is blind.

Let us make such a window with our own hands. Meet sensor 13, the noisiest on the station — one row in every five of the log is already its. Today it went down: a cascade failure, and in a single morning twenty thousand fault readings dropped into the log. The cell below writes them onto the end of the log itself and — without letting the machine redraw its charts — puts the same question about faults to the planner:

The estimate has stayed the same order — about a thousand: the navigator is looking at a chart taken BEFORE the cascade. (It may come out slightly higher than in the previous cell: the navigator can see that the log has grown fatter even without a chart — but it has not recounted the share of faults in it.) The fact is rows=20900, twenty times the forecast. A discrepancy of an order of magnitude: the planner has gone blind. And Rows Removed by Filter: 179100 has not changed: the same old rows went into the sift — all twenty thousand new readings are faults, and the filter lets them through.

Why a stale chart is dangerous

Today the query survived: there is one node, and a full pass stays a full pass however many rows get through the filter. The danger begins in bigger plans. Forecasting "there are almost no faults", the navigator boldly picks routes designed for hundreds of rows — and twenty thousand travel them. A wrong estimate in a lower node poisons every storey above it: the joins, the sorts, the order of the tables — every following decision is built on a number that does not exist.

The diagnosis is clear and the cure is obvious: redraw the chart. Add ANALYZE ls_readings; between the insert and the query in the cell above and run it again — the estimate will come almost right up to the fact. What the charts are made of inside — which values the navigator remembers by name and how it guesses at the rest — we take apart in the navigator's cabin, in chapter seven.

QUERY: Notice: the machine did not break. It honestly executed the best route for the chart that was on the table. If you want a different route, bring a fresher chart.

The instruments are assembled: the blueprint, the timing, the input and output, and the estimate checked against the fact. In the next lesson we fold them into a single order of operations — the workflow an engineer brings to any slow query.

Practice: solve the tasks

One customer's orders through the linkEASY

Solved 0 of 1