Enhancing Planner Estimates with Extended Statistics in PostgreSQL
summary
PostgreSQL shows large estimation errors on correlated columns; creating CREATE STATISTICS on the specific column pair and running ANALYZE moves an estimate from ~20 rows to ~19k (actual ~100k), and a simple predicate already reads ~99k vs 100k. An aggregate was overestimated by about 100x until an ndistinct stats was added and reanalyzed. Extended statistics improve planner selectivity for correlated and high‑cardinality columns; they do not apply to join queries, so rewrite joins if estimates remain wrong.
AI-written summary · about
related talks
31mpg_stat_advisor: Optimizing PostgreSQL Planner with Insights
26mpg_stats: How Postgres Internal Stats Work
22mImproving PostgreSQL Join Estimates Statistics and Beyond
52mA Deep Dive into Postgres Statistics
50mImproving Postgres' Efficiency
56mQuery Planning Gone Wrong
40mAdaptive query optimization in PostgreSQL
36mAdvice is seldom welcome but efficacious