S043 · Query Planning — EXPLAIN, Execution Plans, Tuning

Module M04: Databases & SQL · Session 43 of 130 · Track: DE 🗺️

What you'll be able to do after this session

• Explain Query Planning to a friend in one minute, out loud, no notes.
• Recognise it when you see it in real code / systems / papers.
• Complete the hands-on exercise at the end without looking up help.

Prerequisites

If you can't answer these in 30 seconds each, redo the linked session first:

• S041 — Indexes — B-Tree Intuition, When to Add

Pre-read (skim before the session)

• 🎥 Intuition (5–15 min) · Secret To Optimizing SQL Queries — Understand The SQL Execution Order — ByteByteGo animates the logical order of query execution. Everything else builds on this.
• 🎥 Deep dive (30–60 min) · Postgres Explain Explained — How Databases Prepare Optimal Query Plans — Hussein Nasser reads real EXPLAIN output line by line.
• 🎥 Hands-on demo (10–20 min) · How do SQL Indexes Work — kudvenkat runs before/after EXPLAIN on SQL Server. Same concepts, different vendor — reinforces the mental model.
• 📖 Canonical article · PostgreSQL Docs — Using EXPLAIN — read this three times over your career. Every plan node explained.
• 📖 Book chapter / tutorial · Use The Index, Luke! — The Where Clause — Markus Winand teaches how the planner picks index vs scan vs join strategy.
• 📖 Engineering blog · explain.dalibo.com — paste any Postgres plan, get a visual flame-graph. Bookmark this tool for life.

(a) Intuition · 5 min

The one-paragraph mental model. Why this concept exists, what problem it solves, and the analogy that makes it stick.

In one sentence: How the database actually runs your query.

A SQL query is declarative: you say what you want ("all customers in Germany who bought > ₹10,000 last month"), not how to get it. The database's query planner (or "optimizer") is the piece that translates your SQL into an actual step-by-step execution recipe — which table to read first, whether to use an index, which join algorithm to pick, whether to sort in memory or spill to disk. For a query with 5 joined tables, there are literally thousands of possible execution orders, and the planner uses statistics about your data (roughly how many rows are in each table, how unique each column is) to pick one it thinks is fastest.

