Roq: Robust Query Optimization Based on a Risk-aware Learned Cost Model

2024-01-26

AI Summary

Traditional query optimizers frequently select suboptimal execution plans due to inaccurate cardinality estimation and unrealistic assumptions—such as attribute independence—leading to poor robustness. This work formally defines *query optimization robustness* for the first time and introduces a risk-quantification framework grounded in approximate probabilistic machine learning. We design a learned cost model that jointly predicts both execution cost and its associated uncertainty (i.e., risk), enabling risk-aware plan selection. Evaluated across multiple benchmarks, our approach reduces the misselection rate of high-cost plans by 47% and decreases average execution time variance by 39% compared to state-of-the-art methods. These improvements significantly enhance runtime stability and performance predictability in production database systems.

Technology Category

• Reasoning under Uncertainty: Stochastic Optimization
• Search and Optimization: Learning to Search
• Intelligent Robots: Learning & Optimization for ROB

Application Category

• Search and Retrieval-Augmented AI: Web learning to rank, online learning, and counterfactual learning for ranking
• User Modeling, Personalization and Recommendation: Attacks and countermeasures in recommendation systems
• Economics, Online Markets and Human Computation: Cost models of using LLMs in production systems

Abstract

Query optimizers in RDBMSs search for execution plans expected to be optimal for given queries. They use parameter estimates, often inaccurate, and make assumptions that may not hold in practice. Consequently, they may select plans that are suboptimal at runtime, if estimates and assumptions are not valid. Therefore, they do not sufficiently support robust query optimization. Using ML to improve data systems has shown promising results for query optimization. Inspired by this, we propose Robust Query Optimizer, (Roq), a holistic framework based on a risk-aware learning approach. Roq includes a novel formalization of the notion of robustness in the context of query optimization and a principled approach for its quantification and measurement based on approximate probabilistic ML. It also includes novel strategies and algorithms for query plan evaluation and selection. Roq includes a novel learned cost model that is designed to predict the cost of query execution and the associated risks and performs query optimization accordingly. We demonstrate that Roq provides significant improvements in robust query optimization compared with the state-of-the-art.

Problem

• Improves query optimization robustness using risk-aware learning.
• Addresses suboptimal query plans due to inaccurate parameter estimates.
• Introduces a learned cost model for better query execution predictions.

Innovation

• Risk-aware learning approach for query optimization
• Approximate probabilistic ML for robustness quantification
• Learned cost model predicting execution costs and risks