Row-Estimation Revelation and the Monolithic Query Syndrome
summary
Claim: planner row counts are often wildly wrong causing wrong join choices and catastrophic slowdowns — row estimates off by orders of magnitude. Evidence: EXPLAIN ANALYZE and pgBadger show nested loops chosen where hash/merge are appropriate and 47‑minute queries drop to seconds after remediation. Tools and fix: generate the problematic subset into a temp table + ANALYZE so the planner gets exact row counts, or refactor monolithic SQL into staged steps; mention of Tom Lane’s advice about schema/statistics limits.
AI-written summary · about
chapters (6)
- 0:00Start
- 9:40Generic Types of Joins of Postgres
- 9:53Nested Loop
- 11:58Breakthrough
- 23:01Temp Tables
- 28:40Downsides to Temp Tables
related talks
56mQuery Planning Gone Wrong
32mEXPLAIN: make it make sense
29mExplaining the Postgres Query Optimizer
52mUnderstanding PostgreSQL Query Plans (aka EXPLAIN)
26mBe Good Friends with Query Planner
04mEnhancing Planner Estimates with Extended Statistics in PostgreSQL
53mWhen to stop tuning a query
23mBest Practices for Tuning Slow Postgres Queries