Adaptive query optimization in PostgreSQL
summary
An extension for PostgreSQL that stores actual cardinalities from executions and uses a K-nearest-neighbors model to predict node cardinalities, sharing those with the planner via hooks. Tested on JoinBench and IMDb-like schemas with learning (parallelism off for learning), cardinality error falls toward zero; about 10 queries speed up ~4×, ~11 double, and two slow down. It skips unreliable partial nodes and temporary-table nodes, exposes per-query stats for autotuning, and reduces misplans caused by correlated or nonuniform data.
AI-written summary · about
chapters (9)
- 0:00Introduction to AQO
- 1:54Optimizer problems with cardinality
- 4:43Model description of AQO
- 8:25Learning workflow in AQO
- 15:01Problems and features in AQO
- 18:24Example database and terminology
- 26:23Testing results and benchmarks
- 29:17Conclusion and future improvements
- 30:23Q&A session
related talks
31mpg_stat_advisor: Optimizing PostgreSQL Planner with Insights
26mBe Good Friends with Query Planner
04mEnhancing Planner Estimates with Extended Statistics in PostgreSQL
01h 02mStardog Query Optimiser: Architecture and Cardinality Estimations for Graph Queries
52mBoosting Typical Query Patterns: PostgreSQL 18's Performance Enhancements
50mImproving Postgres' Efficiency
22mImproving PostgreSQL Join Estimates Statistics and Beyond
41mOptimizing your app by understanding your PostgreSQL database