Understanding PostgreSQL Query Plans (aka EXPLAIN)
summary
Shows how to read EXPLAIN and EXPLAIN ANALYZE outputs, what EXPLAIN ANALYZE with buffers and io timing reveals about shared hits versus OS reads, and how planning vs execution time differ. Demonstrates tuning knobs: adjust work_mem × hash_mem_multiplier to avoid hash-join batching to disk, toggle enable_ options to force alternate plans, and use VACUUM/ANALYZE or extended statistics to fix wildly wrong row estimates. Introduces the memoize node (PG14/15)* for repeated parameterized nested-loop lookups and points to explain.depes.com for visual analysis.
AI-written summary · about
chapters (16)
- 0:00Introduction and welcome
- 0:50What is a query plan
- 3:20Costing in PostgreSQL plans
- 6:30EXPLAIN options and planning time
- 9:06Running queries with EXPLAIN ANALYZE
- 12:23Retrieving data nodes
- 18:22Join types in PostgreSQL
- 22:13Aggregate operations
- 23:38Parallel query nodes
- 27:08Bitmap and materialized nodes
- 31:26Memoize and other nodes
- 33:13Statistics and query optimization
- 37:25Analyzing join plans
- 40:33EXPLAIN ANALYZE with buffers
- 42:16Parallel plan details
- 44:24Q&A session
related talks
29mExplaining the Postgres Query Optimizer
26mBe Good Friends with Query Planner
23mBest Practices for Tuning Slow Postgres Queries
26mBeyond Joins and Indexes
47mDebugging active queries...
32mEXPLAIN: make it make sense
40mExplaining the Postgres Query Optimizer
47mIdentifying Slow Queries and Fixing Them!