What role do database statistics play in query optimization, and how do you ensure they are accurate?

Question

Accepted Answer

Database statistics are the foundation of the query optimizer's decision-making process. Without accurate statistics, even the most sophisticated optimizer will choose poor execution plans. As a mid-level DBA, ensuring statistics are current and accurate is one of the most impactful performance tuning activities.

The query optimizer uses statistics to estimate the number of rows that will be returned by each operation in a query plan. These estimates drive decisions about which indexes to use, which join algorithm to select, and in what order to join tables. When statistics are stale or inaccurate, the optimizer makes poor choices. For example, if statistics indicate a table has 1000 rows when it actually has 10 million, the optimizer might choose a Nested Loop join that works fine for 1000 rows but is catastrophically slow for 10 million.

In PostgreSQL, the pg_statistic system catalog stores detailed statistics for each column, including the number of distinct values, the most common values and their frequencies, a histogram of the value distribution, and the correlation between physical row order and logical column order. The ANALYZE command collects these statistics by sampling rows from the table. PostgreSQL samples a configurable number of rows, controlled by the default_statistics_target parameter, which defaults to 100. Higher values provide more accurate statistics but take longer to collect.

Autovacuum, which handles both vacuuming and auto-analysis, runs ANALYZE automatically when a sufficient percentage of rows in a table have been modified. The thresholds are controlled by autovacuum_analyze_threshold and autovacuum_analyze_scale_factor. The default triggers analysis after about 10 percent of rows have changed. For large tables that receive many writes, these defaults may leave statistics stale between autovacuum runs. Tune the thresholds on a per-table basis for critical tables.

For columns with skewed or unusual distributions, increase the statistics target for that specific column using ALTER TABLE table_name ALTER COLUMN column_name SET STATISTICS 500. This tells ANALYZE to sample more rows for that column, producing more detailed histogram and most-common-values data. This is particularly important for columns where the distribution is highly non-uniform, such as a status column where 95 percent of rows are in one status.

Extended statistics handle correlations between columns that single-column statistics miss. For example, if city and state are correlated but the optimizer treats them as independent, it underestimates the selectivity of filtering by both columns simultaneously. CREATE STATISTICS creates extended statistics on groups of columns, and the optimizer uses these for better multi-column estimates.

In MySQL, the optimizer uses similar statistics stored in the data dictionary and information_schema.statistics. ANALYZE TABLE updates statistics. The innodb_stats_persistent and innodb_stats_auto_recalc parameters control automatic statistics updates.

As a mid-level DBA, build monitoring that detects when estimated row counts in execution plans differ significantly from actual counts. This is the most reliable indicator that statistics need attention. When you encounter a query plan where estimates are orders of magnitude off from reality, running ANALYZE on the affected tables is often the fastest fix.