[IMPALA-9974] Join elimination based on referential integrity

• Type: Sub-task
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: Frontend
• Epic Color: ghx-label-4
• Assignee: Unassigned
• Reporter: Shant Hovsepian
• Created: 19/Jul/20 16:51
• Updated: 19/Jul/20 16:51

Description

A join can be eliminated if the following conditions are met.

1. An FK/PK relationship exists between two joined table.

2. Only the PK columns are used and no other columns from the PK table.

3. The query predicates are conjunctive

4. The PK column can only appear in the equality predicate of the WHERE clause.

If the above conditions are met then the join can be eliminated, all PK column reference will be substituted by their FK counterparts, and an IS NOT NULL condition will be added for the FK columns.

This can be applied to INNER and OUTER joins. Assuming f.id -> d.id

SELECT f.* FROM fact f INNER JOIN dim d ON (f.id = d.id); 
=>
SELECT f.* FROM fact f WHERE f.id IS NOT NULL;