Query Planning Gone Wrong
summary
Surveying 168 reports from pgsql-performance yields concrete failure modes: 168 reports are classified into settings (23), inherently slow requests (23), architectural limitations, and bugs (14), but the biggest root cause is planner estimation errors dominate split into 28 conceptual mismatches and 55 row‑count/cost misestimates. Frequent, reproducible problems include correlated columns and order by with limit misestimation, generic/prepared plan pitfalls, work_mem/statistics_target tuning, and toasting/cost misaccounting; many cases are fixed by specific rewrites or PG versions (9.1/9.2) while others need deep engine changes.
AI-written summary · about
chapters (12)
- 0:00Introduction
- 1:05Why this talk
- 3:07What I did
- 5:17The data
- 8:29Settings
- 12:33Slow
- 17:01Planning Errors
- 18:38Categories
- 21:07Grand Prize Winners
- 34:30Cross Table Correlation
- 38:00Cost Estimation Errors
- 42:03Conceptual Errors
related talks
23mBest Practices for Tuning Slow Postgres Queries
41mRow-Estimation Revelation and the Monolithic Query Syndrome
26mBe Good Friends with Query Planner
26mWhere do the performance cliffs come from?
30mReproduce those complex query planner issues
47mIdentifying Slow Queries and Fixing Them!
01h 00mThe 10 Deadly PostgreSQL Sins
04mEnhancing Planner Estimates with Extended Statistics in PostgreSQL