A Deep Dive into Postgres Statistics
summary
Explains exactly how Postgres computes selectivity from pg_stats using most common values, histograms and null fraction, and how equal versus scalar operators are combined into per-clause estimates; shows the default statistics_target=100 and reservoir sampling that builds the sample, and the brute‑force MCV algorithm. Demonstrates concrete mismatches (estimate 297 rows vs actual 5,657 for city+country) and fixes: create statistics multicolumn dependencies and MCVs that cut a GROUP BY from ~800–900ms to ~300ms. Notes histogram bucket math and reservoir sampling limits.
AI-written summary · about
chapters (15)
- 0:00Introduction and speaker intro
- 2:18Viewing Postgres statistics
- 5:30Default statistics in Postgres
- 14:22Query optimizer and statistics
- 16:34Selectivity for equality clauses
- 21:52Selectivity for scalar operators
- 26:51Combining selectivities in queries
- 32:53When statistics fail and solutions
- 34:43Extended statistics configuration
- 37:12Functional dependency statistics
- 38:18NDistinct count statistics
- 40:28Multivariate most common values
- 43:22Configuring statistics targets
- 45:28How Postgres gathers statistics
- 50:30Summary and conclusion
related talks
26mpg_stats: How Postgres Internal Stats Work
50mImproving Postgres' Efficiency
22mImproving PostgreSQL Join Estimates Statistics and Beyond
39mDeep dive into PostgreSQL internal statistics
04mEnhancing Planner Estimates with Extended Statistics in PostgreSQL
29mExplaining the Postgres Query Optimizer
47mUnderstanding Statistics in SQL Server
51mPostgreSQL Query Performance Insights