Skip to content
  • Guilhem Bichot's avatar
    5d9500be
    Bug#31376809 PERFORMANCE REGRESSION FROM 5.7->8.0, DUE TO ANTIJOIN OF NOT EXISTS SUBQUERY · 5d9500be
    Guilhem Bichot authored
    Disable antijoin transformation if this is:
    - NOT EXISTS, or <constant> NOT IN,
    - and the subquery is not correlated.
    Otherwise we got this type of plan:
    nested loop antijoin
       -> outer
       -> inner
    and we would read each row of "outer", only to find, every time, that
    there is a row in "inner" and that we should not emit the row of "outer".
    Instead, we evaluate the subquery during optimization, leading to an always
    true or false condition.
    This does not cancel Steinar's patch for the same bug report; however it
    makes "case 2" (see his commit comment) apply only to antijoin coming from
    LEFT JOIN, as antijoin coming from NOT EXISTS will not reach the executor
    anymore, due to the present patch.
    
    Approved by: Roy Lyseng <roy.lyseng@oracle.com>
    
    Change-Id: I59a887456a56039a6b1480b3ec8f7ac188d1ce58
    5d9500be
    Bug#31376809 PERFORMANCE REGRESSION FROM 5.7->8.0, DUE TO ANTIJOIN OF NOT EXISTS SUBQUERY
    Guilhem Bichot authored
    Disable antijoin transformation if this is:
    - NOT EXISTS, or <constant> NOT IN,
    - and the subquery is not correlated.
    Otherwise we got this type of plan:
    nested loop antijoin
       -> outer
       -> inner
    and we would read each row of "outer", only to find, every time, that
    there is a row in "inner" and that we should not emit the row of "outer".
    Instead, we evaluate the subquery during optimization, leading to an always
    true or false condition.
    This does not cancel Steinar's patch for the same bug report; however it
    makes "case 2" (see his commit comment) apply only to antijoin coming from
    LEFT JOIN, as antijoin coming from NOT EXISTS will not reach the executor
    anymore, due to the present patch.
    
    Approved by: Roy Lyseng <roy.lyseng@oracle.com>
    
    Change-Id: I59a887456a56039a6b1480b3ec8f7ac188d1ce58
Loading