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

optimizer_use_cond_selectivity > 1 can cause slow plans

    XMLWordPrintable

    Details

      Description

      In MariaDB 10.4 we changed the default of optimizer_use_cond_selectivity
      from 1 to to 4 to get better plans for complex queries.

      The definition of the values are (from mysqld --help):

      1 - use selectivity of index backed range
      conditions to calculate the cardinality of a partial join
      if the last joined table is accessed by full table scan
      or an index scan. This is basically how MariaDB and MySQL 5.5 and before
      works.

      2 - use selectivity of index backed range conditions to calculate the
      cardinality of a partial join in any case

      3 - additionally always use selectivity of range conditions that are not backed
      by any index to calculate the cardinality of a partial join

      4 - use histograms to calculate selectivity of range conditions that are not
      backed by any index to calculate the cardinality of a partial join

      5 - additionally use selectivity of certain non-range predicates calculated on
      record samples

      In some case when optimizer_use_cond_selectivity = 2 (or bigger) the optimizer
      calculates the selectivity wrong, which can cause it to select a wrong (slow)
      plan.

      One can find if this is what causes wrong plan by using optimizer_trace
      (https://mariadb.com/kb/en/optimizer-trace-overview/). If you see a cost
      that is DOUBLE_MAX then you have probably hit this bug.

      A temporary workaround, until this problem is fixed, is to set
      optimizer_use_cond_selectivity to 1.

      If you have this problem, please also attach to this Jira entry:

      • The query,
      • Table defintions
      • Optimizer trace

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              monty Michael Widenius
              Reporter:
              monty Michael Widenius
              Votes:
              5 Vote for this issue
              Watchers:
              12 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.