Explaining the Postgres Query Optimizer
summary
Shows how PostgreSQL chooses sequential scan, bitmap heap scan, or index scan using table statistics gathered by ANALYZE/auto‑vacuum and demonstrated with temporary tables (199–260 rows, 78% of rows sharing one value). Uses EXPLAIN/ANALYZE and simple functions to prove that when a value is common the planner picks sequential or bitmap scans, when rare it picks index scans, and that turning off the optimizer makes estimated cost go from 8 to 39, drastically slowing queries.
AI-written summary · about
chapters (10)
- 0:00Introduction
- 2:27Query optimizer role
- 5:33Data scanning methods
- 16:14Statistics and optimization
- 19:19Cost of operations
- 22:14Join methods
- 28:43Constraints and performance
- 31:49Heuristics and accuracy
- 33:00Optimizer evolution
- 34:50Q&A
related talks
51mExplaining the Postgres Query Optimizer
29mExplaining the Postgres Query Optimizer
26mBeyond Joins and Indexes
52mUnderstanding PostgreSQL Query Plans (aka EXPLAIN)
30mQuerySet.explain(): make it make sense.
32mEXPLAIN: make it make sense
23mBest Practices for Tuning Slow Postgres Queries
52mPostgreSQL Index Strategy Guide