The same query. The same data. 43 seconds on Monday, 12 milliseconds on Tuesday. Nothing changed except one CREATE INDEX statement someone ran overnight — a 3,500× speedup from a single line. Indexes are the closest thing databases have to magic, and EXPLAIN is how you see the trick.
What you'll learn
What
An index is a separate, sorted data structure (almost always a B-tree) that lets the database jump to matching rows instead of scanning the whole table. You'll learn how B-trees work, how to read execution plans with EXPLAIN and EXPLAIN ANALYZE, why indexes have costs (writes, storage), and the query-writing rules — sargability, composite-column order, selectivity — that decide whether your index actually gets used.
Why
Query performance is the difference between an interactive dashboard and a spinner, between a nightly job finishing at 2am or 2pm. Analysts who can read a plan debug their own slow queries instead of filing tickets; engineers who understand index costs don't index every column 'to be safe' and wonder why writes crawl. And 'why is this query slow?' is among the most common senior SQL interview questions.
Where it's used
Every slow-query investigation, dashboard latency budget, index review on a growing table, ORM-generated query audit, and the EXPLAIN screenshot in half of all database incident postmortems.
Where this runs in production
Database engineers review EXPLAIN plans in pull requests for hot-path queries — a missing index on a table this size is a production incident, not a slow page.
Early engineering posts documented how careful composite indexes on (user_id, created_at) style patterns kept feed and media lookups fast across billions of rows.
Its Database Monitoring product surfaces execution plans and index suggestions automatically — an entire commercial product built on the skill this lesson teaches.
Finding 'sargable' in a 900-page book by reading every page is a sequential scan. Flipping to the alphabetized index, finding 'sargable → p. 412', and jumping there is an index seek. Note what the book index also teaches: it's SEPARATE from the text (extra pages = storage cost), it must be updated when the book is revised (write cost), and it only helps for lookups it was built for — an index of TERMS won't help you find all pages with diagrams.
CREATE INDEX idx_orders_customer ON orders (customer_id); EXPLAIN SELECT * FROM orders WHERE customer_id = 4211; -- QUERY PLAN -- ------------------------------------------------------------ -- Index Scan using idx_orders_customer on orders -- (cost=0.43..8.45 rows=12 width=64) -- Index Cond: (customer_id = 4211)
EXPLAIN shows the planner's chosen strategy WITHOUT running the query. Read: scan type (Index Scan — the win; Seq Scan on a big table with a selective filter — the smell), cost=startup..total in arbitrary planner units, rows = ESTIMATED matches, and the condition pushed into the index. The planner chooses by comparing estimated costs — it's free to ignore your index if it predicts a scan is cheaper.
Plain EXPLAIN is a forecast. EXPLAIN ANALYZE runs the query and reports actual time and actual rows next to the estimates. The single most diagnostic pattern: estimated rows=40, actual rows=800000 — stale or insufficient statistics led the planner to a terrible strategy (e.g. a nested loop that runs 800k times). Fix with ANALYZE tablename to refresh stats. Caution: EXPLAIN ANALYZE executes the statement — wrap DML in a transaction you roll back.
An index stores RAW column values, so the column must stand bare on one side of the comparison. WHERE created_at >= '2026-07-01' AND created_at < '2026-08-01' → seek. WHERE DATE_TRUNC('month', created_at) = '2026-07-01' → the engine must compute the function per row: full scan. Same disease: LOWER(email) = …, price * 1.2 > 100, LIKE '%gmail' (leading wildcard defeats sorted order; 'anna%' is fine). Cures: move math to the constant side, rewrite dates as half-open ranges, or build an expression index ON t (LOWER(email)) that stores the computed value.
CREATE INDEX ON orders (customer_id, created_at) sorts by customer_id first, then created_at within each customer — like a phone book sorted by (last name, first name). It serves: filters on customer_id alone; on customer_id AND created_at (the sweet spot: one customer's date range is a contiguous run of leaf pages); but NOT created_at alone — dates are scattered across all customers, like finding everyone named 'Anna' regardless of surname. Column ORDER is therefore a design decision: equality-filtered columns first, range-filtered columns last. One composite (a, b) generally beats separate indexes on a and b for combined queries.
New analysts see 'Seq Scan' next to an existing index and assume the database is broken. Usually the planner did the math: fetching 40% of a table via index means bouncing between index and table pages randomly — slower than reading the table straight through. Small tables, low-selectivity filters, and queries returning most rows all legitimately favor scans. Trust EXPLAIN ANALYZE's actual times over intuition; investigate only when estimates and reality diverge, or when a selective filter still scans.
The life of WHERE customer_id = 4211 AND created_at >= '2026-07-01' — from SQL text to leaf pages. Click each stage.
orders has 5M rows; support constantly looks up a customer's orders. Measure, index, measure again.
The tell-tale line is 'Rows Removed by Filter: 4,999,988' — the engine read five million rows to keep twelve. A highly selective filter (12 of 5M) doing a Seq Scan is exactly the case an index exists for.
Your task
The query below runs constantly and crawls: it finds one customer's 2026 order revenue, but its predicate defeats the index on (customer_id, created_at). Fill in the blanks to (1) make the date filter sargable with a half-open range and (2) verify with EXPLAIN that both conditions reach Index Cond.
QUERY PLAN
------------------------------------------------------------
Aggregate (cost=8.47..8.48 rows=1 width=32)
-> Index Scan using idx_orders_cust_date on orders
Index Cond: ((customer_id = 4211) AND
(created_at >= '2026-01-01') AND
(created_at < '2027-01-01'))
Execution Time: 0.093 ms
(5 rows)Write your solution in the editor on the right, then hit Run.
Why does a B-tree index make WHERE email = '...' fast on a huge table?
What does EXPLAIN ANALYZE add over plain EXPLAIN?
An index exists on orders(created_at), yet WHERE EXTRACT(YEAR FROM created_at) = 2026 does a Seq Scan. Why?
Given CREATE INDEX ON orders (customer_id, created_at), which query gets the LEAST help from it?
What's the actual diagnosis and first fix?
The hot query is: SELECT order_id, amount FROM orders WHERE status = 'pending' AND created_at >= (some date) ORDER BY created_at — and only ~1% of orders are pending. Write (1) a single PARTIAL index shaped exactly to this workload (index created_at, restrict to pending) and (2) the EXPLAIN command for the hot query with '2026-07-01' as the date, to verify it's used.
A production query is slow. Walk me through your process.
First, reproduce and measure: run EXPLAIN ANALYZE (in a transaction if it's DML) to get the real plan with actual times, and find where the time concentrates — one node usually dominates. Second, check estimates against actuals at that node: a large gap (est 200, actual 800k) means stale statistics, fixed with ANALYZE before touching anything else — many 'index problems' are really planner-misinformation problems. Third, if estimates are honest, examine the expensive node: a Seq Scan under a selective filter suggests a missing index or a non-sargable predicate — functions wrapped around the column, leading-wildcard LIKEs, or type mismatches — which I fix by rewriting to ranges or adding an expression index. Fourth, consider the workload, not just this query: would a composite index (equality columns first, range/sort last) serve it and its siblings? Is a partial index better for a skewed filter? Finally, verify with the same EXPLAIN ANALYZE and record the before/after — and I'd note what I deliberately DIDN'T do: sprinkle indexes speculatively, because each one taxes every write to the table forever.
Why not index every column, to be safe? What does an index actually cost?
Three costs. Write amplification: every INSERT, DELETE, and most UPDATEs must modify every index on the table — eight indexes turn one logical write into nine physical ones, and hot OLTP tables feel it directly (bulk loads often drop and rebuild indexes for exactly this reason). Storage and cache: indexes are real disk — routinely rivaling the table — and they compete for the buffer cache, evicting pages that useful queries needed. Planner and maintenance overhead: more options to cost, more structures to vacuum and keep statistics for, and unused indexes that linger for years because nobody is sure they're safe to drop (pg_stat_user_indexes' idx_scan counter answers that). And the payoff side is bounded anyway: low-selectivity columns — status with three values — rarely reward a plain index, since fetching 30% of a table through an index is slower than scanning it. So the craft is workload-driven: index foreign keys and the filters/sorts of your hottest queries, prefer one well-ordered composite over several singles, use partial indexes for skewed predicates, and audit usage stats periodically. 'Safe' is a measured index budget, not maximum coverage.
Design the indexing for a messages table: 100M rows, queries are (a) unread messages for a user, newest first; (b) a conversation's messages by time; (c) rare admin full-text search. Writes are heavy.
Start from the queries, spend the write budget deliberately. Query (a): a partial composite — CREATE INDEX ON messages (recipient_id, created_at DESC) WHERE read = false. Unread is a tiny, shrinking fraction of 100M rows, so the partial keeps it small and every marked-read message eventually LEAVES the index; equality column first, then the sort column, so 'newest 50 unread' is a contiguous walk with no Sort node. Query (b): CREATE INDEX ON messages (conversation_id, created_at) — same equality-then-range logic; this also covers the foreign-key side of joins to conversations. Query (c): rare admin search doesn't justify a per-write tax on a hot table — either a GIN full-text index only if measurements show admins truly need it interactive, or better, push search to a replica or external system (which is what heavy-write shops actually do). Under write-heavy load I'd stop at those two B-trees plus the primary key, resist 'while we're at it' additions, and after a month check idx_scan stats to confirm both earn their keep. If asked to go further: BRIN on created_at for archival range scans is nearly free, and time-partitioning becomes the conversation at the next order of magnitude.
Common Mistakes to Avoid
1) Wrapping indexed columns in functions — EXTRACT(YEAR …)=2026, LOWER(email)=… force scans; rewrite as ranges or add expression indexes. 2) Assuming Seq Scan means something's broken — for small tables or unselective filters the planner is right; check estimate-vs-actual before 'fixing'. 3) Composite column order by intuition — equality columns first, range/sort columns last; (created_at, customer_id) serves customer lookups terribly. 4) Indexing everything — each index taxes every write and competes for cache; unused indexes are pure cost (check idx_scan). 5) Diagnosing with EXPLAIN alone — only ANALYZE exposes actual rows/times, and stale statistics (fixed by running ANALYZE on the table) cause more bad plans than missing indexes.
Ask the AI Tutor
Try these prompts in the AI Tutor panel: • 'ELI5 how a B-tree finds one row among a billion in 4 hops.' • 'Paste me a gnarly EXPLAIN ANALYZE output and walk me through reading it node by node.' • 'Quiz me: show a WHERE clause, I say sargable or not, and fix the broken ones.' • 'Give me 5 query patterns and make me design the composite index, defending column order.' • 'Interview mode: a query got slow after data growth — make me diagnose it step by step without hints.'
Glossary
Index — a separate sorted structure enabling seeks instead of scans. B-tree — the default index: shallow, sorted, O(log n) lookups, supports equality and ranges. Sequential (Seq) Scan — reading the whole table; correct for unselective queries. Index Scan / Index Cond — walking the B-tree; conditions satisfied inside the walk. Index-only scan — query answered entirely from index pages (covering index; INCLUDE adds payload columns). EXPLAIN / EXPLAIN ANALYZE — plan forecast / plan with actual execution times and row counts. Planner statistics — table metadata (row counts, distributions) behind cost estimates; refreshed by ANALYZE. Selectivity — fraction of rows a predicate matches; low selectivity undermines plain indexes. Sargable — predicate form (bare column vs constant) that permits index use. Composite index — multi-column index; usable via its leftmost prefix, equality columns first. Expression index — index over a computed expression like LOWER(email). Partial index — index restricted by a WHERE clause to the interesting slice of rows. Write amplification — every table write updating every index. Nested Loop / Hash Join — join strategies for small vs large row sets; chosen by estimated cost.
Recommended Resources
• Essential: 'Use The Index, Luke!' (use-the-index-luke.com) — Markus Winand's free book on indexing across databases; the sargability chapters alone are worth the visit. • Docs: PostgreSQL manual 'Using EXPLAIN' — read alongside a real plan. • Tool: explain.depesz.com and explain.dalibo.com render pasted plans visually with hot nodes highlighted. • Practice: pick your slowest personal-project query, run EXPLAIN ANALYZE, find where the time goes, and get to a before/after pair. • Next in DSM: reads are now fast — Transactions & ACID covers the write side: how databases keep concurrent changes correct when everyone writes at once.
Recap
✓ B-tree indexes turn O(n) scans into O(log n) seeks by keeping values sorted in a shallow tree. ✓ EXPLAIN forecasts; EXPLAIN ANALYZE executes and shows actual times — estimate-vs-actual gaps mean stale statistics (run ANALYZE) and cause more bad plans than missing indexes. ✓ Sargability: keep the column bare in predicates — functions around it (EXTRACT, LOWER, math) force scans; use half-open ranges or expression indexes. ✓ Composite indexes follow the leftmost-prefix rule: equality columns first, range/sort columns last; one good composite beats several singles. ✓ Indexes cost writes (amplification), storage, and cache — index the workload's hot filters and foreign keys, use partial indexes for skewed predicates, audit usage. ✓ Seq Scan is often the RIGHT answer — trust measured plans over intuition. Next up: Transactions & ACID. Fast queries are half the story — next you'll see how databases keep data CORRECT under concurrent writes: atomic transactions, isolation levels, and why your bank balance never half-updates.
Run your code to see the output here.