Group-By Query Cardinality Estimation Using Machine Learning
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current cardinality estimation methods in database management systems face challenges in accurately estimating distinct value counts for intermediate results, leading to suboptimal query plans and poor performance due to reliance on sampling-based methods and histogram approaches that lack accuracy and scalability.
Innovation Solution
The use of a machine learning-based approach that involves obtaining probability of occurrence values from trained models, generating valid sample tuples, calculating selectivity, and applying progressive sampling to estimate group-by cardinality, thereby improving the accuracy and efficiency of cardinality estimation.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If pre-computation of result tables is performed to determine distinct value counts, then measurement precision of cardinality estimation is improved, but loss of time and computational resources increases
Solution Approach 1:
The system pre-computes and stores cardinality information in histograms and statistics structures during data loading or maintenance operations, so that when query optimization needs cardinality estimates, the data is already prepared and available without requiring full result table materialization at query time
Solution Approach 2:
Instead of materializing actual result tables for cardinality estimation, the system creates simplified copies in the form of histograms and statistical summaries that capture the essential cardinality characteristics of the data, enabling fast estimation without the computational cost of full query execution
2Measurement precision
If multi-column histograms are used to improve cardinality estimation accuracy for correlated columns, then measurement precision is improved, but device complexity and computational overhead increase
Solution Approach 1:
The system segments the complexity by separating single-column histograms (which are simple and fast) from multi-column histograms (which are created only when column correlation is detected). This allows the system to use simple structures most of the time while introducing complexity only where needed for correlated columns
Solution Approach 2:
The system dynamically determines whether to use single-column or multi-column histograms based on the specific query and data characteristics. Multi-column histograms are constructed on-demand or selectively for columns with detected correlations, rather than pre-defining all possible column combinations, making the structure adaptive rather than static
3Device complexity
If independent column assumptions are made in single-column histograms, then device complexity is reduced, but measurement precision deteriorates due to overestimation of cardinality
Solution Approach 1:
The system performs preliminary analysis to detect correlated columns and pre-marks them for multi-column histogram treatment. This preliminary identification allows the system to apply the more complex multi-column approach only where necessary, rather than assuming independence for all columns or pre-computing all possible multi-column histograms
Data Source
AI summary
Mechanisms are disclosed for estimating cardinality of group-by queries. A probability of occurrence of values is obtained for columns that satisfy the query occurring in tables from a trained machine learning model. A range selectivity is calculated based on a conditional probability of occurrence of the values. A set of valid generated sample tuples is generated from the trained machine learning model. A group-by selectivity is calculated by keeping the conditional probability of occurrence to obtain probabilities that a result set will have specific group-by column values associated with the tables while proceeding with progressive sampling. A sampling probability is calculated by normalizing the group-by selectivity by dividing the group-by selectivity by the range selectivity. The samples are filtered such that the samples having a sampling probability below a sampling probability threshold are filtered out. A sampling-based estimator is applied to the filtered samples set to estimate the cardinality.