Learn What Really Matters: A Learning-to-Rank Approach for ML-based Query Optimization
Query optimization is crucial for any data management system to achieve good performance. Recent advancements in Machine Learning (ML) have led to several efforts in the database research community that aim at improving query optimization with the help of ML. In particular, many works propose replacing the cost model used during plan enumeration with an ML model. The goal of these works is to learn a regression model from previously executed query plans that estimates the runtime of a given plan. Interestingly, it is well-known that what really matters in query optimization is the relative order of the query plan alternatives and not their actual cost or runtime. We thus take a learning-to-rank approach and propose a novel neural network model architecture that can predict the rank of a plan. It considers a plan in comparison with alternative plans of the same query and together with a loss function that incorporates ranking metrics into the learning process we highlight the learning-to-rank objective.To enable training, we first extract features from query plans by adapting a state-of-the-art deep learning approach so that all features are independent of the input dataset schema. Second, we devise two score functions that map the runtime of plans to scores which are then used as labels during training. We integrate the trained model into an adapted bottom-up plan enumeration algorithm that finds the best possible execution plan for a given query. We evaluate our approach against two state-of-the-art ML models and the highly tuned cost model of a commercial database and measure the runtime of the plans chosen in each case when executed in the database. We show that our approach achieves up to an order of magnitude better query performance than the comparison models and is able to either match (for short and medium-running queries) or outperform the commercial database (up to 5× for long-running queries).
