Enhance join prefilter optimizer

[kaikalur]

Currently we only support it when the other side is scan. We should enhance this feature to:

a) union all of scan
b) if the side has an aggregation on the join key we can still do it (and not do distinct)
c) drop any rows where any of the keys is null (especially important for multi-key joins)
d) make it cost based so that we prefilter the "correct" side which gives best results

• also do it for outer joins - flipping the sides and left/right outer join if needed

CC: @feilong-liu

[Shravanpmk]

Can you please give me more details on where exactly we should do this optimization ?
PS : I am starting with presto. so some details will help me navigate the problem and contribute to the solution. Thanks

[Shravanpmk]

If I am not wrong it should in the file : presto-main/src/main/java/com/facebook/presto/sql/planner/optimizations/JoinPrefilter.java

[Shravanpmk]

@kaikalur Can you please guide me through this issue
Would be of much help thanks

[kaikalur]

@kaikalur Can you please guide me through this issue Would be of much help thanks

Yes you found the code. Basically, right now we do it only if the other side is only a scan+filter+project. So extend it for other cases that I listed above.