Outer Join Predicate Pushdown for MPP Database Performance
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In massively parallel processing systems, the high processing costs of parallel joins become undesirable due to excessive redistribution and duplication of tables during join operations, especially when join predicates do not include the primary index, leading to inefficient performance in relational database management systems.
Innovation Solution
The solution involves pushing down predicates involving only attributes of the left table in left outer joins to the outer relation, partitioning the left table into two sub-relations based on these predicates, and only performing the join with the right table for the sub-relation that satisfies the predicate, thereby reducing the number of rows to be redistributed and joined.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If tables are redistributed and duplicated to complete join operations in MPP systems, then join operations can be performed, but processing costs become excessively high
Solution Approach 1:
The patent segments the left table into two sub-relations based on the outer table predicate: rows satisfying the predicate form the first sub-relation, while rows not satisfying it form the second sub-relation. This segmentation allows the system to avoid redistributing and joining the entire left table, thereby reducing processing costs while maintaining join operation functionality.
2Reliability
If all rows of the left table are joined with the right table, then complete join results are obtained, but the number of rows to be redistributed and joined becomes excessively large
Solution Approach 1:
The patent extracts only the necessary subset of rows from the left table for the join operation. By evaluating the outer table predicate on the left table rows beforehand, the system identifies and extracts only those rows that satisfy the predicate (first sub-relation), excluding rows that would not contribute to the join result. This extraction maintains result completeness while dramatically reducing the quantity of rows requiring redistribution and joining.
3Measurement precision
If predicates involving only left table attributes are evaluated during the join operation, then accurate join results are produced, but processing efficiency is reduced
Solution Approach 1:
The patent performs preliminary evaluation of outer table predicates on the left table rows before the actual join operation. By pushing down the predicate evaluation to occur before redistribution and joining, the system pre-identifies which rows should participate in the join. This preliminary action ensures accurate join results (as all necessary filtering is done correctly) while improving processing efficiency (as unnecessary rows are filtered out before expensive redistribution and join operations).
Data Source
AI summary
A system, method, and computer-readable medium for optimizing the performance of outer joins in a parallel processing system are provided. Predicates involving only attributes of a left table of a left outer join are pushed down to the outer relation for left outer joins having join predicates involving left table attributes and/or predicates involving attributes of both the right and left table. In such an instance, the rows of the left table may be partitioned into two sub-relations according to the predicate involving only attributes of the left table. Rows of the left table are allocated to a first sub-relation if the rows satisfy the predicate involving only attributes of the left table and rows of the left table are allocated to a second sub-relation if the rows fail to satisfy the predicate involving only attributes of the left table. Accordingly, only rows of the first sub-relation are required to be left outer joined with the right table. Advantageously, a reduction in the requisite number of rows to be redistributed and joined is facilitated. The disclosed embodiments may be similarly applied for optimization of right outer joins. Further, embodiments for optimizing full outer joins are disclosed.