CREATE STATISTICS Teaches the Planner That Two Columns Move Together
By default the planner assumes columns are independent, so it estimates WHERE city = 'Paris' AND country = 'France' by multiplying two fractions and gets a number far smaller than reality. Extended statistics, created with CREATE STATISTICS on the column pair, record the dependency so the estimate matches. It is the standard fix when a plan chooses a nested loop for what is actually a large result because it believed the filter was far more selective than it is.
Questions this Concept answers
Why does the default assumption of column independence produce a far-too-small estimate for `WHERE city = 'Paris' AND country = 'France'`?
J
jeremy
Video
How pg_stats Column Statistics Drive Planner Cost Estimates in PostgreSQL
PostgreSQL's cost-based query planner does not consult table data when choosing a plan; it consults column-level statistics stored in `pg_statistic` (exposed human-readably through the `pg_stats` vie…