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

Index condition pushdown is not used with ref access of partitioned tables

    XMLWordPrintable

Details

    Description

      Create tables t0,t1,t2 and populate them as follows:

      create table t0 (
      tp int, a int, b int, c varchar(12), index idx (a,b)
      ) engine=myisam;
      insert into t0 values
      (1,3,30,'yyzy'), (1,3,30,'yxxyy'), (1,3,30,'yyxy'), (1,3,30,'xxyy'),
      (1,3,30,'yyxz'), (1,3,30,'yyzz'), (1,3,30,'xxyzy'), (1,3,30,'yyyy'),
      (1,3,30,'yzzy'), (1,93,30,'zzzy'),
      (2,3,40,'yxx'), (2,4,40,'xx'), (2,3,10,'zxz'),
      (2,3,40,'yyx'), (2,4,40,'xxx'), (2,3,10,'zyyz'),
      (2,3,40,'xzzzz'), (2,4,40,'yyyxx'), (2,3,10,'zyz'),
      (2,3,40,'xxx'), (2,4,40,'xx'), (2,3,10,'zzz'),
      (2,3,40,'yyxzx'), (2,4,40,'xyx'), (2,3,10,'xzz'),
      (2,3,40,'xxxzz'), (2,4,40,'xxz'), (2,3,10,'zzzy'),
      (2,3,40,'zzxxx'), (2,4,40,'zxx'), (2,3,10,'yzzz'),
      (2,3,40,'xyyxx'), (2,4,40,'xzzzx'), (2,3,10,'zzxxz'),
      (2,3,40,'yzxxx'), (2,4,40,'xxzy'), (2,3,10,'zzzyx'),
      (2,93,40,'xzx'), (2,94,40,'xz'), (2,93,10,'zyyyz'),
      (3,4,30,'yx'), (3,4,30,'yyxxx'), (3,4,30,'zzyy'), (3,4,30,'zxyy'),
      (3,4,30,'xxyy'), (3,4,30,'yyzx'),  (3,4,30,'zyyy'), (3,4,30,'yzy'),
      (3,4,30,'zzzyy'), (3,94,30,'yyz');
       
      create table t1 (
      tp int, a int, b int, c varchar(12), index idx (a,b)
      ) engine=myisam
      partition by list (tp)
      ( partition p1 values in (1),
        partition p2 values in (2),
        partition p3 values in (3));
      insert into t1 select * from t0;
       
      create table t2 (a int, index idx(a)) engine=myisam;
      insert into t2 values (1), (2), (3), (4), (5);
      insert into t2 select a+10 from t2;
      insert into t2 select a+20 from t2;
       
      analyze table t0,t1,t2;
      

      Now we can see that for the query

      select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4;
      

      the optimizer employs index condition pushdown:

      MariaDB [test]> explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4;
      +------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+--------------------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref       | rows | filtered | Extra                    |
      +------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+--------------------------+
      |    1 | SIMPLE      | t2    | range | idx           | idx  | 5       | NULL      |    2 |   100.00 | Using where; Using index |
      |    1 | SIMPLE      | t0    | ref   | idx           | idx  | 5       | test.t2.a |   13 |   100.00 | Using index condition    |
      +------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+--------------------------+
      

      while for the similar query:

      select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4;
      

      it doesn't

      MariaDB [test]> explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4;
      +------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+--------------------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref       | rows | filtered | Extra                    |
      +------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+--------------------------+
      |    1 | SIMPLE      | t2    | range | idx           | idx  | 5       | NULL      |    2 |   100.00 | Using where; Using index |
      |    1 | SIMPLE      | t1    | ref   | idx           | idx  | 5       | test.t2.a |    5 |   100.00 | Using where              |
      +------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+--------------------------+
      

      though tables t0 and t1 are accessed by ref using index on (a,b).

      The tables t0 and t1 are of the same structure and populated with the same set of rows. The only difference is that t1 is partitioned.

      Setting a breakpoint in handler_index_cond_check shows that execution of the second query really is done without index condition pushdown.

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              igor Igor Babaev
              Votes:
              1 Vote for this issue
              Watchers:
              2 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.