Abstract:In recent studies, machine learning techniques have been employed to support or enhance cost-based query optimizers in DBMS. Although these approaches have shown superiority in certain benchmarks, they also suffer from certain drawbacks. These include unstable performance, high training costs, and slow model updating, which can be attributed to the inherent challenges of predicting the cost or latency of execution plans using machine learning models. In this paper, we introduce a le arning-to- r ank query o ptimizer, called Lero , which builds on top of the native query optimizer and continuously learns to improve query optimization. The key observation is that the relative order or rank of plans, rather than the exact cost or latency, is sufficient for query optimization. Lero employs a pairwise approach to train a classifier to compare any two plans and tell which one is better. Such a binary classification task is much easier than the regression task to predict the cost or latency, in terms of model efficiency and effectiveness. Rather than building a learned optimizer from scratch, Lero is designed to leverage decades of wisdom of databases and improve the native optimizer. With its non-intrusive design, Lero can be implemented on top of any existing DBMS with minimum integration efforts. We implement Lero and demonstrate its outstanding performance using PostgreSQL and Spark SQL. In our experiments, Lero achieves near-optimal performance on several benchmarks. It reduces the execution time of the native PostgreSQL optimizer by up to and other learned query optimizers by up to on single-machine environments. On distributed environments, our Lero improves the running time of the native Spark SQL optimizer by up to . Meanwhile, Lero continuously learns and automatically adapts to query workloads and changes in data.

RLO: a Reinforcement Learning-Based Method for Join Optimization

Learning to Optimize Join Queries With Deep Reinforcement Learning

Optimization Factor Analysis Of Large-Scale Join Queries On Different Platforms

Reinforcement Learning with Tree-LSTM for Join Order Selection

Join Query Optimization with Deep Reinforcement Learning Algorithms

LOGER: A Learned Optimizer Towards Generating Efficient and Robust Query Execution Plans

A novel reinforcement learning-based method for structure optimization

Deep Reinforcement Learning for Join Order Enumeration

A New Graph Neural Network-based Join Optimization Algorithm

Lero: applying learning-to-rank in query optimizer

JoinGym: An Efficient Query Optimization Environment for Reinforcement Learning

Query Join Order Optimization Method Based on Dynamic Double Deep Q-Network

Robust Listwise Learning-to-Rank Approach for Database Query Optimizer

A Novel Framework Using Deep Reinforcement Learning for Join Order Selection

Lero: A Learning-to-Rank Query Optimizer.

COOOL: A Learning-To-Rank Approach for SQL Hint Recommendations

JAPO: Learning Join and Pushdown Order for Cloud-Native Join Optimization

SQLoop: High Performance Iterative Processing in Data Management

Balsa: Learning a Query Optimizer Without Expert Demonstrations

Join Processing for Graph Patterns: An Old Dog with New Tricks

Learning for Robust Combinatorial Optimization: Algorithm and Application