gistfile1.txt

commit 432a4ebe5cd2ebf4d0fad79092e82e5d1a9f53ba
Author: Michael Widenius <monty@mariadb.org>
Date:   Wed May 18 22:17:32 2022 +0300

    Improve table pruning in optimizer with up to date key_dependent map

    Part of:
    MDEV-28073 Slow query performance in MariaDB when using many tables

    s->key_dependent has a list of tables that are compared with key fields
    in the current table.  However it does not take into account if a key
    field could be resolved by another table.
    This is because MariaDB expands 'join_tab->keyuse' to include all generated
    comparisons.
    For example:
    SELECT * from t1,t2,t3 where t1.key=t2.key and t2.key=t3.key
    In this case keyuse for t1 includes t2.key and t3.key and key_dependent
    contains 't2.map | t3.map'
    If we in best_extension_by_limited_search() consider t2,t1 then t1's
    key is fully defined, but we cannot do any prune of plans as
    s->key_dependent indicates that t3 is still needed.

    Fixed by calculating in best_access_patch the current key_dependent map
    of tables that is needed to satisfy all keys. This allows us to prune
    more bad plans earlier as soon as all keys can be used.

    We also set key_dependent to 0 if we found an EQ_REF key, as this an
    optimal key for the table and there is no reason to check more keys.

commit 64f24b776dfdb8bcc37cc9d5be022a8af28f76b0
Author: Michael Widenius <monty@mariadb.org>
Date:   Sun May 15 15:46:29 2022 +0300

    greedy_search() and best_extension_by_limited_search() scrambled table order

    best_extension_by_limited_search() assumes that tables should be sorted
    according to size to be able to quickly disregard bad plans. However the
    current usage of swap_variables() will change the table order to a not
    sorted one for the next recursive call. This breaks the assumtion and
    causes performance issues when using many tables (we have to examine
    many more plans).

    This patch fixes this by ensuring that the original table order is kept
    for the not yet used tables when best_extension_by_limited_search() is
    called.

    This was done by always calling swap_variables() for each table and
    restoring the original table order at exit.

    Some test changed:
    - In a majority of the test the change was that two "identical tables"
      where swapped and the optimzer is now using the first/smaller table
    - In few test the table order was changed. The new plan looks identical
      or slighly better than the original.