Think of it like Google Maps for your query. You tell it "get me from A to B." It looks at traffic (statistics), road types (indexes), and picks a route (plan). Sometimes it picks a bad route because its traffic data is stale (ANALYZE hasn't run) or because you asked for a weird destination the routing engine doesn't handle well (OR clauses, functions on indexed columns). EXPLAIN is your GPS turn-by-turn view — it shows you exactly which route the planner picked, without actually running the trip.

The gotcha to carry forever: the planner is only as smart as its statistics. After a bulk load, a big delete, or a schema change, run ANALYZE (or let autovacuum do it). A 100x slowdown "for no reason" is almost always a stale-statistics story: the planner thinks a table has 1,000 rows when it has 10 million, picks a nested-loop join, and now every query does 10 million lookups in a loop.

(b) Visual walkthrough · 15 min

Diagrams, worked examples, step-by-step visuals.

EXPLAIN prints a plan as a tree of nodes, executed bottom-up. Each node has:

• Node type (Seq Scan, Index Scan, Nested Loop, Hash Join, Merge Join, Sort, Aggregate…)
• Cost — abstract units, startup..total (only useful for comparison, not real ms)
• Rows — the planner's estimate of how many rows it will produce
• Width — average bytes per row

Add EXPLAIN ANALYZE and the database actually runs the query and adds actual timings and row counts. The single most useful signal: compare estimated vs actual rows. If the planner thought 10 and got 10 million, it picked the wrong plan.

Worked example: SELECT * FROM orders o JOIN customers c ON o.cust_id = c.id WHERE c.country = 'DE'.

The planner chose a nested loop because it estimated only ~500 German customers — so looping and doing 500 indexed lookups on orders is cheap. If Germany had 5 million customers, it would switch to a hash join (build a hash table of German customers in memory, then stream orders past it) or a merge join (sort both sides, walk them in lockstep).

Three join algorithms in one table:

Reading real EXPLAIN output — the anti-pattern to spot: Rows Removed by Filter: 9_998_400 on a Seq Scan. Translation: "I read 10 million rows, threw away 9.99 million, and returned 1,600." Add an index on the filter column.

(c) Hands-on · 20 min

Code you type and run. Not pseudo-code — real, runnable, copy-pasteable.

Ask Postgres to show you its work.

docker run --rm -d --name pg-plan -e POSTGRES_PASSWORD=pw -p 5432:5432 postgres:16
sleep 3
psql "postgres://postgres:pw@localhost:5432/postgres" <<'SQL'
-- Build a realistic mini-schema
CREATE TABLE customers (id serial PRIMARY KEY, name text, country text);
CREATE TABLE orders    (id serial PRIMARY KEY, cust_id int, total numeric, created_at timestamptz DEFAULT now());
 
INSERT INTO customers (name, country)
SELECT 'cust_'||g, (ARRAY['US','IN','DE','JP','BR'])[1+(g%5)]
FROM generate_series(1, 100000) g;
 
INSERT INTO orders (cust_id, total)
SELECT (1+(g%100000)), (random()*1000)::numeric(10,2)
FROM generate_series(1, 1000000) g;
 
-- CRITICAL: refresh statistics after a big load
ANALYZE customers; ANALYZE orders;
 
-- 1. Plain EXPLAIN (planner's guess, no execution)
EXPLAIN
SELECT c.country, sum(o.total)
FROM orders o JOIN customers c ON c.id = o.cust_id
WHERE c.country = 'DE'
GROUP BY c.country;
 
-- 2. EXPLAIN ANALYZE (actually runs the query, shows real timings)
EXPLAIN (ANALYZE, BUFFERS)
SELECT c.country, sum(o.total)
FROM orders o JOIN customers c ON c.id = o.cust_id
WHERE c.country = 'DE'
GROUP BY c.country;
 
-- 3. Add helpful indexes
CREATE INDEX ON customers(country);
CREATE INDEX ON orders(cust_id);
ANALYZE;
 
-- 4. Re-run — plan should switch to Index Scan + Hash Join
EXPLAIN (ANALYZE, BUFFERS)
SELECT c.country, sum(o.total)
FROM orders o JOIN customers c ON c.id = o.cust_id
WHERE c.country = 'DE'
GROUP BY c.country;
 
-- 5. Show what stale stats look like — bulk delete without analyze
DELETE FROM orders WHERE cust_id > 90000;
EXPLAIN (ANALYZE) SELECT count(*) FROM orders WHERE cust_id = 500;
-- Compare row estimate vs actual. Then:
ANALYZE orders;
EXPLAIN (ANALYZE) SELECT count(*) FROM orders WHERE cust_id = 500;
SQL
 
# When done:
docker stop pg-plan

Observe (checklist):

1. In step 1, the plan estimates ~200k rows and picks a Hash Join over sequential scans.
2. In step 2, actual rows and actual ms appear; look for large gaps between estimated and actual.
3. After indexes (step 4), the outer Seq Scan customers becomes Index Scan using customers_country_idx.
4. BUFFERS shows shared hit=… vs shared read=… — hits are pages already in cache, reads are from disk.
5. Between step 5's two EXPLAINs, the row estimate should visibly improve after ANALYZE.

Try this modification: paste any EXPLAIN (ANALYZE, FORMAT JSON) … output into explain.dalibo.com — you get a visual flame graph. Which node in your query dominates the time?

(d) Production reality · 10 min

How this shows up in real systems, gotchas, war stories, common bugs.

The #1 production incident I have personally cleaned up at Microsoft: a Postgres query that ran in 40 ms in staging and 40 seconds in prod. Root cause: prod had 100x more data, planner switched from a Hash Join (which needed 800 MB work_mem) to a disk-spilling Sort Merge Join. Fix was a two-liner: SET LOCAL work_mem = '1GB'; SET LOCAL statement_timeout = '10s'; around that query only. Plans change as data grows. A query that's fine at 10k rows can catastrophically flip its plan at 10M.

Second most common story: the planner picked a nested loop for a "small" table that quietly grew to millions of rows because nobody ran ANALYZE after the last big data migration. In Postgres, autovacuum handles this, but for tables with heavy INSERT + DELETE churn (queues, session tables) autovacuum can fall behind. Explicitly ANALYZE after bulk loads. In MySQL/InnoDB it's ANALYZE TABLE. In SQL Server it's UPDATE STATISTICS.

Every real database team maintains a slow-query log — Postgres has log_min_duration_statement = 500 (log everything over 500 ms), MySQL has slow_query_log. Netflix's data platform surfaces the top 100 slow queries per service every hour. Instagram famously blocks any query estimated to touch > 100k rows with a query hint / lint rule in ORM code.

Two more gotchas that bite everyone eventually: OR across different columns often defeats indexes (rewrite as UNION of two indexed queries), and NOT IN (subquery) can produce surprising plans when NULLs are involved — prefer NOT EXISTS. Finally, ORMs like SQLAlchemy/Hibernate happily generate 5-table joins with functions wrapped around columns; always spot-check the generated SQL with EXPLAIN before it ships. In Django, .explain() is your friend.

(e) Quiz + exercise · 10 min

1. What is the difference between EXPLAIN and EXPLAIN ANALYZE?
2. Name the three main join algorithms and when each is appropriate.
3. Why is a large gap between "estimated rows" and "actual rows" a red flag?
4. When would running ANALYZE (Postgres) or UPDATE STATISTICS (SQL Server) suddenly speed up a slow query?
5. Give one query pattern that defeats the query planner even when the right index exists.

Stretch (connects to S042 — Transactions): if you run EXPLAIN ANALYZE inside a BEGIN … ROLLBACK, does it actually mutate the database? What about EXPLAIN ANALYZE DELETE FROM …? Why is this both useful and dangerous?

Explain-out-loud test

If you can't teach these 3 things to a friend without notes, redo the session:

1. What is Query Planning? (one sentence, no jargon)
2. When would you use it? (one concrete example)
3. When would you NOT use it? (one anti-pattern)

What comes next

• Next session (S044): NoSQL Landscape — KV, Document, Column, Graph
• Previous (S042): Transactions & ACID — Isolation Levels, MVCC
• Hub: The 6-Month Learning Plan

Part of a 130-session evergreen learning series. Session structure: (a) intuition · (b) visual walkthrough · (c) hands-on · (d) production reality · (e) quiz + exercise. Duration: 60 minutes.