CSE Roadmap

Common tier · about 2 hours · 5 parts

Part 4 of 5 · 2 min read

Why the optimizer's estimate can be wrong

Stale statistics, correlated columns, and unusual data distributions can all mislead the estimate.

Why the optimizer's estimate can be wrong

Think of the store manager's monthly sales summary suddenly going stale. An unexpected, unprecedented sale event starts, making every staffing prediction based on that summary wrong for the day.

In plain words: a cost estimate can be wrong for several concrete reasons. Statistics can simply be stale, collected before a large batch of new data arrived, understating or overstating the true row counts and distributions. Two columns can be correlated in a way plain, per-column statistics do not capture, such as city and postal_code moving together, causing the optimizer to underestimate how selective a combined condition on both columns actually is. A particular value can be far more common than the stored distribution suggests, called data skew, making an estimate based on an average case wrong for that specific value.

A worked example. A table's statistics were collected before a large batch import doubled its row count; until statistics are refreshed, the optimizer's estimates for every query against this table are based on the old, now-understated row count.

The key lines. No new SQL; this is a limitation of how and when statistics get collected, not something a single query controls.

Data skew

Data skew describes a situation where some values in a column occur far more often than others, making an estimate based on an average or typical distribution wrong for those specific, overrepresented values.

Example: the unprecedented, unplanned-for sale event, far outside the average day the monthly summary described, mirrors data skew misleading a selectivity estimate.

Remember this

A cost-based optimizer is only as good as its statistics. When an execution plan from Level 5 shows a surprisingly bad plan, stale statistics, column correlation, or data skew are the first three places to look.

Quick notes

  • The optimizer relies on periodically collected statistics, not live data, to estimate cost.
  • A selectivity estimate predicts how many rows a condition will match, shaping scan and join choices.
  • Stale statistics, collected before major data changes, can make estimates inaccurate.
  • Correlated columns can make a combined condition's true selectivity different from what per-column statistics suggest.
  • Data skew means some values are far more common than an average-case estimate assumes.