Part 4 — Fast at scale › Chapter 27

Reading an execution plan

Scan vs seek, nested loops vs hash vs merge, and the two numbers to look at before anything else.

From the advert

“Ottimizzazione e tuning delle query” — this is the skill the phrase actually names. Everything else is guessing with confidence.

Getting a plan

How to ask each engine for a plan — and which of these actually run the query.

-- PostgreSQL. ANALYZE actually RUNS it and reports real numbers, not estimates.
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;   -- BUFFERS: cache hits vs disk reads

-- SQL Server
SET STATISTICS IO, TIME ON;          -- logical reads and CPU: often enough on its own
-- then "Include Actual Execution Plan" (Ctrl+M) in SSMS, or:
SET SHOWPLAN_ALL ON;                 -- ESTIMATED plan, without running the statement

-- MySQL
EXPLAIN ANALYZE SELECT ...;          -- 8.0.18+; also runs the query
-- Oracle
EXPLAIN PLAN FOR SELECT ...;         -- store the plan…
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);   -- …then read it back as rows

Estimated versus actual, and the warning that matters

An estimated plan is what the optimiser intends. An actual plan adds what really happened. The gap between estimated and actual row counts is the single most useful number on the page: a factor of a thousand means the statistics are wrong (chapter 28) or the predicate is unestimatable.
EXPLAIN ANALYZE executes the statement. On an UPDATE or DELETE that is a real change — run it inside a transaction you intend to roll back, or not at all.
SET STATISTICS IO reports logical reads: 8 KB pages touched. It is the one measurement that does not move with cache warmth or a busy server, which makes it the right number to compare two versions of a query.

⚠ Estimated is not actual

An estimated plan is what the optimiser intends; an actual plan additionally reports what happened. Nearly every performance problem is a gap between the two, so a plan without actual row counts is missing the diagnostic information. In PostgreSQL that means EXPLAIN ANALYZE, not plain EXPLAIN — and note it really executes the statement, so wrap a DELETE in a transaction you roll back.

The two numbers to look at first

🧒 Simple

Somebody planned a trip expecting twelve people and booked a small car. Four hundred turned up. The car is not the problem — the guess about how many people were coming is the problem, and everything that went wrong afterwards followed from it. When you read a plan, look first for the place where the database expected a few and got a lot.

🎓 Before you read a single operator name

Estimated rows vs actual rows. A gap of more than about 10× at any operator is the root cause, and everything downstream of it is a consequence, not a problem in itself. Fix the estimate — statistics, a rewritten predicate, a temp table — and the plan often changes shape entirely.
Where the time or the reads actually went. PostgreSQL prints actual time per node; SQL Server gives a percentage-of-batch cost, and SET STATISTICS IO ON gives logical reads per table, which is more trustworthy than the percentage because it is measured rather than estimated.

The expensive operator is rarely the broken one.

The operators you actually need

Operator	Means	Concerning when
Index Seek / Index Scan (PG)	Navigating the tree to a range	Rarely — this is what you want
Index Scan (MSSQL) / Seq Scan (PG)	Reading the whole structure	On a large table with a selective predicate
Key Lookup / Bitmap Heap Scan	Fetching the row after finding the key	With a high execution count — make the index cover
Nested Loops	Seek the inner side once per outer row	When the outer input is large or badly estimated
Hash Match / Hash Join	Build a hash table, then probe	When it spills to disk
Merge Join	Walk two sorted inputs	When it forced a sort to get them
Sort	Ordering	Nearly always — can an index supply the order?
Spool / Materialize	The engine caching an intermediate result	Often a sign of a correlated subquery it could not flatten
Parallelism / Gather	Multiple workers	When repartitioning costs more than it saves on a small query

The warnings worth knowing by name

🎓 Three that each explain a whole class of slowness

