@Hanu: Can you clarify a bit more where is the problem? >From a quick loon in LoptOptimizeJoinRule it seems that all joins to be re-ordered are grouped using a MultiJoin operator. Have you checked that the SemiJoin in question is part of the MultiJoin operator?
In general semi joins are more difficult to re-order with other joins since they do not have neither the commutative nor the associative property. There are other properties that allow the re-ordering of semi-joins but I am not aware if they are exploited by LoptOptimizeJoinRule. If you plan to work on this you may find interesting the work by Moerkotte et. al. on join enumeration [1]. Best, Stamatis [1] On the Correct and Complete Enumeration of the CoreSearch Space <https://www.researchgate.net/profile/Guido_Moerkotte/publication/262216932_On_the_correct_and_complete_enumeration_of_the_core_search_space/links/58467f2c08ae61f75ddb205c/On-the-correct-and-complete-enumeration-of-the-core-search-space.pdf> Στις Δευ, 14 Ιαν 2019 στις 7:31 π.μ., ο/η Julian Hyde <[email protected]> έγραψε: > I don't see any reason in principle why LoptOptimizeJoinRule could not > handle semi-joins. Of course it will have to remember that these are > semi-joins so that it can re-create them as semi-joins afterwards. And > there may be complications because a semi-join eliminates duplicates > in its right-hand input (or rather, the number of rows is not > multiplied if a key has multiple values) and therefore moving a > semi-join up and down the join graph may not affect row-counts in the > expected way. > > It's certainly beneficial to push semi-joins down in many cases. > > Julian > > > On Fri, Jan 11, 2019 at 8:41 PM hanu mapr <[email protected]> wrote: > > > > Hello all, > > > > I am working on an issue wherein one of the tpcds query is regressing > when > > semijoin is also present with other inner joins in the query. Currently, > > DRILL uses LoptOptimizeJoinRule for join order optimization. During the > > debugging process, I observed that LoptOptimizeJoinRule is not > considering > > semi joins for join order optimization. > > Please do let me know if my understanding is correct. If so what is the > > best option for handling this scenario?. > > > > Thanks in advance. > > > > Regards, > > -Hanu >
