Overriding the Planner's Distinct-Value Count When It Misjudges a Column
ANALYZE only samples a table, and the number of distinct values is the hardest thing to estimate from a sample, so on a very large table PostgreSQL can badly under-count — which makes it think each value matches far more rows than it does, or the reverse. You can inspect the estimate in pg_stats.n_distinct and override it with ALTER TABLE ... SET (n_distinct = ...). It is a blunt tool you reach for after ANALYZE, a higher statistics target and extended statistics have not fixed the estimate.
Questions this Concept answers
Why is the distinct-value count the hardest thing for `ANALYZE` to derive from a sample?
J
jeremy
Text
PostgreSQL Tutorial: Tune estimated number of distinct values
PostgreSQL keeps an estimate of how many different values live in a column, and it uses that number to guess how many rows a filter or a grouping will produce. Because the estimate comes from a small…