Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-28852

Improve optimization of joins with many tables, including eq_ref tables

    XMLWordPrintable

    Details

      Description

      This is a followup to MDEV-28073.

      Problem description

      Join optimizer can be very slow when the join has many tables and join prefix pruning feature doesn't manage to prune join prefixes.

      An important special case is when a subset of join tables are joined with an equi-join condition on a primary key, and so can be joined using eq_ref in any order. Then, query plans that use different permutations of these tables have the same cost. The optimizer will consider all possible permutations. This can take a lot of CPU but provide little benefit.

      The fix for MDEV-28073 addresses these issues to some extent and was pushed into 10.6. This MDEV is about more comprehensive fix.

      Solution

      TODO description. Copy from:
      https://jira.mariadb.org/browse/MDEV-28073?focusedCommentId=225701&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-225701

      Join prefix pruning is now controlled by @@optimizer_prune_level variable. The new pruning is done when @@optimizer_prune_level=2, which is the new default. One can disable certain aspects (TODO elaborate) of the new behavior by setting @@optimizer_prune_level=1 (the default before 10.10).

      The tree

      It is preview-10.10-MDEV-28852-join-pruning

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.