Beyond Joins and Indexes
summary
Explains that the Postgres optimizer actually chooses 43 different plan-node strategies beyond simple joins and indexes, shows the exact SQL and EXPLAIN output used to produce each plan, and demonstrates which ones measurably speed queries. Examples include incremental_sort using an index for the leading ORDER BY column, memoize cache that stores hot right‑side lookups (Postgres 14) and parallel hash join / parallel aggregate that split scans and combine partial results; slides and runnable SQL are provided.
AI-written summary · about
chapters (14)
- 0:00Intro
- 1:23What will be covered (numbered list)
- 3:03#1-3 Result ... Function Scan
- 4:01#4-7 Incremental Sort ... Merge Append
- 7:02#8-10 Subquery Scan ... SetOp
- 8:39#11-12 Materialize .. Memoize
- 12:26#13-18 Group ... WindowAgg
- 15:13#19-26 Parallel Seq Scan ... Parallel Hash Join
- 19:58#27-29 CTE Scan ... Recursive Union
- 21:54#30-35 ProjectSet ... Tid Scan
- 23:13#36-39 Insert ... Merge
- 23:53#40-42 Semi Join ... SubPlan
- 25:25#43 Others
- 25:49Conclusion
related talks
29mExplaining the Postgres Query Optimizer
51mExplaining the Postgres Query Optimizer
40mExplaining the Postgres Query Optimizer
52mUnderstanding PostgreSQL Query Plans (aka EXPLAIN)
50mImproving Postgres' Efficiency
28mWhat’s new in the Postgres 18 query planner / optimizer
29mPostgres Index types and where to find them
52mPostgreSQL Index Strategy Guide