Machine Learning Index Advisor for Database Query Cost Prediction
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing index advisors in databases rely on cost estimates from query optimizers, which often lead to incorrect index configuration recommendations, resulting in query performance regressions and hindering automated indexing in production systems.
Innovation Solution
A machine learning-based approach is employed to predict execution costs by training a classifier model using relative execution cost comparisons between query plans, allowing for more accurate index configuration recommendations and adaptive tuning.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Extent of automation
If cost estimates from query optimizer are used for index recommendations, then automated indexing can be performed, but the accuracy of execution cost prediction deteriorates leading to incorrect recommendations
Solution Approach 1:
The patent introduces an intermediary machine learning model that acts as a mediator between the query optimizer's cost estimates and the actual execution cost. This ML model is trained on historical query execution data to learn the mapping from optimizer estimates to actual costs, thereby improving prediction accuracy without eliminating automated indexing capability
Solution Approach 2:
The system implements feedback by continuously collecting actual query execution costs and using them to retrain and refine the machine learning model. This closed-loop feedback mechanism allows the system to learn from past predictions and improve its cost estimation accuracy over time while maintaining automated operation
2Measurement precision
If more index configurations are tested to improve recommendation accuracy, then prediction accuracy improves, but query execution time and system resource consumption increase
Solution Approach 1:
The patent applies preliminary action by pre-training the machine learning model on a comprehensive dataset of query execution costs before deployment. This allows the model to learn from diverse scenarios in advance, so that during actual operation, accurate predictions can be made without needing to execute numerous test queries, thus saving time
3Device complexity
If traditional index advisors are used, then system complexity remains low, but reliability of index recommendations deteriorates due to estimation errors
Solution Approach 1:
The patent substitutes the mechanical system of rule-based cost estimation with a machine learning-based predictive system. This replacement enables the system to capture complex, non-linear relationships in query execution behavior that traditional mechanical estimation methods cannot, thereby improving reliability while managing complexity through standardized ML pipelines
Data Source
Figure 1
Figure 2
Figure 3
AI summary
Systems and techniques for leveraging query executions to improve index recommendations are described herein. In an example, a machine learning model is adapted to receive a first query plan and a second query plan for performing a query with a database, where the first query plan is different from the second query plan. The machine learning model may be further adapted to determine execution cost efficiency between the first query plan and the second query plan. The machine learning model is trained using relative execution cost comparisons between a set of pairs of query plans for the database. The machine learning model is further adapted to output a ranking of the first query plan and second query plan, where the first query plan and second query plan are ranked based on execution cost efficiency.