Reinforcement Learning with Tree-LSTM for Join Order Selection

Join order selection (JOS) -the problem of finding the optimal join order for an SQL query -is a primary focus of database query optimizers. The problem is hard due to its large solution space. Exhaustively traversing the solution space is prohibitively expensive, which is often combined with heuristic pruning. Despite decades-long effort, traditional optimizers still suffer from low scalability or low accuracy when handling complicated SQL queries. Recent attempts using deep reinforcement learning (DRL), by encoding join trees with fixed-length handtuned feature vectors, have shed some light on JOS. However, using fixed-length feature vectors cannot capture the structural information of a join tree, which may produce poor join plans. Moreover, it may also cause retraining the neural network when handling schema changes (e.g., adding tables/columns) or multialias table names that are common in SQL queries.

In this paper, we present RTOS, a novel learned optimizer that uses Reinforcement learning with Tree-structured long shortterm memory (LSTM) for join Order Selection. RTOS improves existing DRL-based approaches in two main aspects: (1) it adopts graph neural networks to capture the structures of join trees; and (2) it well supports the modification of database schema and multi-alias table names. Extensive experiments on Join Order Benchmark (JOB) and TPC-H show that RTOS outperforms traditional optimizers and existing DRL-based learned optimizers. In particular, the plan RTOS generated for JOB is 101% on (estimated) cost and 67% on latency (i.e., execution time) on average, compared with dynamic programming that is known to produce the state-of-the-art results on join plans.

Example 1 shows that, join trees with different join orders may be encoded into the same feature vector using existing learners; that is, they only consider the static information (such as tables and columns) of a join, without being able to capture the structural information of a join tree. Intuitively, a better learner should also understand different structural information of different join trees.

Our Methodology. Based on the above observation, we present RTOS, a novel learned optimizer using tree-structured long short-term memory (Tree-LSTM) [37]. RTOS trains a DRL model for JOS, which can automatically improve future JOS by learning from previously executed queries.

Tree-LSTM is one kind of graph neural networks (GNNs).

1297