An interviewer pastes a query into the shared editor, tells you it runs in 38 seconds in production, and asks you to make it fast. The junior move is to start guessing at indexes. The move that gets you hired is to ask for the execution plan first, because the plan shows what the database is actually doing, and almost every real slowdown traces back to one choice the planner made that you can point at and explain.
Every mainstream relational database works the same way at a high level. Your SQL gets parsed into a tree, rewritten (views expanded, some subqueries flattened), handed to a cost-based optimizer that weighs many candidate plans, and the cheapest estimated plan wins. Then the executor runs it. The word “cheapest” is carrying most of the weight in that sentence, and understanding how the optimizer measures cost is most of what query tuning actually is.
The planner is guessing, and it guesses from statistics
Postgres, MySQL with InnoDB, SQL Server, and Oracle all use cost-based optimizers. They don’t know how many rows a filter will return. They estimate it from statistics: histograms of column values, the number of distinct values, the fraction of nulls, the physical size of the table. Postgres refreshes these when autovacuum runs ANALYZE. If the stats go stale after a bulk load, the planner is reasoning about a table that no longer exists.
That estimate, the cardinality, drives everything downstream. If the planner thinks a filter returns 50 rows and it actually returns 5 million, it picks a plan that’s perfect for 50 rows and catastrophic for 5 million. This one failure mode sits behind a large share of “the query got slow overnight and nothing changed” incidents. Nothing changed except the data distribution, and the statistics didn’t keep up with it.
Reading EXPLAIN ANALYZE without fooling yourself
EXPLAIN shows the plan the optimizer chose along with its estimates. EXPLAIN ANALYZE actually runs the query and shows estimated versus real numbers side by side, which is the version you want when something is slow. One habit separates people who can tune queries from people who can’t: compare the estimated row count against the actual row count at every node.
EXPLAIN ANALYZE
SELECT o.id, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'US'
AND o.created_at >= '2026-01-01';A plan for that might read like this:
Hash Join (cost=210.0..48000 rows=9500) (actual rows=1240000 loops=1)
Hash Cond: (o.customer_id = c.id)
-> Seq Scan on orders o (actual rows=2000000 loops=1)
Filter: (created_at >= '2026-01-01')
-> Hash (actual rows=8000 loops=1)
-> Index Scan using customers_country_idx on customers c
Index Cond: (country = 'US')Read it inside out. The customers side uses an index and returns 8,000 rows, fine. The orders side is a sequential scan of two million rows, and the planner estimated 9,500 output rows but got 1.24 million. That gap is the whole story. The optimizer picked a hash join because it expected a small result. The real result is huge, so you’re paying to scan and hash the entire orders table. An index on orders(created_at), or better a composite that also carries customer_id, reshapes this plan completely.
Three ways to join, and when each one wins
Interviewers like asking why the database chose one join algorithm over another, because it separates people who memorized “add an index” from people who know what the index is for. Three join strategies are in wide use, and the optimizer picks between them based on table sizes, sort order, and whether a useful index exists.
| Join algorithm | How it works | Best when | Falls apart when |
|---|---|---|---|
| Nested loop join | For each row of the outer table, look up matching rows in the inner table, ideally through an index | Outer side is small and the inner join column is indexed; highly selective queries | Outer side is large and the inner table has no usable index, giving O(n*m) scans |
| Hash join | Build a hash table on the smaller input’s join key, then probe it with rows from the larger input | Large, unsorted inputs joined on equality with no useful index or sort order | Equality joins only; the build side must fit in memory or it spills to disk |
| Merge join | Sort both inputs on the join key, then walk them in lockstep like a zipper | Both inputs already arrive sorted on the join key (read through indexes) or a sort is cheap | Inputs aren’t sorted and both are large enough that sorting them is expensive |
The practical read: a nested loop with an index on the inner table is often the fastest join you can get for a selective query, and the worst possible one when the outer side is large and the inner has no index. That accidental O(n*m) is what turns a 10ms query into a 40-second one. Hash joins are the workhorse for large, unsorted joins on equality. Merge joins do well when both inputs already come sorted, which you get for free when both sides are read through an index on the join key.
Why the index is there and the query still won’t use it
“There’s an index on that column, why is it doing a sequential scan” is a top-five interview question, and it has a few real answers. The first is that the predicate isn’t sargable, meaning you wrapped the indexed column in a function or expression, so the index on the raw column no longer matches what you asked for.
-- index on created_at can't be used here
WHERE DATE(created_at) = '2026-07-01'
-- rewrite so the raw column is compared directly
WHERE created_at >= '2026-07-01'
AND created_at < '2026-07-02'Implicit type casts do the same damage. Compare a varchar column against an integer literal and the database may cast the column instead of the literal, which quietly disables the index. A leading-wildcard LIKE ‘%foo’ can’t use a normal B-tree either, since the tree is ordered left to right and you gave it no prefix to seek on.
The second real answer is that a sequential scan is genuinely cheaper. If your filter matches 40% of the table, jumping back and forth between the index and the heap to fetch each matched row costs more than reading the whole table in order. The planner knows this from the statistics and chooses the scan on purpose. Adding the index changes nothing, because the index was never the problem. This is the answer that impresses, because it shows you treat an index as a cost tradeoff rather than a magic switch.
Covering indexes and the heap fetch you forgot about
A B-tree index stores the indexed columns plus a pointer to the row. If your SELECT needs a column that isn’t in the index, the database takes an extra hop to the table (the heap) for every matched row. When the match set is large, those random heap fetches dominate the cost. A covering index answers the query straight from the index, with no table lookup at all: an index-only scan in Postgres, INCLUDE columns in SQL Server.
-- if the query only reads these three columns,
-- this index answers it without any heap fetch
CREATE INDEX ON orders (customer_id, created_at) INCLUDE (total);This is where “just add an index” stops being good advice. The index that helps is the one that lets the planner seek to the right rows and read everything the query needs in a single pass, in the order the query wants them. Often that’s a composite index, not the single-column index on whatever happens to appear in the WHERE clause.
What they’re actually testing
The through-line in every version of this question is whether you reason from evidence or from folklore. Folklore says add indexes, avoid SELECT *, joins are slow. Evidence says read the plan, find the node where estimated and actual row counts diverge or where a large scan feeds a nested loop, form one hypothesis, change one thing, and re-run EXPLAIN ANALYZE to confirm the change did what you predicted. A few prompts, phrased the way they come up:
- “This query uses an index in staging but a sequential scan in production. Same schema. What changed?” (data volume and statistics; the planner’s cost math flipped)
- “How would you speed up a query that joins five tables and returns in 12 seconds?” (pull the plan, find the join that explodes the row count, look for the missing index or the bad estimate)
- “Your ORM fires a query per row inside a loop. Why is that slow and how do you fix it?” (the N+1 problem; one join or one batched IN instead of hundreds of round trips)
- “When would you choose not to add an index?” (write-heavy tables where every insert maintains the index, low-selectivity columns, or when a sequential scan already wins)
If you can bring up a plan, point at the node that’s lying about its row count, and name the specific change that fixes it, you’ve shown the exact thing the question exists to find. The syntax of EXPLAIN varies by engine and the precise cost numbers don’t matter much. The reasoning does, and it carries across every database you’ll ever touch.
Keep sharpening your system design:
