Reading EXPLAIN: How the Optimizer Actually Runs Your Query

· tech

#sql#performance

📑 Contents

The previous post left a question: is the index actually being used? The answer is in EXPLAIN. This post is the SQL sibling of the Spark execution-plan post I wrote earlier — the same “read the plan, find the bottleneck” thinking, on a different engine. Learn to read EXPLAIN and you upgrade from “guessing why it’s slow” to “opening it up and looking”.

EXPLAIN: laying the optimizer’s plan open

EXPLAIN <query> prints how the optimizer intends to run the query — without actually executing it; a static estimate. EXPLAIN ANALYZE really runs it once and attaches each step’s actual time and actual row count. Read it as in the Spark post: the deepest indentation runs first (inside out), layer by layer up to the final result.

In the plan, first recognise the “scan type” — which connects straight to the previous post’s indexes:

  • Seq Scan: full table scan (no index used).
  • Index Scan: walk the index to find the location, then go back to the table for the data.
  • Index Only Scan: every column needed is in the index, so the table isn’t touched at all (covering index).
  • Bitmap Heap Scan: when the matching rows are neither few nor many, use the index to collect a batch first, then read them in one go.

Three JOIN algorithms

The most worthwhile thing to understand in a plan is which algorithm the JOIN used. This also completes the thread left in post 2 on join memory — the optimizer picks one of three based on table size, sort order and indexes:

Nested Loop outer outer outer inner(faster if indexed) each outer row → one inner lookup suits: small tables / indexed inner Hash Join small hash table (memory) big small builds hash, big probes suits: big tables, equality joins Merge Join 1 3 5 2 4 6 sort both sides, merge like a zipper suits: already sorted / indexed
No JOIN algorithm is absolutely better, only better suited: Nested Loop is fast for small tables (or an indexed inner table), Hash Join handles big equality joins, Merge Join saves effort when the data is already sorted. The same set of concepts as Spark's broadcast vs sort-merge

Reading a real plan

Put it together and look at how to read the plan for orders JOIN customers:

Hash Join (cost=… rows=500) Hash Join (small side builds, big side probes) -> Seq Scan on orders full scan of orders — should this be indexed? -> Hash -> Index Scan on customers customers used its index ✓ Reading: deepest indentation runs first; cost is the optimizer's "estimate", EXPLAIN ANALYZE gives real actual time / rows
Read inside out: scan customers (via index) to build the hash, scan orders to probe, and the Hash Join combines them at the top. A big-table Seq Scan should make you ask "is an index missing here" — that's how a performance problem becomes a line you can point at on screen

The three things to look for when reading a plan: ① a Seq Scan where an index should have been used? (missing index, or disabled by a function/type mismatch) ② was the right JOIN algorithm chosen? (a small-table join using Nested Loop over a big table is a disaster) ③ do the estimated rows differ from EXPLAIN ANALYZE’s actual by orders of magnitude? — a big gap means stale statistics; the optimizer holding wrong estimates picks the wrong plan, and running ANALYZE to refresh the statistics often fixes it outright.

Reflections

Reading the plan turns “guessing” into “seeing”

The takeaway from this post is almost word for word the same as the Spark post: before you can read a plan, performance is mysticism; after, it’s lines on a screen you can understand. I used to debug slow queries by experience and by trial and error; now the first step is always EXPLAIN ANALYZE, letting the plan tell me directly which step is the bottleneck, how many rows it scanned, how the join was done. This discipline of “read the plan before touching anything” is fully shared between SQL and Spark — the underlying thinking crosses engines; learn one and you’re halfway through the other.

Cost is an estimate; ANALYZE is the fact

EXPLAIN’s rows and cost are the optimizer’s guesses from statistics, not reality. I’ve been burned staring at a pretty cost and assuming all was well, only for the real run to be dreadfully slow — because the statistics were stale, the optimizer estimated a step with a million rows at a thousand, and the plan went completely sideways. So now I only trust EXPLAIN ANALYZE’s actual. There’s a bigger truth here: however clever the optimizer, it’s only as accurate as the statistics in its hands. Estimates drifting from reality is the root of many “inexplicably slow” incidents — and the fix is often laughably plain: refresh the statistics.

No JOIN algorithm is best, only best suited

Nested Loop, Hash and Merge each have their home ground, and the optimizer usually picks right. But “usually” isn’t “always” — when its estimates are off, it can run Nested Loop over a big table and be a thousand times slower. You need to be able to read “why it chose this, and what it should have been” to judge whether the optimizer got it wrong. That requires genuinely understanding the temperament of the three algorithms, and that knowledge is the same set as post 2’s join memory and Spark’s broadcast vs sort-merge — in the end, “how data gets matched” is the same physics on one machine and across many. Learn it thoroughly once and it serves you on every engine.