Adaptive Cardinality Estimation via ML Feedback
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current query optimization methods in distributed computing environments face challenges in accurately estimating query execution times due to large variances and the complexity of database structures, leading to sub-optimal plans and inefficiencies, as they rely on outdated models that fail to adapt to changing data relations and often produce inaccurate results.
Innovation Solution
Implementing a machine learning-based system that uses workload feedback and runtime feedback to continuously learn and update models, allowing for adaptive cardinality estimation and optimization, enabling the identification of errors in estimates and the retraining of models to improve query execution efficiency.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If traditional query optimization methods are used, then the system can execute queries with basic performance, but the variance in execution times is large and accuracy is poor
Solution Approach 1:
The patent implements a feedback mechanism where actual query execution results are collected and used to update cardinality estimation models. The system compares estimated cardinalities with actual cardinalities from query execution, feeds this difference back to the machine learning model, and retrains the model to improve future estimates. This closed-loop feedback system continuously reduces estimation errors and execution time variance.
Solution Approach 2:
The system performs preliminary actions by pre-collecting statistics and pre-training machine learning models on historical query data before actual query execution. Cardinality estimation models are trained in advance on representative workloads, and the system prepares optimized query plans using these pre-trained models, reducing the need for complex real-time optimization during query execution.
2Adaptability or versatility
If complex database structures are accessed through multiple paths and join methods, then query flexibility is improved, but processing time variance increases
Solution Approach 1:
The patent applies dynamics by making the query optimization system adaptive and dynamic rather than static. The cardinality estimation models are continuously updated based on actual execution feedback, allowing the system to dynamically adjust to changing data distributions and query patterns. This enables the system to maintain optimal performance across multiple execution paths and join methods despite varying data conditions.
3Device complexity
If the query optimizer uses fixed models for cardinality estimation, then the system is simpler to implement, but the models fail to adapt to changing data relations
Solution Approach 1:
The system implements self-service by enabling the query optimizer to automatically improve itself without external intervention. The machine learning models autonomously collect execution feedback, identify estimation errors, retrain on new data, and update their parameters automatically. This self-improving mechanism allows the system to adapt to changing data relations while maintaining reasonable complexity through automated processes.
Data Source
AI summary
In an approach to improve query optimization in a database management system, embodiments identify opportunities for improvement in a cardinality estimate using a workload feedback process using a query feedback performed during query compilation. Embodiments identify correlations and relationships based on the structure of the query feedback and the runtime feedback performed, and collects data from the execution of a query to identify errors in estimates of the query optimizer. Further, embodiments submit the query feedback and the runtime feedback to a machine learning engine to update a set of models. Additionally, embodiments update a set of models based on the submitted query feedback and runtime feedback, and output a new, updated, or re-trained model based on collected data from the execution of the query to identify the errors in estimates of the query optimizer, the submitted query feedback and the runtime feedback, or a trained generated mode.