Statistics and Query Planning
You'll learn how database engines rely on statistical data to choose optimal query execution plans—and why keeping that data fresh matters.
What Are Database Statistics?
When you run a query, the database optimizer doesn't actually execute every possible plan to see which is fastest. Instead, it makes educated guesses based on statistics about your data: how many rows are in each table, how values are distributed across columns, the selectivity of indexes, and more.
Think of statistics as the optimizer's "weather forecast" for your data. Just as a forecast helps you decide whether to bring an umbrella, statistics help the optimizer decide whether to use an index scan, a full table scan, or which table to join first.
How Statistics Influence Plans
The optimizer uses statistics to estimate:
- Cardinality: How many rows will match a filter condition
- Cost: How expensive different operations will be (index lookup vs scan)
- Join order: Which table should be processed first in multi-table queries
If statistics say a table has 10 rows, the optimizer might choose a full scan. If they say it has 10 million rows, an index becomes critical. Wrong statistics lead to terrible plans.
When to Update Statistics
Statistics become stale after significant data changes. After bulk inserts, deletions, or updates, your "weather forecast" is outdated. Most databases update statistics automatically, but you may need to trigger manual updates (ANALYZE in PostgreSQL, UPDATE STATISTICS in SQL Server) after major operations or when query plans suddenly degrade.
Key Takeaway: Database statistics are the optimizer's map of your data landscape—accurate statistics enable smart query plans, while stale statistics cause the optimizer to make poor choices that hurt performance.