OLAP Join Cardinality Estimation via Factor Graphs
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing data query methods in large-scale online analytical processing (OLAP) systems face challenges in accurately estimating the join cardinality due to complex correlations and highly skewed distributions of join key values, leading to low precision in query optimization.
Innovation Solution
A data query method based on online analytical processing that constructs a factor graph from target join keys in a structured query statement, using factor nodes to represent conditional probability distributions, and estimates an upper limit value of the join cardinality using this graph.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If traditional join cardinality estimation methods are used, then the query optimization process is simple, but the precision of join cardinality estimation is low due to complex correlations and highly skewed distributions of join key values
Solution Approach 1:
The patent segments the join cardinality estimation problem into multiple independent steps: (1) obtaining filtered value ranges for each join key from query conditions, (2) calculating selection ratios based on histograms and value ranges, (3) computing intermediate join cardinalities using selection ratios, and (4) determining the final join cardinality through iterative optimization. This segmentation allows each step to be processed independently, improving precision while managing complexity through structured computation.
Solution Approach 2:
The patent performs preliminary actions by pre-computing histograms for each join key and storing value distribution statistics before the actual query execution. During query optimization, these pre-computed histograms are directly utilized to calculate selection ratios and join cardinalities, eliminating the need for complex real-time distribution analysis and reducing the computational burden during query processing.
2Measurement precision
If complex probability distribution models are used to improve estimation precision, then the accuracy of join cardinality estimation improves, but the computational overhead increases
Solution Approach 1:
The patent uses lightweight histogram data structures that are inexpensive to compute and store, replacing complex probability distribution models. The histograms are computed once during data loading and discarded after query optimization, providing accurate value distribution information without the computational overhead of maintaining complex statistical models during query execution.
Solution Approach 2:
The patent computes selection ratios and join cardinalities for only the relevant join keys involved in the current query, rather than analyzing the entire dataset or all possible join combinations. This partial computation approach focuses resources on the specific query requirements, reducing unnecessary computational overhead while maintaining estimation accuracy for the queried join operations.
Data Source
AI summary
The present disclosure relates to a data query method and apparatus based on online analytical processing, a medium, and a device. The method includes: obtaining a plurality of target join keys between query tables in a structured query statement in response to reception of the structured query statement; constructing a first factor graph corresponding to the structured query statement based on the plurality of target join keys, where a factor node in the first factor graph is used to represent a conditional probability distribution in the case of presence of the target join keys in a query table corresponding to the factor node; and estimating an upper limit value of a join cardinality of the structured query statement based on the first factor graph.