What’s new in the Postgres 18 query planner / optimizer
summary
PostgreSQL 18’s planner adds concrete changes: OR->IN conversion turns long OR lists into an index-friendly equals-any index scan; self-join elimination removes redundant same-table joins; EXCEPT/INTERSECT use pre-sorted input to cut a 354 ms case about 2× (→ ~177 ms); GROUP BY drops columns covered by unique indexes; lock slots increase and runtime partition pruning before locking drops lock overhead for thousands of partitions (planning now ~2× faster at 1,024 parts and ~9× at 8,000 in microsecond-scale tests using EXPLAIN/ANALYZE and commit-based benchmarks).
AI-written summary · about
chapters (10)
- 0:00Introduction
- 1:07PostgreSQL 18 feature freeze
- 2:26Part 1: PostgreSQL 18 query planner’s new abilities
- 6:34Self join elimination
- 9:52Improved EXCEPT / INTERSECT support
- 13:31GROUP BY clause optimization
- 16:52Part 2: General query planner improvements
- 20:24Run-time pruning: v17 vs v18
- 23:29Planner performance improvements
- 26:07Other notable changes & testing
related talks
26mPostgreSQL 17 vs 18: Side‑by‑Side Performance Wins in Real‑World Queries
26mBeyond Joins and Indexes
29mExplaining the Postgres Query Optimizer
06mSomething Got Better For Partitioning in v18 Unless Reverted
52mBoosting Typical Query Patterns: PostgreSQL 18's Performance Enhancements
50mImproving Postgres' Efficiency
26mBe Good Friends with Query Planner
51mExplaining the Postgres Query Optimizer