Advice is seldom welcome but efficacious
summary
Overview of PostgreSQL advisory features with a focus on the execution plan advisor pg_plan_advsr which integrates with pg_hint_plan and pg_cor_stats to auto-diagnose plans, generate hints (get_hint) and propose extended statistics (get_ext_stats). Demoed results include a 3500× speedup from an index suggestion and an 8.2× improvement from automatic plan tuning. Implementation uses planner/executor hooks, works with hypoPG and other extensions, and is currently meant for verification environments due to overhead.
AI-written summary · about
chapters (18)
- 0:00Introduction and speaker intro
- 1:53Overview of advisory features
- 3:19Types of advisory features
- 6:15Parameter advisors like PG tune
- 7:41Index advisors overview
- 9:30Index suggestion effectiveness demo
- 10:51Partitioning advisors
- 12:23Execution plan advisors
- 15:15PG plan advisor features
- 17:52Automatic execution plan tuning
- 18:39Demo of automatic tuning
- 20:25Automatic hint generation
- 21:31Extended stats suggestion
- 22:58Demo of extended stats feature
- 25:47Implementation and roadmap
- 27:46Feedback for PostgreSQL development
- 30:38Talk conclusion and summary
- 31:40Q&A session
related talks
31mpg_stat_advisor: Optimizing PostgreSQL Planner with Insights
26mBe Good Friends with Query Planner
52mUnderstanding PostgreSQL Query Plans (aka EXPLAIN)
27mBoosting Azure Database for PostgreSQL Performance with Azure Advisor
23mBest Practices for Tuning Slow Postgres Queries
50mPostgreSQL Query Performance Insights
04mEnhancing Planner Estimates with Extended Statistics in PostgreSQL
41mOptimizing your app by understanding your PostgreSQL database