Statistics and Histograms
You'll learn how databases collect and use statistical summaries of data to make smarter decisions when optimizing queries.
The Problem with Blind Estimates
In the previous lesson on cardinality estimation, you learned that the query optimizer needs to predict how many rows different operations will produce. But how does it make those predictions accurately? It uses statistics and histograms — pre-computed summaries of your actual data distribution.
What Are Database Statistics?
Database statistics are metadata about your tables and columns:
- Row counts for tables
- Distinct value counts (how many unique values exist in a column)
- NULL counts (how many rows have NULL in a column)
- Min/max values for a column
- Average row/column size
Think of statistics as a "cheat sheet" the database keeps about your data, so it doesn't have to scan millions of rows just to make a plan.
What Are Histograms?
A histogram divides a column's value range into buckets and tracks how many rows fall into each bucket. This reveals data skew — when values aren't evenly distributed.
For example, if you have a country column, a histogram might show:
- Bucket 1 (USA): 500,000 rows
- Bucket 2 (Canada): 50,000 rows
- Bucket 3 (Other): 10,000 rows
Without histograms, the optimizer might assume equal distribution and badly misjudge selectivity. With them, it knows a WHERE country = 'USA' filter will return 10× more rows than WHERE country = 'Canada'.
How the Optimizer Uses Them
During cost estimation, the optimizer combines statistics and histograms with your query predicates to predict cardinality much more accurately, leading to better join orders and index choices.
Key Takeaway: Statistics and histograms let the database accurately estimate row counts by capturing the actual distribution of your data, not just guessing based on table size alone.