Spill to tempdb / external merge sort. A sort or hash asked for less memory than it needed, because the row estimate was low, and finished on disk. Look at the estimate that caused it, not at the sort.
Implicit conversion / CONVERT_IMPLICIT. A type mismatch is preventing a seek. See chapter 07.
Missing index suggestion. SQL Server will propose one. Treat it as evidence, not instruction: it considers one query in isolation, routinely suggests an index with fourteen included columns, and does not know you already have one that differs by a single column. Read it, then design the index yourself.

A method, in order

Get an actual plan plus STATISTICS IO / BUFFERS.
Find the largest estimate-versus-actual gap. Start there, not at the most expensive-looking node.
Find the table with the most logical reads. That is where the work is, regardless of what the cost percentages claim.
Ask what the engine had to do that it should not have: a scan where a seek was possible, a sort an index could have supplied, a lookup a covering index would remove, a spool over a correlated subquery.
Change one thing, re-measure. Two changes at once and you have learned nothing about either.

Two plans, side by side: an index that already sorts, and a sort the engine has to do itself.

-- Question one: one customer's orders, newest first.
EXPLAIN QUERY PLAN                                            -- ask for the plan…
SELECT * FROM orders WHERE customer_id = 7                    -- …of this query…
ORDER BY placed_at DESC;                                      -- …including its sort
--   SEARCH orders USING INDEX ix_orders_cust_date (customer_id=?)
--   …and NO "USE TEMP B-TREE FOR ORDER BY" line, because the index is already in
--   that order. The missing line is the win.

-- Question two: the ten largest orders overall.
EXPLAIN QUERY PLAN                                            -- the same request…
SELECT * FROM orders ORDER BY total_cents DESC LIMIT 10;      -- …for a different question
--   SCAN orders                       ← every row read…
--   USE TEMP B-TREE FOR ORDER BY      ← …then all of them sorted, to return ten

Reading a plan, in three habits

Look for what is missing as well as what is there. A plan with no sort step for an ORDER BY means an index supplied the order — the cheapest possible outcome.
LIMIT 10 does not mean “read ten rows”. Without a matching index the engine must sort everything to know which ten. An index on (total_cents DESC) would turn this into reading ten index entries.
Change one thing at a time and re-read the plan. Two changes at once and you have learned nothing about either.

✔ How to talk about a plan in an interview

Narrate it as a story about work: “It seeks the index on status, gets about four hundred rows, then does a key lookup for each one to fetch the customer id — so four hundred random reads. Making that index cover customer_id removes the lookup entirely.” That is more convincing than naming operators, because it shows you know what the operator costs.

Get an actual plan. An estimated plan is missing the diagnostic half.
The biggest estimate-versus-actual gap is the root cause; everything below it is a consequence.
Logical reads are measured; cost percentages are estimated. Trust the measured one.
A scan is not automatically wrong, and a seek is not automatically right.
A key lookup with a high execution count means the index should cover the query.
A sort operator is an invitation to ask whether an index could supply the order.
A spill to disk means the memory grant was too small, which means the estimate was too low.
Missing-index suggestions are evidence, not instructions. They see one query and no existing indexes.
Change one thing and re-measure.

Interview questions this chapter answers

How do you approach a slow query?

Get the actual plan and the IO statistics. Look for the largest gap between estimated and actual rows, because everything downstream of a bad estimate is a symptom. Then look at which table did the most logical reads and ask what work the engine did that it should not have — a scan instead of a seek, a sort an index could have supplied, a lookup per row. Change one thing, re-measure.

You see a key lookup executed 50 000 times. What does that mean?

The index found the rows but did not contain every column the query needed, so the engine is following the pointer back to the table once per row — 50 000 random reads. Adding the missing columns to the index, as INCLUDE where available, removes the lookup completely.

Should you always follow SQL Server's missing-index suggestions?

No. They are generated per query in isolation, so they ignore indexes you already have, propose very wide INCLUDE lists, and take no account of write cost. I treat them as a hint about which columns matter, then design the index — often by extending one that already exists rather than adding a fourth similar one.