Explaining the Postgres Query Optimizer
summary
Explains how PostgreSQL chooses sequential, bitmap or index scans and when it picks nested loop, hash or merge joins, using EXPLAIN on temporary sample tables built from pg_class and pg_proc with a 78% skew on one value. Shows that missing optimizer stats makes the planner use bad index plans, that running ANALYZE fixes bad plans, that LIMIT changes join strategy, and that multi‑column or extended statistics (Tomas Vondra’s work) and raising stats_target are the practical fixes demonstrated.
AI-written summary · about
chapters (7)
- 0:00Introduction
- 2:10What is the query optimizer?
- 3:53Choices the query optimizer needs to make
- 4:19Scan methods: sequential, bitmap index, & index
- 12:13Join methods & join order
- 21:18Conclusion
- 23:22Q&A with Bruce & Marco
related talks
51mExplaining the Postgres Query Optimizer
40mExplaining the Postgres Query Optimizer
26mBeyond Joins and Indexes
52mUnderstanding PostgreSQL Query Plans (aka EXPLAIN)
50mImproving Postgres' Efficiency
23mBest Practices for Tuning Slow Postgres Queries
32mEXPLAIN: make it make sense
26mBe Good Friends with Query Planner