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

Suboptimal execution plan (ref vs eq_ref) with 5x difference chosen with optimizer_use_condition_selectivity>=3 on query with JOINs, AND conditions

Details

    Description

      Without EITS/optimizer_use_codition_selectivity, the query in the test case below sometimes is executed with eq_ref, and sometimes with ref. But with EITS/optimizer_use_codition_selectivity>=3, it seems always ref.

      Execution with ref takes approximately 5 times longer. 

      ANALYZE TABLE L, K, J;
      		 
      +--------+---------+----------+-----------------------------+
      		 
      | Table  | Op      | Msg_type | Msg_text                    |
      		 
      +--------+---------+----------+-----------------------------+
      		 
      | test.L | analyze | status   | OK                          |
      		 
      | test.K | analyze | status   | Table is already up to date |
      		 
      | test.J | analyze | status   | OK                          |
      		 
      +--------+---------+----------+-----------------------------+
      		 

      EXPLAIN EXTENDED SELECT  alias1 . `col_varchar_10_latin1` AS field1 , alias2 . `col_varchar_1024_latin1_key` AS field2 FROM  L AS alias1  LEFT  JOIN  K AS alias2  LEFT  JOIN J AS alias3 ON  alias2 . `col_varchar_10_latin1_key` =  alias3 . `col_varchar_1024_utf8_key`  ON  alias1 . `col_int` =  alias3 . `pk`  WHERE  alias3 . `col_varchar_1024_utf8` IS NOT NULL AND  alias2 . `col_varchar_1024_utf8` > 'v' AND alias2 . `col_varchar_1024_utf8` < 'z';
      		 
      +------+-------------+--------+--------+-----------------------------------+---------+---------+---------------------+------+----------+-------------------------------------------------+
      		 
      | id   | select_type | table  | type   | possible_keys                     | key     | key_len | ref                 | rows | filtered | Extra                                           |
      		 
      +------+-------------+--------+--------+-----------------------------------+---------+---------+---------------------+------+----------+-------------------------------------------------+
      		 
      |    1 | SIMPLE      | alias1 | ALL    | NULL                              | NULL    | NULL    | NULL                | 2927 |   100.00 | Using where                                     |
      		 
      |    1 | SIMPLE      | alias3 | eq_ref | PRIMARY,col_varchar_1024_utf8_key | PRIMARY | 4       | test.alias1.col_int |    1 |   100.00 | Using where                                     |
      		 
      |    1 | SIMPLE      | alias2 | ALL    | NULL                              | NULL    | NULL    | NULL                | 3000 |   100.00 | Using where; Using join buffer (flat, BNL join) |
      		 
      +------+-------------+--------+--------+-----------------------------------+---------+---------+---------------------+------+----------+-------------------------------------------------+
      		 

      SELECT  alias1 . `col_varchar_10_latin1` AS field1 , alias2 . `col_varchar_1024_latin1_key` AS field2 FROM  L AS alias1  LEFT  JOIN  K AS alias2  LEFT  JOIN J AS alias3 ON  alias2 . `col_varchar_10_latin1_key` =  alias3 . `col_varchar_1024_utf8_key`  ON  alias1 . `col_int` =  alias3 . `pk`  WHERE  alias3 . `col_varchar_1024_utf8` IS NOT NULL AND  alias2 . `col_varchar_1024_utf8` > 'v' AND alias2 . `col_varchar_1024_utf8` < 'z';
      		 
      +------------+----------
      		 
      | field1     | field2
      		 
      +------------+----------
      		 
      ...
      		 
      +------------+----------
      		 
      1034 rows in set (0.11 sec)
      		 

      SET use_stat_tables = PREFERABLY, optimizer_use_condition_selectivity = 3;
      		 
      ANALYZE TABLE L, K, J;
      		 
      +--------+---------+----------+-----------------------------------------+
      		 
      | Table  | Op      | Msg_type | Msg_text                                |
      		 
      +--------+---------+----------+-----------------------------------------+
      		 
      | test.L | analyze | status   | Engine-independent statistics collected |
      		 
      | test.L | analyze | status   | OK                                      |
      		 
      | test.K | analyze | status   | Engine-independent statistics collected |
      		 
      | test.K | analyze | status   | Table is already up to date             |
      		 
      | test.J | analyze | status   | Engine-independent statistics collected |
      		 
      | test.J | analyze | status   | OK                                      |
      		 
      +--------+---------+----------+-----------------------------------------+
      		 

      EXPLAIN EXTENDED SELECT  alias1 . `col_varchar_10_latin1` AS field1 , alias2 . `col_varchar_1024_latin1_key` AS field2 FROM  L AS alias1  LEFT  JOIN  K AS alias2  LEFT  JOIN J AS alias3 ON  alias2 . `col_varchar_10_latin1_key` =  alias3 . `col_varchar_1024_utf8_key`  ON  alias1 . `col_int` =  alias3 . `pk`  WHERE  alias3 . `col_varchar_1024_utf8` IS NOT NULL AND  alias2 . `col_varchar_1024_utf8` > 'v' AND alias2 . `col_varchar_1024_utf8` < 'z';
      		 
      +------+-------------+--------+------+-----------------------------------+---------------------------+---------+------+------+----------+-------------------------------------------------+
      		 
      | id   | select_type | table  | type | possible_keys                     | key                       | key_len | ref  | rows | filtered | Extra                                           |
      		 
      +------+-------------+--------+------+-----------------------------------+---------------------------+---------+------+------+----------+-------------------------------------------------+
      		 
      |    1 | SIMPLE      | alias2 | ALL  | NULL                              | NULL                      | NULL    | NULL | 3000 |    16.00 | Using where                                     |
      		 
      |    1 | SIMPLE      | alias3 | ref  | PRIMARY,col_varchar_1024_utf8_key | col_varchar_1024_utf8_key | 768     | func |    1 |   100.00 | Using where                                     |
      		 
      |    1 | SIMPLE      | alias1 | ALL  | NULL                              | NULL                      | NULL    | NULL | 3000 |   100.00 | Using where; Using join buffer (flat, BNL join) |
      		 
      +------+-------------+--------+------+-----------------------------------+---------------------------+---------+------+------+----------+-------------------------------------------------+
      		 

      SELECT  alias1 . `col_varchar_10_latin1` AS field1 , alias2 . `col_varchar_1024_latin1_key` AS field2 FROM  L AS alias1  LEFT  JOIN  K AS alias2  LEFT  JOIN J AS alias3 ON  alias2 . `col_varchar_10_latin1_key` =  alias3 . `col_varchar_1024_utf8_key`  ON  alias1 . `col_int` =  alias3 . `pk`  WHERE  alias3 . `col_varchar_1024_utf8` IS NOT NULL AND  alias2 . `col_varchar_1024_utf8` > 'v' AND alias2 . `col_varchar_1024_utf8` < 'z';
      		 
      +------------+----------
      		 
      | field1     | field2
      		 
      +------------+----------
      		 
      ...
      		 
      +------------+----------
      		 
      1034 rows in set (0.66 sec)

      Execution times above are from 10.1 commit d161546b67142cdd5322a4ed160441045ae0cd1e. Also reproducible on 10.0 and on debug builds – execution time is different there, but the approximate ratio holds.

      The data dump is attached.

      Attachments

        1. dump.gz
          420 kB
          Elena Stepanova

        Activity

          Ascending order - Click to sort in descending order
          elenst Elena Stepanova added a comment -

          I've set it to minor because the scenario is not necessarily common (data types and all). Feel free to adjust it if needed.

          elenst Elena Stepanova added a comment - I've set it to minor because the scenario is not necessarily common (data types and all). Feel free to adjust it if needed.
          igor Igor Babaev (Inactive) added a comment -

          If I create an index on K(col_varchar_1024_utf8) the optimizer chooses the slow plan with the default value of optimizer_use_condition_selectivity (=1).
          I'll try to figure out in 10.2 why the slow plan is so slow with the help of ANALYZE.

          igor Igor Babaev (Inactive) added a comment - If I create an index on K(col_varchar_1024_utf8) the optimizer chooses the slow plan with the default value of optimizer_use_condition_selectivity (=1). I'll try to figure out in 10.2 why the slow plan is so slow with the help of ANALYZE.

          People

            Unassigned Unassigned
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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