Improving Postgres' Efficiency
summary
Describes concrete Postgres changes and measured effects: adds multi-column statistics (Postgres 10) to fix huge selectivity errors, uses index-only scans and prefetching (8.4) to cut I/O, rewrites expression engine and hash-aggregate structures to gain ~35% on TPC-H Q1, and adds JIT compilation that together with other fixes yields roughly 3x faster for that query; prototypes a cache-miss loop join to avoid millions of index probes, and flags CTE materialization as a common planner trap.
AI-written summary · about
chapters (13)
- 0:00Introduction
- 0:55Efficiency
- 2:35Analytics
- 12:30Correlation
- 18:18Index only scans
- 22:26Prefetching
- 26:37FastX
- 28:07Query Plan
- 31:25Performance
- 34:00Native Code
- 37:21Performance Results
- 40:50Nested Loop Join
- 45:56Per Day Query
related talks
31mPostgres Performance for Humans
32mHacking Postgres Executor For Performance
23mBest Practices for Tuning Slow Postgres Queries
26mBeyond Joins and Indexes
48mNew query execution features in Postgres 10, 11 & PostgresPro
34mPostgres 10, Performance, and You
42mPostgreSQL Tips & Tricks For App Devs
41mOptimizing your app by understanding your PostgreSQL database