Query optimization has long been a fundamental yet challenging topic in the database field. With the prosperity of machine learning (ML), some recent works have shown the advantages of reinforcement learning (RL) based learned query optimizer. However, they suffer from fundamental limitations due to the data-driven nature of ML. Motivated by the ML characteristics and database maturity, we propose LEON–a framework for ML-aidEd query OptimizatioN. LEON improves the expert query optimizer to self-adjust to the particular deployment by leveraging ML and the fundamental knowledge in the expert query optimizer. Different from the previous regression objective, we propose a pair-wise ranking objective and train a ranking model for plans. To help the optimizer escape the local minima and avoid failure, a ranking and uncertainty-based exploration strategy is proposed, which discovers the valuable plans to aid the optimizer. To enhance the robustness and practicality of our framework, we introduce an advanced version of the LEON framework, referred to as LEON+. By dynamically adjusting the optimization space, we significantly enhance the framework’s robustness against unseen workloads and drastically reduce the costs associated with exploration. We have seamlessly integrated the LEON+ framework into traditional optimizers, enabling unobtrusive and automated tuning. Extensive experiments offer evidence that the proposed framework can outperform the state-of-the-art methods in terms of end-to-end latency performance, training efficiency, and stability.
