Database Statistics: The Query Optimizer's Internal Map

Why it exists

A database's query optimizer must choose the most efficient execution plan from many possible options for a single SQL query. Without knowing the underlying data's characteristics, it would be guessing blindly. Database statistics provide the necessary context for the optimizer to make an informed, cost-based decision.

The mental model

Think of database statistics as an intelligence report for the query optimizer. Before planning an operation, the optimizer consults this report on the "terrain" of your data. Is this table a tiny village or a massive city? Is this column's data uniform, or is it heavily skewed with a few common values? The answers determine the strategy and prevent costly mistakes.

How it works

The database periodically collects and stores metadata about its objects in the data dictionary. Key statistics include: Table-level stats like the number of rows, data blocks, and average row length; Column-level stats like the number of distinct values (NDV), null counts, and histograms showing data distribution; and Index-level stats like leaf blocks and clustering factor. The optimizer plugs these numbers into its cost model to estimate the I/O and CPU cost for each potential step in a query plan. It then assembles the plan with the lowest total estimated cost.

When to use it

All modern relational databases use cost-based optimizers, so statistics are always in play. You actively manage them by ensuring they are collected regularly, especially after significant data changes like large bulk loads, deletes, or schema updates. This process is often triggered by commands like ANALYZE or GATHER_STATS.

When not to use it

The question isn't whether to use them, but how to manage them. For extremely volatile tables that change constantly, freshly gathered stats can become stale almost immediately. In these rare cases, more advanced techniques like dynamic sampling might be needed. For very small, static tables, the optimizer's default assumptions are often good enough, and the overhead of gathering stats is unnecessary.

One canonical example

A query filters a 10-million-row orders table for status = 'SHIPPED'. With accurate statistics, the optimizer knows that 'SHIPPED' accounts for 95% of the rows. It will correctly choose a full table scan, as using an index would be less efficient. If the query filtered for status = 'RETURNED', and stats showed this was only 0.1% of rows, the optimizer would use an index on the status column to quickly find that small subset. Stale or missing stats could cause it to make the wrong choice in both cases, leading to a slow query.