After a large bulk load, a query that had reliably used an index started doing full table scans. How do optimizer statistics drive that decision, and how would you diagnose and correct a cardinality misestimate in production?
answer
- estimate quality drives plan quality
- histogram, distinct count, common values, correlation
- out-of-range after bulk load = estimate of one
- first node where estimated diverges from actual
- independence assumption breaks on correlated columns
basics
~20 sThe optimizer estimates matching rows from sampled statistics - histograms, distinct-value counts, common values - then costs plans. Stale or out-of-range statistics make the estimate wrong, so it prices the index badly. Diagnose by comparing estimated with actual rows in the plan, then refresh statistics and add multi-column ones for correlated predicates.
solid answer
~60 sCosts are derived from estimated row counts, and estimates come from statistics collected by sampling: a histogram of value distribution, the number of distinct values, the most common values and their frequencies, and null fraction. A bulk load breaks this two ways. First, the collected picture is simply out of date until the auto-analyze threshold trips - and that threshold is usually a fraction of table size, so on a huge table it can lag a long time. Second, if the loaded rows extend a monotonically increasing column beyond the recorded maximum, predicates on the newest data fall outside the histogram and the engine estimates almost no rows or applies a decaying guess. Diagnosis is mechanical: capture the plan with actual row counts and find the first node where estimated and actual diverge sharply - errors compound upward. Then refresh statistics on that table, ideally with a larger sample for skewed columns, and re-plan. If the estimate is still wrong, the cause is usually correlation between columns, which the optimizer models as independent; multi-column or expression statistics fix that.
code
text · 7 linesNested Loop (estimated rows=1 actual rows=1,842,003 loops=1)
-> Index Scan on orders_created_at_idx
Index Cond: (created_at >= '2026-08-12')
(estimated rows=1 actual rows=1,842,003)
-> Index Scan on customers_pkey
Index Cond: (id = orders.customer_id)
(estimated rows=1 actual rows=1 loops=1,842,003)go deeper
Know that the optimizer guesses row counts from collected statistics and that a big data change can make those guesses stale, so refreshing them can restore the old plan.
Explain histograms and distinct-value counts, and the out-of-range problem for growing timestamp or sequence columns after a load.
Lead with the estimated-versus-actual walk to the first diverging node, cover correlation and sampling depth, and refresh statistics as part of the load job.
Own it as an operational policy: per-table refresh thresholds and sample targets, statistics as a pipeline step, drift monitoring on top queries, and a governed exception path for plan pinning.
## What statistics are A cost-based optimizer never reads the data while planning. It works from a compact statistical summary, refreshed periodically, typically holding: table row count and page count; per-column null fraction; number of distinct values; a list of most common values with frequencies to capture skew; a histogram of bucket boundaries describing the rest of the distribution; and a measure of how well the physical row order correlates with the column's sort order. From these it estimates the selectivity of each predicate, multiplies by row count to get cardinality, and prices access paths and join methods. The key insight for interviews: **plan quality is downstream of estimate quality.** A wrong estimate at a leaf node does not produce a slightly worse plan, it produces a categorically wrong one, because the join method and join order chosen above it were selected for a different data volume. ## Why bulk loads break estimates - **Staleness.** Automatic statistics refresh is usually triggered by a change counter crossing a fraction of the table's size. Load ten percent of a billion-row table and you may still be under the threshold; every plan is computed against the pre-load distribution. - **Out-of-range predicates.** Statistics record the observed value range. Timestamps, sequences, and identity keys grow past that maximum continuously. A query for "the last hour" then asks about a range the histogram says is empty, and the engine estimates one row - which makes a nested loop with per-row lookups look free even though millions of rows qualify. This is the classic "the report broke after the nightly load" pattern. - **Distribution shift.** A load can change skew - one tenant suddenly holds most of the rows - so the recorded most-common-values list is no longer representative. ## Failures that surviving a refresh will not fix - **Column correlation.** Optimizers multiply per-predicate selectivities as if the columns were independent. Filtering on a city and a postal code that always co-occur gives a product far below the true fraction. The remedy is multi-column or extended statistics that record the joint distribution, where the engine supports it. - **Expressions.** Statistics are collected on columns, not on transformations. A predicate on a computed expression falls back to a fixed default guess unless an index or statistic exists on that expression. - **Parameter values.** With bound parameters the optimizer may plan for an unknown value using average selectivity, or reuse a plan tuned to the first value it saw. On a skewed column that produces one plan that is wrong for most executions. - **Sampling error.** Default sample sizes are small. Columns with heavy skew or very high distinct counts may need a larger sample or an explicit per-column target. ## A production diagnosis sequence 1. Capture the plan **with actual row counts and loop counts**, on the real workload, not a hand-typed literal version. 2. Walk the plan bottom-up and find the first node where estimated and actual rows diverge by an order of magnitude. That node is the cause; everything above is a symptom. 3. Check when statistics were last collected for that table, and the change counter since. 4. Note whether the predicate value sits beyond the recorded range - the out-of-range signature is an estimate of exactly one, or a tiny number, for a large result. 5. Refresh statistics for that table and re-plan. If the plan flips and the estimate is now close, you have your answer. 6. If it does not, test for correlation by measuring the actual selectivity of each predicate separately and together, then add joint statistics. 7. Only then consider structural change: an index on the expression, a partial index for a skewed value, or splitting the hot value into its own query shape. ## Operating it, not just fixing it The durable fix is process. Refresh statistics as the final step of any bulk load or migration, in the same job. Lower the auto-refresh threshold or raise the sample target for large, skewed, or fast-growing tables rather than accepting one-size-fits-all defaults. Monitor for estimate-versus-actual drift on your top queries so regressions surface before users report them. And treat plan-freezing tools as a stopgap for a specific known-bad estimate, with a review date, not as a substitute for maintaining statistics. ## The trap answer "Rebuild the indexes" often appears to work because index maintenance refreshes statistics as a side effect. Believing the fragmentation story leads teams to schedule expensive rebuilds when a cheap statistics refresh was the actual remedy.
- Why does an estimate of one row so often produce a catastrophically slow plan rather than a slightly slower one?An estimate of one makes a nested loop with an index lookup on the inner side look nearly free, so the optimizer picks it and also drops any hashing or sorting it would otherwise choose. If the real count is a million, that lookup executes a million times, each a tree descent plus a possible random page fetch. The error is multiplicative, which is why estimate errors near the leaves matter far more than errors near the root.
- Statistics are fresh but the estimate is still off by two orders of magnitude on a two-column filter. What now?Suspect correlation: the optimizer multiplies the two selectivities as if independent, and strongly related columns such as city and postal code make that product far too small. Measure each predicate's real selectivity alone and together to confirm. The fix is multi-column or extended statistics capturing the joint distribution, or, where unavailable, restructuring so one column implies the other or indexing the combination.
saying these in an interview costs you the question
- Blaming index fragmentation for a plan flip when the estimate was wrong
- Believing automatic statistics refresh always keeps up with large tables
- Not knowing that predicates beyond the recorded value range collapse to a near-zero estimate
- Assuming the optimizer accounts for correlation between columns by default
- Pinning the plan with a hint before ever comparing estimated with actual rows