An interviewer pastes a query that joins orders to customers, filters on a date range, and takes eight seconds. They ask what you’d look at first. The candidate who says “add an index” has skipped the only question that matters: what plan is the database running right now, and why did it pick that one? SQL is declarative. You describe the result you want, and a piece of software you never see decides how to get it. That software is the query optimizer, and knowing how it thinks is what these rounds are really testing.
This skill shows up most in SQL and database screens, where you’re handed a slow query and asked to make it fast. It resurfaces in a system design round the moment your data outgrows a single node and the optimizer can no longer see the whole picture, which is the harder version we’ll get to at the end.
The four things that happen before a row comes back
Every mainstream engine (Postgres, MySQL/InnoDB, SQL Server, Oracle) runs the same rough pipeline. First the parser turns your text into a tree and rejects syntax errors. Then a rewrite stage normalizes the tree: it flattens some subqueries, expands views, folds constants, and applies transformations that don’t change the result but give the planner more freedom. Then the planner/optimizer generates candidate execution plans and scores each one. Finally the executor runs the winning plan, pulling rows through operators like scans, joins, sorts, and aggregates.
The interesting stage is the third one. For a three-table join there might be dozens of valid plans: which table to read first, whether to use an index or scan, which join algorithm to use, whether to sort now or later. The optimizer’s job is to pick one without running any of them. It does that with a cost model.
The cost model is an estimate of work, not time
The concrete numbers and tooling from here down are Postgres; the shape of the reasoning carries across engines, but the specific constants and command output do not. Cost is a unitless number. Postgres builds it from a handful of tunable constants: reading a page sequentially costs 1.0 by default, a random page read costs 4.0, processing a row in the CPU costs 0.01, and so on. The planner adds these up for every operator in a candidate plan and compares the totals. The plan with the lowest estimated cost wins, even if a different plan would have been faster in reality.
That gap between estimated cost and real time is where most trouble lives. The constants assume things about your hardware (that random reads are four times slower than sequential, which is roughly true for spinning disks and wildly pessimistic for NVMe SSDs). On fast storage, an out-of-the-box optimizer will sometimes reject an index scan it should have used, because it overprices the random I/O the index would cause. This is a genuinely good thing to mention: the cost model is a model, and you can be right about the query while it is wrong about your disk.
Cardinality estimation is where plans go wrong
Everything the optimizer decides rides on one guess: how many rows will each step produce? That guess is the cardinality estimate (cardinality is just the planner’s guess at how many rows a step returns), and it’s derived from statistics the database collects in the background (in Postgres via ANALYZE, which samples rows to build per-column histograms, a bucketed picture of how a column’s values are distributed, plus a list of the most common values, the specific values that appear most often stored with their frequencies).
Selectivity is the fraction of rows a predicate keeps. For WHERE status = 'shipped', the optimizer looks up how common 'shipped' is and multiplies by the table’s row count. When the column is roughly uniform and the stats are fresh, the estimate is close. It falls apart in a few predictable ways, and interviewers like all of them:
- Statistics go stale. You bulk-load ten million rows, the histograms still describe yesterday’s table, and the planner estimates a hundred rows where there are now a million.
- Columns are correlated. The optimizer assumes
city = 'Boston'andstate = 'MA'are independent, multiplies their selectivities, and estimates far too few rows because nearly every Boston row is already in MA. - A function wraps the column.
WHERE date_trunc('day', created_at) = '2026-06-01'leaves the planner with no histogram for the function’s output, so it falls back to a generic guess. - The data is skewed. One customer holds 40% of the orders, so any average-based estimate is meaningless for that value.
A wrong estimate at the bottom of a plan compounds. If the optimizer thinks a filtered table yields 100 rows when it yields 500,000, it will happily choose a nested loop that made sense for 100 rows and is catastrophic for half a million. The index you’d reach for is downstream of this. Knowing when the planner will and won’t use an index is a related skill covered in the database indexing interview guide, and the index-specific traps (leftmost-prefix, low-selectivity columns, type mismatches) are the subject of why the database ignores your index. This piece stays one level up, on the cost model and the row estimates that decide whether any index gets used at all.
Join ordering and the algorithms it picks between
Join ordering is the most expensive decision the optimizer makes, because the number of possible orderings grows factorially with the number of tables. Engines cap the search with heuristics: Postgres switches from exhaustive search to a genetic algorithm once you cross a table-count threshold (12 by default). A join that produces at most one row, say because it hits a primary key, usually gets pushed early so later joins have less to chew on.
Once an order is fixed, each join gets an algorithm. Naming the three and saying when each wins is one of the most common questions in this topic.
| Join algorithm | How it works | Best when | Cost shape | Falls apart when |
|---|---|---|---|---|
| Nested loop | For each row on the outer side, look up matches on the inner side, ideally through an index | Outer side is small and the inner side is indexed on the join key | Roughly outer rows times the per-lookup cost | Outer side is large or the inner has no usable index, degrading toward outer times inner |
| Hash join | Build a hash table on the smaller input, then probe it with rows from the larger input | Large, unindexed, equality joins between two big tables | Roughly linear in the sum of both inputs | The build side doesn’t fit in working memory and spills to disk; it only handles equality predicates |
| Merge join | Sort both inputs on the join key, then walk them in lockstep | Both inputs are already sorted (for example, read in index order) or are large | Dominated by the sort, so n log n unless input is pre-sorted | Inputs are unsorted and large, making the sort the bottleneck |
That trio is Postgres, SQL Server, and Oracle. MySQL/InnoDB is the exception worth flagging: it was nested-loop-only for years, added hash join only in 8.0.18 (2019), still has no merge join, and its EXPLAIN output looks different. So “name the three join algorithms” is a Postgres-shaped answer; on a MySQL-focused loop, the sharper move is knowing the engine historically barely had a choice. The point of the table isn’t memorization either way. It’s to reason from it: a nested loop over a big unindexed inner table is the classic accidental O(n·m) blowup, every outer row scanning every inner row, and it’s exactly what a bad cardinality estimate talks the optimizer into.
Reading EXPLAIN ANALYZE: estimated versus actual rows
EXPLAIN alone shows the plan and the optimizer’s estimates without running anything. EXPLAIN ANALYZE actually executes the query and prints real timings and real row counts next to the estimates. The single most useful habit in the whole topic is comparing those two numbers per node.
Plan output is a tree printed as indented text: each indented node is a child that feeds its parent, so you read from the most-indented line upward. A node tagged loops=N ran N times, once per row from the operator above it, which is how you spot a nested loop that fired far more often than the planner expected.
Nested Loop (cost=0.42..8123.55 rows=1 width=64) (actual time=0.03..2140.9 rows=182004 loops=1)
-> Seq Scan on orders (cost=... rows=1 ...) (actual ... rows=182004 ...)
Filter: (date_trunc('day', created_at) = '2026-06-01')
-> Index Scan using customers_pkey on customers (actual ... loops=182004)Read it inside out. The planner estimated rows=1 from the filtered orders scan; the actual was 182,004. That single bad estimate (the date_trunc wrapper defeated the histogram) convinced it a nested loop was safe, so it probed customers 182,004 times instead of building one hash table. The fix isn’t “add an index,” it’s rewriting the predicate to created_at >= '2026-06-01' AND created_at < '2026-06-02' so the estimate becomes sane, at which point the optimizer will typically switch to a hash join on its own. When you can narrate that, you’ve answered the question.
What interviewers actually push on
Expect a slow query and “how would you debug this,” where the correct opening move is EXPLAIN ANALYZE, not a guess. Expect “the estimated rows and actual rows are far apart, what does that tell you,” which is really asking whether you understand statistics and selectivity. Expect “why would the database ignore an index you added,” where good answers include a low-selectivity predicate (the index would match most of the table, so a scan is cheaper), stale stats, a type mismatch or function on the column, or the cost model overpricing random I/O.
And expect a design-flavored version in a broader system design interview. Once a table is sharded across nodes, each shard’s optimizer plans only against its own local statistics; there is no global cost model that sees the whole dataset. A join across shards can’t stay a local hash join, so the system either ships rows between nodes (network cost the single-node model never priced) or you pick a shard key that keeps joined rows co-located. An optimizer that plans beautifully on one node can produce a cross-node fan-out that saturates the network once the data is partitioned, which is why shard-key choice is really a query-planning decision wearing a schema hat. The tradeoff space shifts again once you compare against SQL versus NoSQL engines that don’t do cost-based planning at all, and the surrounding query-writing skills (window functions, CTEs, subquery shapes the rewriter can and can’t flatten) show up in the same interviews and are collected in these SQL interview questions.
Get comfortable running EXPLAIN ANALYZE on your own database before the interview. Break something on purpose. Wrap a column in a function, delete your stats, force a nested loop, and watch the estimate diverge from reality. You’ll recognize the shape instantly when it shows up on a whiteboard.
Keep sharpening your system design:
