Where do the performance cliffs come from?
summary
Explains why Postgres produces abrupt slowdowns with concrete numbers: a hard-coded hash-table threshold at 9 values makes removing one IN element double runtime; cost-based planner chooses index/bitmap up to 1% selectivity but actual fastest switch is ~27%, so a tiny bump from 1.27% to 1.28% moves runtime from ~400 ms → ~10 s. Practical mitigations listed: raise work_mem, tune random_page_cost, use faster spill storage, or pursue adaptive execution / smooth scan research to avoid wrong-plan flips.
AI-written summary · about
chapters (6)
- 0:00Introduction
- 1:27What is a performance cliff?
- 9:03Runtime decisions
- 14:21Multiple Paths
- 19:12Mitigations
- 24:06Robustness / Research papers
related talks
50mImproving Postgres' Efficiency
23mBest Practices for Tuning Slow Postgres Queries
31mPostgres Performance for Humans
30mOvercoming Performance Hurdles in Postgres Partitioned Tables
29mExplaining the Postgres Query Optimizer
40mExplaining the Postgres Query Optimizer
51mExplaining the Postgres Query Optimizer
25mScaling PostgreSQL Beyond Query Optimization