🗄 SQL & Databases · Chapter 8

Query plans: reading EXPLAIN without fear

Stop guessing why a query is slow. The database will tell you exactly what it plans to do — you just have to read it bottom-up.

Study this lesson free in the app →

The basics

EXPLAIN asks the database to show its query plan — the step-by-step recipe it has chosen to answer your SQL: which tables it reads, in what order, using which method, and how many rows it expects at each step.

The problem it solves: SQL says what you want, never how to get it. The same query can take 40 ms or 95 seconds depending on whether the engine uses an index, which join method it picks, and whether it has to spill to disk. Without the plan you're guessing — adding indexes and rewriting joins at random.

The mental model: a cab app's route preview. EXPLAIN shows the planned route and estimated time. EXPLAIN ANALYZE actually drives it and shows what happened at each turn. When estimate and reality differ wildly — "expected 200 rows, got 40,000" — that gap is almost always where the problem is.

Read the plan as a tree: the most indented lines run first and feed their rows upward.

Explained

Most people optimise SQL by superstition: add an index, rearrange the joins, sprinkle DISTINCT, hope.

EXPLAIN replaces all of that with a fact. It shows the plan — which tables get read, in what order, by what method, and how many rows the engine expects at each step.

Three numbers answer almost every performance question: what it's scanning, how many rows it thinks it'll get, and how wrong that estimate turns out to be.

The route the driver actually took

You ask for a route from Shillong to Guwahati. The app says 3 hours.

It took 7.

Useless information on its own. But if you can see the route it chose — "went via the hill road, hit a diversion, waited at a level crossing" — you know precisely what to change.

EXPLAIN is the route. EXPLAIN ANALYZE is the route plus what actually happened, and the gap between the two is where the answer usually lives: the engine expected 300 rows and got 4 million, so it chose a strategy that only makes sense for 300.

Reading a plan: bottom-up, inside-out

EXPLAIN ANALYZE
SELECT c.city, count(*)
FROM rides r JOIN cities c ON r.city_id = c.id
WHERE r.ride_date >= '2026-07-01'
GROUP BY c.city;

HashAggregate  (cost=8420 rows=12)  (actual time=910 rows=12 loops=1)
  ->  Hash Join  (cost=8100 rows=402000)  (actual time=880 rows=3980221)
        Hash Cond: (r.city_id = c.id)
        ->  Seq Scan on rides r  (cost=7200 rows=402000)
                                 (actual time=520 rows=3980221)
              Filter: (ride_date >= '2026-07-01')
              Rows Removed by Filter: 36019779
        ->  Hash  (cost=12 rows=12) (actual rows=12)
              ->  Seq Scan on cities c

Read the deepest indentation first — that runs first, and results flow upward.

1. Seq Scan on rides — reading the whole table, filtering after
2. Seq Scan on cities → built into a hash table (tiny, fine)
3. Hash Join — probe the hash with each ride
4. HashAggregate — group and count

The three things to look at, in order:

• rows=402000 vs actual rows=3980221 — the estimate is 10× low. Every decision after this was made on bad information
• Rows Removed by Filter: 36,019,779 — it read 40M rows to keep 4M. That filter should have been an index or partition
• Seq Scan on a large table with a selective filter — the headline symptom

Step through the plan

The fix follows directly from the plan: index or partition on ride_date so the 36 million discarded rows are never read, and ANALYZE so the estimate stops being 10× wrong.

The node types worth recognising

Access — how a table is read

| Node | Meaning | Concern? | | --- | --- | --- | | Seq Scan / Full Table Scan | read everything | ⚠️ only if the filter is selective | | Index Scan | walk the index, fetch rows | ✅ | | Index Only Scan | answered entirely from the index | ✅✅ ideal | | Bitmap Heap Scan | many index matches, fetched in page order | ✅ normal for mid-selectivity |

Joins — and this is where plans go wrong

| Node | Good when | Bad when | | --- | --- | --- | | Nested Loop | one side is tiny | both sides large → O(n×m) | | Hash Join | one side fits in memory | it spills to disk | | Merge Join | both already sorted | it must sort first |

A Nested Loop with a large actual rows on both sides is the classic disaster — usually caused by an estimate that said one side would be small.

Other signals

• Sort with Sort Method: external merge Disk: 240MB — ran out of memory, spilled to disk
• Rows Removed by Filter — work done and discarded
• loops=4000000 on an inner node — that node ran four million times

Two plans, same query

The same query, run on Monday and again on Friday after a data load.

Monday — 40ms

Nested Loop (actual rows=210 loops=1)
  -> Index Scan on drivers (actual rows=210)
  -> Index Scan on rides (actual rows=1 loops=210)

Friday — 95 seconds

Nested Loop (est rows=200 / actual rows=2100000 loops=1)
  -> Seq Scan on drivers (est 200 / actual 40000)
  -> Index Scan on rides (actual rows=52 loops=40000)

Plans in a warehouse look different

EXPLAIN exists in Snowflake, BigQuery and Spark too, but the vocabulary changes and so do the problems.

What you look for in a warehouse plan:

• Partitions scanned vs total — Snowflake's query profile shows partitions scanned / partitions total. If you filtered by date and it still scanned all of them, your pruning failed
• Bytes shuffled — data crossing the network between nodes. Usually the dominant cost, and caused by joins and aggregations on high-cardinality keys
• Spilling to local/remote storage — the warehouse ran out of memory. Remote spilling is catastrophic for runtime
• Skew — one node processing far more than the others. In Spark's UI this is a task duration distribution with one long bar

The mapping from the OLTP world:

| OLTP concern | Warehouse equivalent | | --- | --- | | Seq Scan on a big table | scanning all partitions — pruning failed | | Missing index | missing partition filter or clustering key | | Nested loop blowup | broadcast join that should have been a shuffle, or vice versa | | Sort spilling to disk | spilling to remote storage |

The skill transfers exactly: read the plan, find the step that touches the most data, ask why it's touching that much. Only the node names change.

Read plans bottom-up, deepest indentation first.

Compare est rows with actual rows. A big gap means bad statistics — and every decision after it is wrong.

Rows Removed by Filter is work you paid for and threw away.

Words to know

In two minutes

• EXPLAIN shows the plan; EXPLAIN ANALYZE runs the query and adds actual rows and time per node.
• Read bottom-up: the deepest node runs first and its output flows to its parent.
• Check three things: which node eats the time, est vs actual rows, and rows read then thrown away.
• A large est/actual gap means stale or missing statistics, and every choice above it (join type, memory) is suspect — run ANALYZE.
• Nested Loop is great for small outer sides and a disaster for big ones; Hash Join suits large unsorted inputs.
• Per-loop numbers multiply by loops; a cheap-looking inner node can be the whole runtime.
• Warehouse plans: look at partitions scanned vs total, bytes shuffled and spill to remote storage.

Test yourself

There are 9 questions, 3 puzzles and 7 recall cards for this lesson in the app — the recall cards come back on later days, just before you would forget.