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

Optimizer chooses wrong access access method with LooseScan semi-join strategy

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Not a Bug
    • Affects Version/s: 10.4
    • Fix Version/s: N/A
    • Component/s: Optimizer
    • Labels:
      None

      Description

      The optimizer may choose poor (expensive) access methods to be used together with LooseScan semi-join strategy.

      The original example was observed at a customer, and we don't have access to the dataset.

      I've also found this example in subselect3.result. I'm not sure if it is the same issue, but this gives an example to study:

      create table t0(a int);
      insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      create table t1 (kp1 int, kp2 int, c int, filler char(100), key(kp1, kp2));
      insert into t1 select A.a+10*(B.a+10*C.a), 0, 0, 'filler' from t0 A, t0 B, t0 C;
      insert into t1 select * from t1 where kp1 < 20;
      create table t3 (a int);
      insert into t3 select A.a + 10*B.a from t0 A, t0 B;
      

      explain select * from t3 where a in (select kp1 from t1 where kp1<20);
      +------+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                           |
      +------+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+
      |    1 | PRIMARY     | t1    | index | kp1           | kp1  | 10      | NULL | 1020 | Using where; Using index; LooseScan             |
      |    1 | PRIMARY     | t3    | ALL   | NULL          | NULL | NULL    | NULL | 100  | Using where; Using join buffer (flat, BNL join) |
      +------+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+
      

      Why does LooseScan use type=index? There is a possible range access on index kp1. It is certainly better than the full table scan.

      Running the subquery standalone uses the select:

      mysql> explain select kp1 from t1 where kp1<20;
      +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
      +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
      |    1 | SIMPLE      | t1    | range | kp1           | kp1  | 5       | NULL | 40   | Using where; Using index |
      +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                psergey Sergei Petrunia
                Reporter:
                psergey Sergei Petrunia
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: