Heterogeneous Histograms for Bounded Cardinality Estimation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database management systems face challenges in providing accurate and efficient cardinality estimates for query optimization, often resulting in unbounded estimation errors and inefficient histogram construction due to the manner of data storage.
Innovation Solution
The use of histograms with a q-error metric to guarantee bounded error in cardinality estimation, combined with discrete dense domains for faster histogram construction and dictionary compression to eliminate gaps in data representation, ensuring precise query optimization and efficient plan generation.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If traditional histograms are used for cardinality estimation, then the query optimizer can generate execution plans, but the estimation errors are unbounded leading to suboptimal plans
Solution Approach 1:
The data domain is segmented into multiple heterogeneous histograms, each capturing different portions of the data distribution. This segmentation allows the system to provide bounded error guarantees for cardinality estimation by dividing the estimation problem into manageable segments with controlled error bounds.
Solution Approach 2:
The invention changes the parameter representation by using discrete dense domains with dictionary compression. This transforms the continuous data domain into a compressed discrete representation, enabling efficient histogram construction while maintaining bounded error guarantees for cardinality estimation.
2Measurement precision
If histograms are constructed by scanning data in traditional storage formats, then cardinality estimates can be generated, but the construction process is inefficient
Solution Approach 1:
The system performs preliminary actions by constructing heterogeneous histograms during data loading or maintenance operations, rather than scanning the entire dataset at query optimization time. This preliminary construction stores pre-computed statistics that can be quickly retrieved and used for efficient cardinality estimation.
Solution Approach 2:
By changing the data representation to discrete dense domains with dictionary compression, the invention enables faster histogram construction. The compression reduces the effective data size that needs to be processed, directly reducing construction time while maintaining estimation precision.
3Reliability
If continuous data domains are used for histogram construction, then comprehensive data coverage is achieved, but the histogram construction and storage become inefficient
Solution Approach 1:
The invention transforms the continuous data domain parameter into a discrete dense domain through dictionary compression. This parameter change maps continuous values to compressed discrete identifiers, reducing storage requirements while maintaining the ability to accurately represent and estimate cardinality across the full data range.
Solution Approach 2:
Instead of storing actual continuous data values in histograms, the system uses compressed dictionary copies or references. This copying approach stores only the essential statistical information in compressed form, significantly reducing storage complexity while preserving data representation accuracy.
Data Source
Figure 1
Figure 2
Figure 3~4
AI summary
A method and system to generate cardinality estimates for at least one query execution plan for a query using a histogram, the cardinality estimation being constrained by a q-error that is a factor by which the estimate deviates, at most, from a true value of the cardinality and a threshold value which the cardinality does not exceed; use the cardinality estimation to determine an optimal query plan for executing the query; and produce an output of the optimal query plan.