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

A fast plan with ROR index-merge is ignored when 'index_merge_sort_union=off'

    XMLWordPrintable

    Details

      Description

      The problem can be reproduced with the following test case:

      create table t0 (a int);
      insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      insert into t0 select a+10 from t0;
      insert into t0 select a+20 from t0;
      insert into t0 select a+40 from t0;
      insert into t0 select a+80 from t0;
      insert into t0 select a+160 from t0;
      delete from t0 where a > 300;
       
      create table t1 (
        f1 int, f2 int, f3 int, f4 int,
        primary key (f1),  key (f3),  key(f4)
      ) engine=myisam;
      insert into t1 select a+100, a+100, a+100, a+100 from t0;
      insert into t1 VALUES (9,0,2,6), (9930,0,0,NULL);
      analyze table t1;
       
      set optimizer_switch='index_merge_sort_union=off';
      set optimizer_switch='index_merge_union=on';
       
      explain 
      select * from t1 
         where (( f3 = 1 or f1 = 7 )  and f1 < 10) or
                    (f3 between 2 and 2 and ( f3 = 1 or f4 < 7 ));
       
      insert into t1 values (52,0,1,0),(53,0,1,0);
      insert into t1 values (50,0,1,0),(51,0,1,0);
      insert into t1 values (48,0,1,0),(49,0,1,0);
      insert into t1 values (46,0,1,0),(47,0,1,0);
      insert into t1 values (44,0,1,0),(45,0,1,0);
      analyze table t1;
       
      explain 
      select * from t1 
         where (( f3 = 1 or f1 = 7 )  and f1 < 10) or
                    (f3 between 2 and 2 and ( f3 = 1 or f4 < 7 ));
      

      The first explain shows that a ROR union index merge has been chosen that would select 3 rows.

      MariaDB [test]> explain 
          -> select * from t1 
          ->    where (( f3 = 1 or f1 = 7 )  and f1 < 10) or
          ->               (f3 between 2 and 2 and ( f3 = 1 or f4 < 7 ));
      +------+-------------+-------+-------------+---------------+---------------+---------+------+------+-----------------------------------------+
      | id   | select_type | table | type        | possible_keys | key           | key_len | ref  | rows | Extra                                   |
      +------+-------------+-------+-------------+---------------+---------------+---------+------+------+-----------------------------------------+
      |    1 | SIMPLE      | t1    | index_merge | PRIMARY,f3,f4 | f3,PRIMARY,f3 | 5,4,5   | NULL |    3 | Using union(f3,PRIMARY,f3); Using where |
      +------+-------------+-------+-------------+---------------+---------------+---------+------+------+-----------------------------------------+
      

      The second explain shows that a table scan was used to access all 309 rows of table t1 though a plan with a ROR union index merge that would access less than 15 rows still could be used.

      MariaDB [test]> explain 
          -> select * from t1 
          ->    where (( f3 = 1 or f1 = 7 )  and f1 < 10) or
          ->               (f3 between 2 and 2 and ( f3 = 1 or f4 < 7 ));
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      |    1 | SIMPLE      | t1    | ALL  | PRIMARY,f3,f4 | NULL | NULL    | NULL |  313 | Using where |
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              igor Igor Babaev
              Reporter:
              igor Igor Babaev
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: