AutoQuo: An Adaptive plan optimizer with reinforcement learning for query plan selection

Abstract

An efficient execution plan generation is crucial for optimizing database queries. In exploring large table spaces to identify the most optimal table join orders, traditional cost-based optimizers may encounter challenges when confronted with complicated queries. Thus, learning-based optimizers have recently been proposed to leverage past experience and generate high-quality execution plans. However, these optimizers demonstrate limited generalization capabilities for workloads with diverse distributions.

In this study, an adaptive plan selector based on reinforcement learning is proposed to address these issues. However, three challenges remain: (1) How to generate optimal multi-table join orders? We adopt an exploration–exploitation strategy to traverse the vast search space composed of candidate tables, thereby evaluating the significance of each table. Long short-term memory (LSTM) networks are subsequently used to predict the performance of join orders and generate high-quality candidate plans. (2) How to automatically learn new features in novel datasets? We employ the Actor–Critic strategy, which involves jointly cross-training the policy and value networks. By adjusting the parameters based on real feedback obtained from the database, the new datasets are automatically learnt. (3) How to automatically select the best plan? We introduce a constraint-aware optimal plan selection model that captures the relationship between constraints and plans. This model guides the selection of the best plan under constraints of execution time, cardinality, cost, and mean-squared error (MSE). The experimental results on real datasets demonstrated the superiority of the proposed approach over state-of-the-art baselines. Compared with PostgreSQL, we observed a reduction of 29.73% in total latency and 28.36% in tail latency.