SetSketch Join Order Optimization for Database Cardinality
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing methods for optimizing the order of joining multiple tables in database systems are not robust enough to handle three or more tables effectively, leading to varying intermediate table sizes and subsequent inefficiencies in memory consumption and CPU load.
Innovation Solution
A computer-implemented method that generates probabilistic data structures, such as SetSketches, for each table involved in join operations, calculates cardinality estimates using these structures, and selects the join operation with the lowest cardinality estimate to optimize the join order.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If traditional join optimization methods (HLL sketches) are used for two tables, then distinct value estimation is improved, but robustness for three or more tables deteriorates
Solution Approach 1:
The patent introduces SetSketch as an intermediary probabilistic data structure that bridges the gap between MinHash and HLL sketches. SetSketch uses a novel hashing function with a parameter α that allows it to adapt between the behaviors of MinHash and HLL, providing both accurate distinct value estimation and reliable intersection cardinality estimation for three or more tables. This intermediary structure resolves the contradiction by combining the strengths of both previous approaches while eliminating their weaknesses.
2Quantity of substance
If different join orders are used for three tables, then intermediate table size varies significantly, but memory consumption and CPU load increase with larger intermediate tables
Solution Approach 1:
The patent applies preliminary action by using SetSketch to estimate the intermediate table size for all possible join orders before actually executing the joins. The system calculates cardinality estimates for each potential intermediate table using the probabilistic data structures, then selects the join order that produces the smallest intermediate tables. This preliminary estimation allows the system to avoid executing joins that would create large intermediate tables, thereby preventing excessive memory consumption and CPU load.
3Productivity
If existing probabilistic data structures are used for join optimization, then two-table joins are optimized, but handling of multiple join operations deteriorates
Solution Approach 1:
The patent makes the probabilistic data structure universal by designing SetSketch to handle both two-table and multi-table join operations with the same structure and algorithms. The SetSketch data structure can estimate intersections of any number of tables (two, three, four, or more) using the same underlying mechanism, eliminating the need for different optimization approaches for different numbers of tables. This multi-functionality resolves the contradiction by making the system equally effective for all join scenarios.
Data Source
Figure 1
Figure 2~3
Figure 4
AI summary
A computer-implemented method is presented for joining tables in a database system. The method includes: a) receiving a request to join tables according to a join query; b) generating a probabilistic data structure for each table specified in the set of join operations; c) for each join operation, calculating a cardinality estimate of table resulting from a particular join operation using the probabilistic data structures for the tables to be joined; d) selecting a join operation in the set of join operations, where the selected join operation has the lowest cardinality estimate amongst the join operations; e) removing the selected join operation from the set of join operations; f) replacing the tables to be joined by the selected join operation with the joint of these tables in the set of join operations; and repeating steps c) to f) until the set of join operations comprises a single join operation.