Tracking plan shapes over time with Plan IDs and the new pg_stat_plans
summary
pg_stat_plans extension builds low‑overhead plan tracking by assigning a 64‑bit plan ID via a planner tree walk and copied jumbling logic rather than hashing explain output, and it uses the Postgres 18 ability to set a plan ID on PlanStmt. Benchmarks show much lower overhead than pg_store_plans (avoids the ~20% hit), supports Postgres 16–18, stores plan text in 10 MB dynamic shared memory with compression (example 4,700 bytes → 855 bytes) and a dictionary that fits ~17,000 big plans in 10 MB.
AI-written summary · about
chapters (8)
- 0:00Introduction
- 1:27What are plan shapes
- 8:13Calculating plan IDs
- 13:27Postgres 18 improvements
- 15:34Introducing pg_stat_plans
- 20:30Plan text storage and compression
- 23:20Future work and open questions
- 25:54Q&A
related talks
24mTracking plan shapes over time with Plan IDs, and a new pg_stat_plans
26mBe Good Friends with Query Planner
31mpg_stat_advisor: Optimizing PostgreSQL Planner with Insights
52mUnderstanding PostgreSQL Query Plans (aka EXPLAIN)
30mReproduce those complex query planner issues
26mBeyond Joins and Indexes
28mWhat’s new in the Postgres 18 query planner / optimizer
57mQuery Logging and Workload Analysis