A Cause-Focused Query Optimizer Alert System
Runfan Ye, Zibo Liang, Xu Chen, Shuncheng Liu, Kai Zheng · 2024
A series of studies apply machine learning to assist cost-based query optimizers in DBMS, emphasizing incorporating uncertainty predictions to guide decision-making. While these approaches have demonstrated advancement in some benchmarks, their drawbacks, such as unstable performance, stem from the inherent challenges of using machine learning models to predict the cost of execution plans and the lack of exploration of the intrinsic characteristics of suboptimal plans. In this paper, we introduce an alert system for query optimization, which is built upon cost models to reduce the selection of regressed plans. The key insight is that there are differences in the predictive uncertainty that lead to query optimization and the regression of execution plans. We investigate the causes of these differences in uncertainty and design a discriminator to filter out execution plans with higher risks of regression. The alert system can be integrated with various cost models, enhancing the robustness of query optimizers. In our experiments, the system further reduces execution time by 20% compared to learned optimizers. Meanwhile, the proportion of optimized queries reduced by the alert system is just 15% of the proportion of regressed queries diminished.