Explaining the Postgres Query Optimizer

on YouTube ↗51m

" summary

Explains what the Postgress optimizer actually decides: whether to use a sequential scan, bitmap scan or index scan, which join algorithm (nested‑loop, hash, merge) and the join order, and how those choices come from optimizer statistics produced by ANALYZE or autovacuum (which triggers after ~10% change; temp tables are not auto‑analyzed). Gives concrete thresholds (sequential often wins above ~8% of rows), shows cost estimates rising (example 13 → 39 when forcing indexes), and shows LIMIT changing plan selection.

AI-written summary · about

" chapters (17)

1. 0:00Start
2. 3:53The Postgres Optimizer
3. 5:16Overview
4. 6:25Parser
5. 6:56The Optimizer
6. 8:36What Decisions Does the Optimizer Have To Make
7. 11:48Sample Table
8. 15:10Optimizer Statistics
9. 21:35Sequential Scan
10. 22:48Bitmap Scan
11. 26:11Table Size Influence
12. 26:41How Do You Know the Depth of the Index
13. 28:05Bit Mapping
14. 38:35Nested Loop
15. 39:18Hash Join
16. 42:47If You Know One Thing Is GonNa Go One Way the Other Can You Force It To Go a Certain Way You You Have Sort of Global Settings Where You Can Turn Off Merge-Join or Turn Off Nested Loop if You Wanted To Do that for the Whole Query but There's no Way within the Structure To Actually Control It and There's a Good Reason for that because Organizations Find that the Developers Who Kind Of Take those Shortcuts Instead of Sort of Addressing the Overall Problem of Why the Query Might Not Be Doing the Right Thing Effectively as Their Data Volumes Change that that Programmers Going Right and Then It Requires Now Somebody Now Has To Go In and Sort Of Figure Out Why Was this Decision Made and Do I Need To Change You Know I'M Saying
17. 47:19And Effectively What the Limit Clause Does Is To Limit the Number of Rows That Are Returned to the Application Now You Might Think Okay Limit Clause That's Really Something That Doesn't Affect the Optimizer Just Affects How Many Rows Come Back but in Fact One of the Great Things about the Limit Clause Is It Allows You To Control To Tell the Optimizer I Don't Want the Whole Query Run if You Can Just Give Me the First Row I'M Happy and There Are some Optimizations That Can Be Made if You'Re Just Wanting a Small Subset of the Results So Here's a Query I'M Doing a Hash Joint Again There's no Where Clause but if I See the Same Query and I Order by the First Column and I Do It Limit One a Hash Join Has Changed to a Nested Loop

" related talks

• 40mExplaining the Postgres Query OptimizerBruce Momjian · HighLoad++ 2012
• 29mExplaining the Postgres Query OptimizerBruce Momjian · Citus Con: An Event for Postgres 2022
• 26mBeyond Joins and IndexesBruce Momjian · POSETTE: An Event for Postgres 2024
• 52mUnderstanding PostgreSQL Query Plans (aka EXPLAIN)Stephen Frost · PGConf NYC 2022
• 50mImproving Postgres' EfficiencyAndres Freund · PostgresOpen Silicon Valley 2017
• 23mBest Practices for Tuning Slow Postgres QueriesLukas Fittl · POSETTE: An Event for Postgres 2025
• 30mQuerySet.explain(): make it make sense.Aivars Kalvāns · PyCon Italia 2025
• 26mWhere do the performance cliffs come from?Tomáš Vondra · POSETTE: An Event for Postgres 2024