• Igor Babaev's avatar
    MDEV-32351 Significant slowdown for query with many outer joins · 954a6dec
    Igor Babaev authored
    This patch fixes a performance regression introduced in the patch for the
    bug MDEV-21104. The performance regression could affect queries for which
    join buffer was used for an outer join such that its on expression from
    which a conjunctive condition depended only on outer tables can be
    extracted. If the number of records in the join buffer for which this
    condition was false greatly exceeded the number of other records the
    slowdown could be significant.
    
    If there is a conjunctive condition extracted from the ON expression
    depending only on outer tables this condition is evaluated when interesting
    fields of each survived record of outer tables are put into the join buffer.
    Each such set of fields for any join operation is supplied with a match
    flag field used to generate null complemented rows. If the result of the
    evaluation of the condition is false the flag is set to MATCH_IMPOSSIBLE.
    When looking in the join buffer for records matching a record of the
    right operand of the outer join operation the records with such flags
    are not needed to be unpacked into record buffers for evaluation of on
    expressions.
    
    The patch for MDEV-21104 fixing some problem of wrong results when
    'not exists' optimization by mistake broke the code that allowed to
    ignore records with the match flag set to MATCH_IMPOSSIBLE when looking
    for matching records. As a result such records were unpacked for each
    record of the right operand of the outer join operation. This caused
    significant execution penalty in some cases.
    
    One of the test cases added in the patch can be used only for demonstration
    of the restored performance for the reported query. The second test case is
    needed to demonstrate the validity of the fix.
    954a6dec
join_cache.result 228 KB