In an execution-time plan, one operator estimated 50 rows but actually produced 2 million. What does that gap tell you, what typically causes it, and how does it make the query slow?
answer
- cardinality drives algorithm, order, path, memory
- find the LOWEST diverging node
- correlated predicates multiplied as independent
- function over a column blinds the histogram
- errors compound up the join tree
basics
~20 sIt is a cardinality misestimate: the optimizer planned for a tiny input and got a huge one, so it likely chose the wrong join method, join order and memory grant. Causes are stale or missing statistics, correlated predicates, and predicates the optimizer cannot see through. Find the lowest node where the gap starts.
solid answer
~50 sThe optimizer chooses join algorithms, join order, access paths and memory budgets from estimated cardinalities. A 40,000x underestimate means every one of those choices was made for the wrong problem: a nested loop that is ideal for 50 rows becomes 2 million index probes, a sort or hash gets a memory budget sized for 50 rows and spills, and the join order puts a huge intermediate result in the wrong place. Typical causes: statistics stale or never gathered after a bulk load; **correlated predicates** the optimizer multiplies as if independent (city and postcode); predicates it cannot see through - a function or expression over a column, a value hidden behind concatenation, a join on a derived expression; skewed data outside the histogram's resolution; and estimates over intermediate results such as aggregates or recursive parts, where errors compound multiplicatively up the tree. Diagnostically, always find the **lowest** node where estimate and actual diverge. Everything above it inherits the error, so fixing the top node is treating a symptom.
code
sql · 7 lines-- opaque: statistics on created_at cannot be used
SELECT * FROM orders WHERE date_trunc('month', created_at) = DATE '2026-03-01';
-- sargable: range over the raw column, histogram applies
SELECT * FROM orders
WHERE created_at >= DATE '2026-03-01'
AND created_at < DATE '2026-04-01';go deeper
Recognise that the plan reports both an expected and an actual row count, and that a large difference means the optimizer planned for the wrong amount of data.
Name the main causes - stale statistics, correlated predicates, expressions over columns - and one consequence, typically a nested loop executed far too many times.
Show the diagnostic method: find the lowest diverging node, classify the cause, fix estimation before changing physical design, and verify by measurement.
Discuss it as a systemic risk - estimation error compounds up join trees, so architecture that keeps queries shallow, keeps statistics maintenance automated, and monitors plan regressions matters more than tuning any single query.
## Why cardinality is the master variable A cost-based optimizer works in two stages: estimate how many rows flow along each edge of the plan, then price the operators that consume those rows. The pricing model is fairly robust. The estimation is not. Practically every dramatic plan regression traces back to a cardinality estimate that was wrong by orders of magnitude, because a wrong row count silently converts into wrong *structural* decisions: - **Join algorithm**: 50 rows justify a nested loop with an index probe per row. Two million rows make that 2 million random probes, where a single hash join scanning both sides once would have been dramatically cheaper. - **Join order**: optimizers try to keep intermediate results small. If the first join's output is underestimated, the whole join sequence is built around a mistake. - **Access path**: an index range scan plus row lookups beats a full scan only up to a selectivity threshold. Underestimate the row count and the engine picks the index, then performs millions of random lookups. - **Memory**: sort and hash operators are granted memory from the estimate. Fifty rows' worth of memory for 2 million rows means spilling to temporary storage, often with multiple passes. ## Where the estimates come from Statistics normally include table row count and physical size, per-column number of distinct values, null fraction, most-common-value lists, and a histogram of the value distribution. From these the optimizer computes a **selectivity** for each predicate and multiplies row counts by it. ## The classic causes of a large gap 1. **Stale or missing statistics.** A table bulk-loaded or grown 100x since the last statistics gathering still looks small. Freshly created tables and temporary/intermediate tables are common offenders. 2. **Correlated predicates.** `country = 'FR' AND city = 'Paris'` is treated as two independent filters and their selectivities multiplied, producing an estimate far below reality because the two columns are highly correlated. Multi-column or extended statistics exist precisely for this. 3. **Predicates the optimizer cannot see through.** Wrapping a column in a function or expression, comparing against an opaque value, or joining on a computed expression makes the histogram unusable, so the engine falls back on fixed default guesses (often a hard-coded fraction of the table). 4. **Skew beyond histogram resolution.** A histogram has a limited number of buckets. If one value out of millions accounts for 40% of the rows and is not captured in the most-common-value list, queries for that value are badly underestimated - and, symmetrically, queries for rare values may be overestimated. 5. **Out-of-range values.** Predicates on a monotonically increasing column (a timestamp, an identity key) beyond the range recorded in statistics get very small estimates, which is why 'today's rows' queries misbehave right after a load. 6. **Derived inputs.** Estimates over the output of an aggregate, a set operation, a recursive part or a multi-level subquery are much weaker than estimates over a base table, and the error compounds: a 10x error at one join becomes 100x two joins up. ## How to diagnose Walk the tree bottom-up and find the **lowest** node whose estimate and actual differ by more than roughly an order of magnitude. That node is where the error is born; every ancestor merely inherits and amplifies it. Then ask which of the causes above applies: is the base-table estimate wrong (statistics), or is the base table fine and the estimate collapses only after a multi-predicate filter (correlation), or after a join (join-selectivity model), or over a derived input? Be careful about direction. Underestimates typically cause nested loops, index-driven access and spills - death by repetition. Overestimates typically cause needless hash joins, full scans, over-large memory grants that starve other sessions, and unnecessary parallelism. Both matter, but underestimates usually hurt far more. ## How to close the gap - Refresh statistics, and increase the sample or histogram resolution on skewed columns. - Create multi-column/extended statistics for correlated predicate groups. - Rewrite predicates so the column is exposed directly rather than wrapped in an expression; or store the computed value as a column and index it, so statistics exist for it. - Break a query so the optimizer estimates over a materialized intermediate result whose real size it can measure, when the engine supports that. - Add an index that changes the shape, but only after you know the estimate is right - adding an index while the estimate is wrong just gives the optimizer a new way to be wrong. Finally, validate by re-running and comparing actual rows and exclusive times, not by comparing cost numbers. Cost is computed from the same estimates you just distrusted.
- Is an overestimate as harmful as an underestimate?Usually less so, but not harmless. Overestimates push the optimizer toward hash joins, full scans, oversized memory grants and unnecessary parallelism, which wastes resources and can starve concurrent sessions. Underestimates are worse because they produce nested loops executed millions of times and undersized memory that spills, turning a linear plan into a quadratic one.
- Statistics are fresh and the estimate is still wrong by 1000x on a two-predicate filter. What now?That signature points at correlation rather than staleness: the optimizer multiplied two selectivities as if the columns were independent. The fix is multi-column or extended statistics over that column group so the engine knows the real combined selectivity, or a rewrite that removes the redundant predicate. If the engine cannot express that, materializing the filtered set or restructuring the query so the join order is forced becomes the fallback.
saying these in an interview costs you the question
- Treating the gap at the top node as the problem instead of tracing to the lowest diverging node
- Assuming refreshing statistics fixes every misestimate, including correlation
- Believing the optimizer can use an index or histogram on a column wrapped in a function
- Adding an index as the first response to any misestimate
- Judging the fix by a lower cost number rather than by measured rows and time