Improving PostgreSQL Join Estimates Statistics and Beyond
summary
Proposal implements cross-table join statistics in Postgres by extending CREATE STATISTICS ... FROM ... JOIN to collect multi-table MCV lists for equal inner joins, using an index-based sampling probe algorithm to avoid full sample joins. Experiments on the Join Order Benchmark show Q-error reduced and overall runs speed up about 1.5–1.7× with some slow queries dropping dramatically; analyze cost rises ~200 ms. Current limits: equality joins only, single join-condition implemented, targets Postgres 20; future work covers distinct counts, FDs, view-style stats, and runtime adaptation.
AI-written summary · about
chapters (18)
- 0:00Join estimate challenges
- 1:08Cardinality estimation matters
- 2:32Extended stats limitations
- 3:58Join Order Benchmark findings
- 4:40Row count underestimation impact
- 5:47Fix with join statistics
- 6:50Syntax for join statistics
- 8:15Testing with join stats
- 9:20Execution time improvements
- 10:04Overhead and analyze
- 10:26Design challenges
- 11:32Statistics collection
- 12:59Row containment
- 14:01Beyond join stats
- 14:42Learned optimization
- 15:45Runtime adaptation
- 17:10Improvement directions
- 18:14Q&A
related talks
50mImproving Postgres' Efficiency
52mA Deep Dive into Postgres Statistics
04mEnhancing Planner Estimates with Extended Statistics in PostgreSQL
29mExplaining the Postgres Query Optimizer
26mBeyond Joins and Indexes
52mBoosting Typical Query Patterns: PostgreSQL 18's Performance Enhancements
40mAdaptive query optimization in PostgreSQL
31mpg_stat_advisor: Optimizing PostgreSQL Planner with Insights