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

optimizer_use_condition_selectivity=4 sometimes produces worse plan than optimizer_use_condition_selectivity=1

    XMLWordPrintable

Details

    Description

      When using set optimizer_use_condition_selectivity=1 we get a better query plan than for set optimizer_use_condition_selectivity=[2|3|4|5]

      set optimizer_use_condition_selectivity=1;
      analyze SELECT sysapproval_approver0.`sys_id`
      FROM   ((sysapproval_approver sysapproval_approver0
               INNER JOIN task task1
                       ON sysapproval_approver0.`sysapproval` = task1.`sys_id`
                          AND (( task1.`sys_domain_path` = '/'
                                  OR task1.`sys_domain_path` LIKE '!!!/!!#/!!$/%'
                                  OR task1.`sys_domain_path` LIKE '!!!/!!!/%' )))
              INNER JOIN task task2
                      ON task1.`parent` = task2.`sys_id`
                         AND (( task2.`sys_domain_path` = '/'
                                 OR task2.`sys_domain_path` LIKE '!!!/!!#/!!$/%'
                                 OR task2.`sys_domain_path` LIKE '!!!/!!!/%' )))
      WHERE  task2.`sys_id` LIKE '8e7792a7dbfffb00fff8a345ca961934%'
             AND ( sysapproval_approver0.`sys_domain_path` = '/'
                    OR sysapproval_approver0.`sys_domain_path` LIKE '!!!/!!#/!!$/%'
                    OR sysapproval_approver0.`sys_domain_path` LIKE '!!!/!!!/%' )
      ORDER  BY sysapproval_approver0.`order`
      LIMIT  0, 50 ;
      +------+-------------+-----------------------+-------+------------------------------------------------------+---------------------------+---------+-----------------+------+--------+----------+------------+----------------------------------------------+
      | id   | select_type | table                 | type  | possible_keys                                        | key                       | key_len | ref             | rows | r_rows | filtered | r_filtered | Extra                                        |
      +------+-------------+-----------------------+-------+------------------------------------------------------+---------------------------+---------+-----------------+------+--------+----------+------------+----------------------------------------------+
      |    1 | SIMPLE      | task2                 | range | PRIMARY,sys_class_name_2,sys_domain_path             | PRIMARY                   | 96      | NULL            | 1    | 0.00   |   100.00 |     100.00 | Using where; Using temporary; Using filesort |
      |    1 | SIMPLE      | task1                 | ref   | PRIMARY,sys_class_name_2,sys_domain_path,task_parent | task_parent               | 99      | tt.task2.sys_id | 255  | NULL   |   100.00 |       NULL | Using index condition; Using where           |
      |    1 | SIMPLE      | sysapproval_approver0 | ref   | sysapproval_approver_ref5,sys_domain_path            | sysapproval_approver_ref5 | 99      | tt.task1.sys_id | 176  | NULL   |   100.00 |       NULL | Using index condition; Using where           |
      +------+-------------+-----------------------+-------+------------------------------------------------------+---------------------------+---------+-----------------+------+--------+----------+------------+----------------------------------------------+
      3 rows in set (0.001 sec)
       
      set optimizer_use_condition_selectivity=[2|3|4|5]; -- use 2 or 3 or 4 or 5
       
      +------+-------------+-----------------------+--------+------------------------------------------------------+---------------------------+---------+--------------------------------------+---------+------------+----------+------------+----------------------------------------------------+
      | id   | select_type | table                 | type   | possible_keys                                        | key                       | key_len | ref                                  | rows    | r_rows     | filtered | r_filtered | Extra                                              |
      +------+-------------+-----------------------+--------+------------------------------------------------------+---------------------------+---------+--------------------------------------+---------+------------+----------+------------+----------------------------------------------------+
      |    1 | SIMPLE      | sysapproval_approver0 | range  | sysapproval_approver_ref5,sys_domain_path            | sysapproval_approver_ref5 | 99      | NULL                                 | 1856810 | 1195428.00 |   100.00 |     100.00 | Using index condition; Using where; Using filesort |
      |    1 | SIMPLE      | task1                 | eq_ref | PRIMARY,sys_class_name_2,sys_domain_path,task_parent | PRIMARY                   | 96      | tt.sysapproval_approver0.sysapproval | 1       | 0.00       |   100.00 |     100.00 | Using where                                        |
      |    1 | SIMPLE      | task2                 | eq_ref | PRIMARY,sys_class_name_2,sys_domain_path             | PRIMARY                   | 96      | tt.task1.parent                      | 1       | NULL       |   100.00 |       NULL | Using where                                        |
      +------+-------------+-----------------------+--------+------------------------------------------------------+---------------------------+---------+--------------------------------------+---------+------------+----------+------------+----------------------------------------------------+
      3 rows in set (15.906 sec)
      
      

      A workaround has been discovered:

      set optimizer_switch="extended_keys=off";
      

      extended_keys=off produces a much better query plan, returning results in 0.001 seconds even when set optimizer_use_condition_selectivity=4.

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              edward Edward Stoever
              Votes:
              3 Vote for this issue
              Watchers:
              12 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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