Cardinality Models for Cloud Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing query optimization techniques in shared cloud infrastructures face challenges in accurately predicting cardinality, leading to poor quality plans and higher costs for end users due to incorrect cost estimates.
Innovation Solution
A machine learning-based approach is employed to train cardinality models by analyzing workload data, extracting features of subgraphs, and using these models to predict cardinalities, thereby improving the accuracy of cost estimates.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If traditional cost-based query optimizer is used, then query plan generation is fast, but cardinality estimation accuracy is poor leading to incorrect cost estimates
Solution Approach 1:
The system pre-trains cardinality estimation models using historical workload data before actual query execution. This preliminary action creates ready-to-use predictive models that can be quickly applied during query optimization, avoiding the need for complex real-time calculations while maintaining high accuracy.
Solution Approach 2:
The patent introduces machine learning models as an intermediary between traditional cost-based optimization and actual query execution. These models serve as a bridge that provides accurate cardinality estimates without requiring the optimizer to perform time-consuming detailed analysis, thus resolving the contradiction between accuracy and speed.
2Reliability
If accurate cardinality estimation is achieved through traditional methods, then cost estimates improve, but computing resources and time consumption increase
Solution Approach 1:
The system uses historical workload data that has already been processed and stored during normal operation. By leveraging this existing data infrastructure, the cardinality models can be trained and deployed without requiring additional expensive computing resources during query execution, making the system self-sufficient.
Solution Approach 2:
The patent replaces traditional mechanical computation methods (complex join order evaluations, detailed statistics calculations) with machine learning-based predictions. This substitution dramatically reduces computing resource consumption while maintaining or improving cost estimate accuracy.
3Measurement precision
If machine learning models are trained on workload data, then cardinality prediction accuracy improves, but model training and deployment complexity increases
Solution Approach 1:
The patent segments the cardinality estimation problem into multiple models, each specialized for specific query patterns or data types. This segmentation allows for simpler, more focused model training while maintaining overall system accuracy, and enables selective deployment based on query characteristics.
Solution Approach 2:
The system dynamically adjusts model parameters and selection based on the specific query being executed. By changing parameters such as which model to use or how to configure it, the system achieves high accuracy without requiring all possible complexity to be present in every model deployment.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
Described herein is a system and method for training cardinality models in which workload data is analyzed to extract and compute features of subgraphs of queries. Using a machine learning algorithm, the cardinality models are trained based on the features and actual runtime statistics included in the workload data. The trained cardinality models are stored. Further described herein is a system and method of predicting cardinality of subgraphs of a query. Features for the subgraphs of the query are extracted and computed. Cardinality models are retrieved based on the features of the subgraphs of the query. Cardinalities of the subgraphs of the query are predicted using the retrieved cardinality models. One of the subgraphs of the query is selected to be utilized for execution of the query based on the predicted cardinalities.