Abstract:Exhaustive enumeration of all possible join orders is often avoided, and most optimizers leverage heuristics to prune the search space. The design and implementation of heuristics are well-understood when the cost model is roughly linear, and we find that these heuristics can be significantly suboptimal when there are non-linearities in cost. Ideally, instead of a fixed heuristic, we would want a strategy to guide the search space in a more data-driven way---tailoring the search to a specific dataset and query workload. Recognizing the link between classical Dynamic Programming enumeration methods and recent results in Reinforcement Learning (RL), we propose a new method for learning optimized join search strategies. We present our RL-based DQ optimizer, which currently optimizes select-project-join blocks. We implement three versions of DQ to illustrate the ease of integration into existing DBMSes: (1) A version built on top of Apache Calcite, (2) a version integrated into PostgreSQL, and (3) a version integrated into SparkSQL. Our extensive evaluation shows that DQ achieves plans with optimization costs and query execution times competitive with the native query optimizer in each system, but can execute significantly faster after learning (often by orders of magnitude).

What problem does this paper attempt to address?

The problem that this paper attempts to solve is that in database query optimization, especially for the optimization of join queries, traditional heuristic - based methods perform poorly when dealing with non - linear cost models. Specifically: 1. **Limitations of Traditional Methods**: Most existing query optimizers use heuristic methods to reduce the search space. For example, the classic System R - style dynamic programming algorithm usually restricts its search space to certain specific shapes (such as "left - deep" plans). These heuristic methods perform well when the cost model is approximately linear, but in the presence of non - linear costs, these heuristic methods may be significantly sub - optimal, resulting in poor query plans. 2. **Challenges of Non - linear Cost Models**: In actual systems, due to factors such as memory limitations or materialization, intermediate results exceeding the available memory may trigger partitioning, or relations crossing size thresholds may lead to changes in physical join implementations, all of which may cause non - linearity in the cost model. This non - linearity makes it difficult for traditional heuristic methods to adapt, especially when dealing with large - scale or complex queries. 3. **Requirements for Data - Driven Optimization**: Ideally, a strategy that can adaptively guide the search space according to specific data sets and query workloads is needed, rather than fixed heuristic methods. This requires that the optimization method can learn from previous query plan results, thereby providing more efficient search strategies for future queries. To solve the above problems, the paper proposes a new method based on deep reinforcement learning (DRL) to learn the search strategy for optimizing join queries. This method links the join order optimization problem with Markov decision processes (MDPs) in reinforcement learning and uses the Q - learning algorithm to approximate the optimal Q - function, thereby guiding the query optimizer to make better decisions when facing different cost models. This method can not only significantly improve the speed of query optimization but also adapt to various complex query scenarios while ensuring that the execution time is close to the optimal.

Learning to Optimize Join Queries With Deep Reinforcement Learning

Join Query Optimization with Deep Reinforcement Learning Algorithms

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

RLO: a Reinforcement Learning-Based Method for Join Optimization

A Novel Framework Using Deep Reinforcement Learning for Join Order Selection

Deep Reinforcement Learning for Join Order Enumeration

DeepO: A Learned Query Optimizer

Reinforcement Learning with Tree-LSTM for Join Order Selection

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

JoinGym: An Efficient Query Optimization Environment for Reinforcement Learning

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

Balsa: Learning a Query Optimizer Without Expert Demonstrations

Learning Approximation Sets for Exploratory Queries

LEON: A New Framework for ML-Aided Query Optimization.

Lero: applying learning-to-rank in query optimizer

Learned Query Optimizers: Evaluation and Improvement

Using Deep Q-Learning to Control Optimization Hyperparameters

Hype or Heuristic? Quantum Reinforcement Learning for Join Order Optimisation

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

Improving Variable Orderings of Approximate Decision Diagrams Using Reinforcement Learning

SkinnerDB: Regret-bounded Query Evaluation via Reinforcement Learning