Correlated Columns Break the Planner's Assumption That Conditions Are Independent
When your WHERE clause has two conditions, the planner usually multiplies their selectivities as if the columns had nothing to do with each other. That is fine for unrelated columns and disastrous for related ones: city = 'Portland' AND state = 'Oregon' is not one-fiftieth as selective as city alone, because the city already implies the state. The result is an estimate hundreds of times too low and a plan built for a handful of rows. Recognising correlated columns in your own schema explains a whole class of mysterious underestimates.
Questions this Concept answers
- Why does `WHERE city = 'Portland' AND state = 'Oregon'` get an estimate far below reality?
Extended Statistics for Correlated Columns in PostgreSQL
PostgreSQL's cost-based planner chooses scan types, join algorithms and join order primarily from estimated row counts, and by default it assumes predicates on different columns are statistically ind…