What are optimizer statistics in a relational database, and what goes wrong when they are missing or out of date?
answer
- Summary of data in the catalog
- Row count, NDV, null fraction, MCV, histogram
- Planner never reads the data while planning
- Wrong estimates → wrong access path / join
- Correctness never affected, only speed
basics
~20 sThey are summaries of the data — row counts, distinct values per column, null fractions, common values, value distributions — that the query planner reads to guess how many rows each step will produce. Missing or stale statistics make those guesses wrong, so the planner picks a bad access path or join method and the query runs far slower.
solid answer
~50 sOptimizer statistics are a compact **summary of what the data looks like**, stored in the system catalog and refreshed by an ANALYZE-style command or a background auto-refresh. Typical contents: table row count and size, and per column the number of distinct values, the fraction of nulls, a list of the most common values with their frequencies, and a histogram of the remaining distribution. The planner never looks at the actual data while planning. It reads these summaries to estimate **how many rows** each predicate and each join will produce, and those row estimates drive every decision: index scan versus sequential scan, which join algorithm, how much memory to request. When statistics are missing or stale, the estimates are wrong. Classic outcomes: a table that grew from 1,000 to 50 million rows since the last refresh is still treated as tiny, so the planner picks a nested loop that now executes 50 million probes. The query is correct — just orders of magnitude slower.
code
sql · 3 linesCOPY events FROM '/data/events.csv';
ANALYZE events;go deeper
Name the main contents — row count, distinct values, nulls, common values, histogram — and say the planner uses them to estimate row counts, so stale ones cause slow but correct queries.
Connect specific statistics to specific decisions: MCV to skewed equality predicates, histograms to ranges, row count to join method and memory sizing.
Lead with diagnosis — estimated versus actual rows — and with the operational triggers that make statistics go stale, especially bulk loads.
Treat statistics freshness as an SLO on plan stability, and reason about refresh cost, sampling rate and skew handling as a fleet-wide policy.
## What statistics are A cost-based optimizer has to compare candidate plans **before** running any of them. To do that it needs to predict how many rows each step will emit. It cannot scan the data to find out — that would cost as much as executing the query. Instead it consults a small, pre-computed summary of the data called *statistics*, kept in the system catalog. A typical statistics set contains: **Per table** - row count (cardinality) - physical size in pages/blocks **Per column** - **NDV** (number of distinct values) — sometimes stored as its reciprocal, the average selectivity of an equality predicate - **null fraction** — what share of rows are NULL - **most-common-value (MCV) list** — the top-k values and their frequencies, capturing skew - **histogram** — buckets summarizing the distribution of everything not in the MCV list - average value width, and often a correlation figure describing how well the column's order matches physical row order **Per index**, additionally, leaf-page counts and tree height. Statistics are gathered by an explicit command (`ANALYZE` in standard-ish spelling, `UPDATE STATISTICS` / `DBMS_STATS` in other engines) and, in every mainstream engine, by a background process that refreshes them automatically after enough rows change. ## How the planner uses them Every estimate begins with the table's row count and is multiplied down by **selectivity** — the fraction of rows a predicate is expected to keep. - `WHERE status = 'X'`: if `status` has 5 distinct values and no skew, selectivity is roughly 1/5. If `'X'` is in the MCV list, its recorded frequency is used directly — far more accurate. - `WHERE created_at > '2026-01-01'`: the histogram tells the planner what share of values sits above that boundary. - `WHERE email IS NULL`: the null fraction gives the answer directly. The resulting row estimate then drives every downstream choice: 1. **Access path.** Estimated 20 rows out of 10 million? Index scan. Estimated 4 million? Sequential scan, because random I/O per row plus table lookups would cost more than reading everything in order. 2. **Join algorithm.** A small outer input favours index nested-loop; two large inputs favour hash or merge. 3. **Join order.** The cheapest order depends on which intermediate results are small. 4. **Memory grants.** Hash tables and sort buffers are sized from estimated row counts; underestimate and the operator spills to disk. ## What breaks when they are wrong The query still returns correct results — statistics never affect correctness. What breaks is performance, sometimes catastrophically: - **Stale row count after growth.** A table that was 1,000 rows at last refresh and is now 50 million still looks tiny. The planner picks a nested loop, which now performs 50 million inner probes instead of a hash join's single pass. - **Stale boundaries after new data.** A histogram ending at last month's maximum date makes `WHERE created_at > yesterday` look like it matches almost nothing, so the planner chooses a plan sized for a handful of rows and gets millions. - **No statistics at all** (fresh table, restored dump not yet analyzed). The planner falls back to hard-coded default selectivities and guesses — occasionally lucky, usually not. - **Under-estimates cause memory spills**; over-estimates cause needless full scans and over-large memory grants that starve other queries. ## The practical habit The things worth internalizing early: 1. After any **bulk load, restore, or large delete**, refresh statistics explicitly rather than waiting for the background job. 2. When a query mysteriously slows down with no code change, compare **estimated versus actual row counts** in the plan; a large divergence points straight at statistics. 3. Statistics are approximate by design — they are usually built from a **sample**, not a full scan, so small errors are normal and expected. It is the order-of-magnitude errors that matter. 4. Statistics are metadata, not data: they never change results, only plans.
- Do wrong statistics ever produce wrong query results?No. Statistics only influence which plan the optimizer chooses; every candidate plan returns the same rows. Bad statistics cost performance — bad access paths, bad join methods, memory spills — but the answer is always correct.
- Where do statistics come from, and are they exact?They are gathered by an ANALYZE-style command or by an automatic background refresh, and they are normally computed from a random sample of rows rather than a full scan, so they are approximate. Row counts and null fractions extrapolate well from a sample; distinct-value counts are the least reliable.
- How would you tell that a slow query is suffering from bad statistics?Compare the estimated row count at each plan step with the actual row count from an instrumented run. A step estimating tens of rows but producing millions — or the reverse — is the signature. That divergence then explains the downstream choice of nested loop versus hash join, or index scan versus sequential scan.
Like a shop's inventory summary rather than the shelves themselves: the manager plans staffing from the summary, and if it says 'we stock about 50 units' when there are 5 million, the plan is wrong even though the shelves are fine.
saying these in an interview costs you the question
- Thinking statistics affect query results, not just plans
- Believing statistics are recomputed on every query
- Assuming statistics are exact rather than sampled approximations
- Saying an index alone guarantees the index will be used, regardless of statistics
- Refreshing statistics as a superstitious fix without checking estimated-versus-actual rows