Hybrid Query Cost Model for Out-of-Distribution Plan Evaluation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing query optimizers in relational database management systems face inaccuracies in cardinality estimation, leading to suboptimal query plans due to reliance on classic cost models and machine learning-based models' limitations with out-of-distribution samples.
Innovation Solution
A hybrid cost model that combines classic and learned cost models with a query classifier to route queries to the most accurate estimation method, leveraging a meta-ensemble approach to balance performance and minimize errors.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If a classic cost model is used for query execution plan evaluation, then the system has simple and well-understood cost estimation, but the cardinality estimation becomes inaccurate leading to suboptimal query plans
Solution Approach 1:
The patent combines classic cost models and machine learning-based cost models into a hybrid cost model. The query optimizer evaluates multiple candidate query execution plans using both classic and learned cost models, then selects the plan with the lowest estimated cost according to the learned model's predictions. This merging approach leverages the strengths of both paradigms: the interpretability of classic models and the predictive accuracy of learned models for in-distribution queries.
Solution Approach 2:
The hybrid cost model serves multiple functions: it provides accurate cost estimation for common query patterns using learned models, maintains fallback capability using classic models, and handles edge cases through ensemble voting. The system can adapt to different query distributions and workload characteristics, making it universally applicable across diverse database workloads.
2Measurement precision
If machine learning-based cost models are used to improve cardinality estimation accuracy, then query plan selection improves for common patterns, but performance degrades on out-of-distribution samples
Solution Approach 1:
The system prepares for potential failures of learned models by maintaining classic cost models as a safety net. When the learned model encounters an out-of-distribution query, the system can fall back to the classic model's cost estimation, preventing complete failure. This beforehand cushioning ensures continuous operation with acceptable performance even when the learned model is uncertain.
Solution Approach 2:
The query optimizer receives feedback from executing queries and measuring actual execution costs. This feedback is used to retrain and refine the learned cost models over time, improving their accuracy for the specific workload distribution. The feedback mechanism allows the system to adapt to changing patterns and gradually expand the learned model's reliability.
3Productivity
If a hybrid cost model combining multiple base cost models is used, then query plan optimization improves, but the system complexity and computational overhead increase
Solution Approach 1:
The hybrid cost model segments the cost estimation task by dividing queries into different categories based on their characteristics. The query optimizer uses a classifier to determine which base cost models should be applied to each query type. This segmentation allows the system to apply complex learned models only to queries where they provide value, while using simpler classic models for routine queries, reducing overall computational overhead.
Solution Approach 2:
Instead of always using the most accurate learned cost models, the system applies them partially only when needed based on query characteristics and distribution. For queries that match known patterns, the learned models provide enhanced accuracy. For queries that don't match training distributions, the system uses classic models or ensemble voting, avoiding unnecessary computational expense while maintaining acceptable performance.
Data Source
AI summary
Aspects of the disclosure include hybrid cost model-based techniques for evaluating query execution plans. A non-limiting example method includes inputting, to a plurality of base cost models including one or more learned cost models and a classic cost model, a query and a search space including a plurality of candidate query execution plans. Each base cost model outputs a predicted execution time or cost for each plan of the plurality of candidate query execution plans and a real execution time for each plan is determined. The method includes generating a training label including the query and a model of the base cost models having a highest correlation between the predicted and real execution times and training a query classifier on training data including the training label to predict which base cost model of the plurality of base cost models is a most suitable cost model for planning a given query.