skip to content

After a nightly bulk load of several million rows, queries that ran in milliseconds yesterday now do full table scans. Explain the mechanism, and how you would fix and prevent it.

level: seniorimportance: must knowfreq 45%

answer

  1. Statistics are a snapshot; loads outrun the refresh
  2. Stale row count → nested loop with millions of probes
  3. Increasing column → predicate beyond last bucket → estimate ~1 row
  4. Diagnose via estimated vs actual rows
  5. Refresh as the final step of the load job

basics

~20 s

The load changed the data but not the catalog statistics, so the optimizer is planning against yesterday's picture — old row counts and a histogram whose maximum value predates the new rows. Estimates collapse, plans flip. Fix by running an ANALYZE-style refresh on the loaded tables; prevent by making that refresh the last step of the load job.

solid answer

~60 s

Statistics are a snapshot refreshed by an explicit command or a background job that fires after a threshold of changed rows. A bulk load changes millions of rows in minutes, so between the load finishing and the refresh running, the optimizer plans against a stale picture. Two failure shapes dominate: 1. **Stale row count.** A table recorded as small is now large, so a nested loop chosen for a tiny inner input now performs millions of probes. 2. **Out-of-range predicates.** On a monotonically increasing column, the histogram's maximum predates the load. `WHERE created_at >= today` falls beyond the last bucket and estimates near-zero rows; the plan sized for a handful gets millions. **Fix now:** run the statistics refresh on the loaded tables and their indexes, then re-check estimated versus actual rows. **Prevent:** make the refresh an explicit final step of the load job rather than trusting the background threshold, which is proportional to table size and fires late on large tables. For partitioned loads, refresh the touched partitions plus the global/table-level statistics.

code

sql · 5 lines
sql
BEGIN;
INSERT INTO events SELECT * FROM events_staging;
COMMIT;

ANALYZE events;

go deeper

for a junior

Say the load changed the data but not the statistics, so the planner is working from an old picture; running an ANALYZE-style refresh fixes it.

for a middle

Name both failure shapes — stale row count and out-of-range predicates on increasing columns — and diagnose via estimated versus actual rows.

for a senior

Own the operational fix: refresh as the final load step, handle partitioned cases and global statistics, and add staleness monitoring rather than reacting to slow queries.

for a principal

Define statistics freshness as a contract between the ingestion pipeline and the query workload, with thresholds, ownership and alerting, so plan stability is engineered rather than rediscovered each incident.

