pg_stats: How Postgres Internal Stats Work
summary
Shows exactly what pg_stats and pg_statistic contain and how ANALYZE builds them, with concrete numbers: a 1,000,000‑row customers table yields ~17,000 rows (≈17%) for CA and ~4,000 for WY; planner cost math picks a sequential scan at cost ~19,000 versus an index plan at ~32,000. Covers histograms (default 100 buckets, raising to 1,000 gives day‑level precision) and uses pg_stats view plus CREATE STATISTICS (dependencies/ndistinct) to fix correlated columns and tighten estimates.
AI-written summary · about
chapters (12)
- 0:00Music & introduction
- 0:30Why Postgres ignores your index
- 2:00How the query planner thinks
- 4:34Exploring pg_statistic and pg_stats
- 6:10Understanding null fractions and cardinality
- 8:15Most common values
- 9:20Cost estimation for sequential and index scans
- 12:25Why the planner chooses different plans
- 16:30Understanding range selectivity with histograms
- 18:35Improving planner accuracy with statistics
- 19:55Improving planner accuracy with CREATE STATISTICS
- 22:55Query planning checklist & final performance tuning tips
related talks
52mA Deep Dive into Postgres Statistics
31mpg_stat_advisor: Optimizing PostgreSQL Planner with Insights
04mEnhancing Planner Estimates with Extended Statistics in PostgreSQL
50mPostgreSQL Query Performance Insights
39mDeep dive into PostgreSQL internal statistics
26mBe Good Friends with Query Planner
40mExplaining the Postgres Query Optimizer
25mPoint-in-time query tuning and observability with pg_stat_statements