What are optimizer statistics in a relational database, and what goes wrong when they are missing or out of date?

juniormust knowfreq 58%

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 s

They 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 s

Optimizer 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.

Optimizer 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 lines

sql

COPY events FROM '/data/events.csv';

ANALYZE events;

go deeper

for a junior

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.

for a middle

Connect specific statistics to specific decisions: MCV to skewed equality predicates, histograms to ranges, row count to join method and memory sizing.

for a senior

Lead with diagnosis — estimated versus actual rows — and with the operational triggers that make statistics go stale, especially bulk loads.

for a principal

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:

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.
Join algorithm. A small outer input favours index nested-loop; two large inputs favour hash or merge.
Join order. The cheapest order depends on which intermediate results are small.
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:

After any bulk load, restore, or large delete, refresh statistics explicitly rather than waiting for the background job.
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.
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.
Statistics are metadata, not data: they never change results, only plans.

## 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

What are optimizer statistics in a relational database, and what goes wrong when they are missing or out of date?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

What statistics are

How the planner uses them

What breaks when they are wrong

The practical habit

▾common follow-ups3 questions

▾analogy~20 s

▾red flags5 flags

§ context

▾related questions11 questions

▾related topics3 topics

What statistics are

How the planner uses them

What breaks when they are wrong

The practical habit

answer

go deeper

context