[HIVE-20090] Extend creation of semijoin reduction filters to be able to discover new opportunities

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 3.2.0, 4.0.0-alpha-1
• Component/s: Physical Optimizer
• Assignee: Jesús Camacho Rodríguez
• Reporter: Jesús Camacho Rodríguez
• Created: 04/Jul/18 20:56
• Updated: 27/Feb/24 22:23
• Resolved: 14/Jul/18 05:16

Description

Assume the following plan:

TS[0] - RS[1] - JOIN[4] - RS[5] - JOIN[8] - FS[9]
TS[2] - RS[3] - JOIN[4] 
TS[6] - RS[7] - JOIN[8]

Currently, TS[6] may only be reduced with the output of RS[5], i.e., input to join between both subplans.
However, it may be useful to consider other possibilities too, e.g., reduced by the output of RS[1] or RS[3]. For instance, this is important when, given a large plan, an edge between RS[5] and TS[0] would create a cycle, while an edge between RS[1] and TS[6] would not.

This patch comprises two parts. First, it creates additional predicates when possible. Secondly, it removes duplicate semijoin reduction branches/predicates, e.g., if another semijoin that consumes the output of the same expression already reduces a certain table scan operator (heuristic, since this may not result in most efficient plan in all cases). Ultimately, the decision on whether to use one or another should be cost-driven (follow-up).

Issue Links

• links to: RB link