🐥 Tuning PostgreSQL query planning with SET STATISTICS

When PostgreSQL queries run slower than expected, one common culprit is inaccurate row count estimation due to insufficient statistics. PostgreSQL collects statistics to help the query planner choose efficient execution strategies. But sometimes, the default settings aren't enough—especially for columns with skewed or complex data.

Suppose you have a denominations table with a denomination_search column frequently used in WHERE clauses. You can increase the statistical resolution for this column like so:

ALTER TABLE denominations ALTER COLUMN denomination_search SET STATISTICS 1000;
ANALYZE denominations;

What this does:

• SET STATISTICS 1000: Increases the number of histogram and most-common-value slots PostgreSQL tracks for the column. The default is 100; higher values provide more detail.
• ANALYZE: Recomputes statistics immediately, applying the new target.

Higher-resolution stats can significantly improve the planner’s ability to estimate row counts and choose the best indexes or join strategies—especially for non-uniform or text-heavy data. While this comes at a slightly higher cost during analysis, the performance benefits for frequent queries can be substantial.