## The mechanism, step by step 1. **Statistics are a snapshot.** They live in the catalog and are refreshed by an ANALYZE-style command or by an automatic background process triggered when the number of changed rows crosses a threshold — typically a percentage of the table plus a constant. 2. **A bulk load changes a lot in a very short time.** Whether it is `COPY`/`LOAD DATA`, a partition attach, or a large `INSERT ... SELECT`, millions of rows arrive faster than the background refresh reacts. 3. **Queries planned in that window use yesterday's picture.** The optimizer has no way to know the catalog is wrong; it trusts what it reads. 4. **Estimates collapse, and the plan flips.** Because every downstream decision is driven by row estimates, one bad estimate at the bottom cascades into the wrong access path, the wrong join method, and the wrong memory grant. ## The two classic failure shapes **Row-count staleness → wrong join method.** A staging or child table recorded at 5,000 rows now holds 8 million. The optimizer picks an index nested-loop join because the inner input looked tiny. At runtime it performs millions of index probes, each a random I/O, instead of a single hash-join pass. The plan looks reasonable in EXPLAIN and is catastrophic in practice. **Out-of-range predicate → estimate near zero.** This is the more insidious one. Consider `created_at`, `id`, or any monotonically increasing column. Its histogram ends at whatever maximum existed at the last refresh. All the newly loaded rows sit *beyond* the last bucket. A query for today's data therefore falls outside the recorded range, and the optimizer estimates the minimum — often one row. It then chooses a plan optimized for one row (nested loops all the way up, no hash tables, tiny memory grant) and executes it against millions. Runtime can be thousands of times worse than the alternative. A third, quieter shape: **skew shift**. A load that changes which values dominate — a status column where every new row is `'NEW'` — invalidates the MCV list even when the row count barely moves. ## Diagnosing it The signature is unambiguous once you look: run the query with runtime instrumentation and compare **estimated rows versus actual rows** at each plan node. A node estimating 1 and returning 4,000,000 is the whole story; everything above it was chosen for the wrong world. Also check the catalog's recorded last-analyze timestamp and row count against the real count. A 3x divergence is noise; a 1000x divergence is your bug. Avoid the reflex of "add an index" or "rewrite the query" before checking estimates — both are common and both waste a day when the real fix takes seconds. ## Fixing it Run the statistics refresh on the affected tables. It is normally an online operation that takes a sample rather than a full scan, so on a large table it is fast relative to the load itself. Then re-plan the query and confirm the estimates now track actuals. If the load was partition-wise, refresh both the touched partitions **and** the table-level aggregate statistics — engines that keep global statistics separately can otherwise still plan against a stale whole-table picture even though every partition is current. ## Preventing it 1. **Make the refresh the final step of the load job.** This is the single most effective habit. Do not rely on the background threshold: it is proportional to table size, so the larger the table the later it fires — precisely backwards from what you want. 2. **Refresh before the dependent workload starts**, not merely "eventually". If reports run at 03:00, statistics must be current at 02:59. 3. **Raise statistics targets on the columns that matter** — the skewed, heavily-filtered ones — rather than globally. 4. **Watch increasing columns specifically.** They are the out-of-range trap. Some engines can extrapolate beyond the histogram's last bucket; do not assume yours does. 5. **Consider tightening the auto-refresh threshold** on large, frequently-loaded tables so the background job is not the last to know. 6. **Alert on staleness**, not just on slow queries: track time since last refresh and rows changed since last refresh for your top tables, and treat a breach as an operational signal. ## The framing to give *Bulk loads change data faster than statistics catch up. The optimizer plans against the catalog, not the data, so the fix is to refresh — and the real fix is to make refreshing part of the load job rather than something the background process discovers later.*

  • Why is a monotonically increasing column the worst case for stale statistics?
    Because newly loaded values fall beyond the histogram's recorded maximum. A predicate asking for recent data therefore lies entirely outside the known range, and the optimizer estimates the minimum — often a single row. It then builds a plan sized for one row and runs it against millions, which is far worse than a merely imprecise estimate inside the known range.
  • Why not just rely on the automatic background statistics refresh?
    Its trigger threshold is typically proportional to table size, so the bigger and more important the table, the more rows must change before it fires — and it runs after the load, not before the dependent workload. For a nightly batch that must be fast at 03:00, an explicit refresh at the end of the load is the only way to guarantee current statistics when it matters.
  • How do you confirm stale statistics rather than guessing?
    Run the query with runtime row counts and compare estimated versus actual rows at every plan node; an order-of-magnitude divergence at a scan node identifies the culprit. Cross-check the catalog's recorded row count and last-analyze timestamp against reality. Only after that should you consider indexing or rewriting the query.
  • What changes if the load targets a single partition of a partitioned table?
    You must refresh both the touched partition and any table-level or global statistics the engine keeps separately. Otherwise per-partition statistics are current while the whole-table picture the planner consults for cross-partition estimates remains stale, and the same bad-plan symptoms persist.

saying these in an interview costs you the question

  • Blaming the index or dropping and rebuilding it before checking estimated-versus-actual rows
  • Assuming the automatic background refresh always runs in time on large tables
  • Thinking stale statistics could return wrong or missing rows
  • Refreshing only the partition after a partition-wise load and ignoring table-level statistics
  • Adding query hints to force the old plan instead of fixing the estimates

context

resolving links…