A PostgreSQL extension that uses machine learning to improve query cardinality estimation and optimize execution plans.
AQO (Adaptive Query Optimization) is a PostgreSQL extension that enhances the standard cost-based query optimizer by using machine learning models to improve cardinality estimation based on actual query execution statistics. It solves the problem of poor query performance caused by inaccurate cardinality estimates in complex queries, which can lead to suboptimal execution plans. The extension continuously learns from workload patterns to make PostgreSQL query planning more adaptive and intelligent.
Database administrators and developers working with PostgreSQL who are experiencing slow query performance due to inaccurate cardinality estimates, particularly for complex analytical or join-heavy queries. It's especially relevant for those managing production databases where query optimization is critical.
Developers choose AQO over standard PostgreSQL optimization because it provides adaptive learning that continuously improves cardinality estimates based on actual execution data, leading to significant performance gains for problematic queries. Its unique selling point is the ability to automatically tune optimization settings per query type while offering multiple operation modes that balance automation with manual control.
Adaptive query optimization for PostgreSQL
Open-Awesome is built by the community, for the community. Submit a project, suggest an awesome list, or help improve the catalog on GitHub.
Uses machine learning models to continuously refine cardinality estimates based on actual query execution data, demonstrated in the README where a complex query's execution time dropped from 8168ms to 738ms after learning.
Offers four operation modes ('controlled', 'learn', 'intelligent', 'forced') to balance automation with manual tuning, with 'controlled' recommended for production to avoid overhead from unnecessary learning.
Stores optimization settings per normalized query hash in the aqo_queries table, allowing targeted adjustments for specific problematic queries, as shown in the usage pattern with SET aqo.mode commands.
Includes an auto_tuning setting that lets AQO automatically decide when to use or learn from ML models, reducing manual configuration for known query types in 'intelligent' mode.
Requires patching PostgreSQL source code and rebuilding the entire database, which is error-prone and not feasible in managed cloud environments or for teams without deep PostgreSQL expertise.
Cannot handle queries involving temporary tables or dynamically generated objects due to OID issues, severely limiting its use in workloads with transient data structures, as admitted in the limitations section.
The 'intelligent' mode, designed for full automation, is explicitly not recommended for production use, forcing manual management and reducing confidence in hands-off deployment.