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

Index condition pushdown for a simple condition over index fields is not used for ref access of partitioned tables when employing BKA

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.3.20, 10.4.10, 10.5.0
    • Fix Version/s: 10.3.23, 10.4.13
    • Component/s: Optimizer
    • Labels:
      None

      Description

      Create and populate tables t0,t1,t2 as in MDEV-21625.
      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.

      Execute the commands

      set join_cache_level=6;
      set optimizer_switch='mrr=on';
      

      You'll 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;
      

      index condition pushdown is empoyed when joining table t0

      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; Using join buffer (flat, BKA join); Rowid-ordered scan |
      +------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+-------------------------------------------------------------------------------+
      

      while for the similar query that uses tables t1,t2

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

      no index condition pushdown is employed

      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; Using join buffer (flat, BKA join); Rowid-ordered scan |
      +------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+---------------------------------------------------------------------+
      

      Setting a breakpoint in handler_index_cond_check confirms the above.

      Interesting that if to make the pushable condition in the second query dependent on table t2

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

      then the optimizer chooses using index condition pushdown

      MariaDB [test]> explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1;
      +------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+------------------------------------------------------------------------------------+
      | 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 index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan |
      +------+-------------+-------+-------+---------------+------+---------+-----------+------+----------+------------------------------------------------------------------------------------+
      

      though the execution of the query is incorrect due to the bug MDEV-21554.

        Attachments

          Activity

            People

            Assignee:
            psergey Sergei Petrunia
            Reporter:
            igor Igor Babaev
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: