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

Performance degradation (suboptimal execution plan) on a query with expected range access

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • N/A
    • 10.1.1
    • Optimizer
    • None

    Description

      Observed on 10.0-mdev6384 development tree (10.0 with the patch for MDEV-6384 applied manually)
      Query:

       SELECT `pk` , MAX( `col_bigint_key` ) FROM `table10000_innodb_int_autoinc`  WHERE ( `col_smallint_key`  IN ( 255 , 255 ) OR  ( `pk` = 144 ) ) AND ( `col_bigint_key`  IN ( 3 , 155 ) AND `col_bigint_key`  IN ( 255 , 8 , 0 ) ) AND ( `col_bigint_key` IS  NULL OR  ( `col_bigint_key` <> 1 ) ) OR ( `col_smallint_key` IS  NULL AND `pk`  BETWEEN 121 AND 4 + 255 ) GROUP BY 1;

      Table:

      CREATE TABLE `table10000_innodb_int_autoinc` (
        `col_smallint_key` smallint(6) DEFAULT NULL,
        `col_bigint_key` bigint(20) DEFAULT NULL,
        `col_varchar_64_key` varchar(64) DEFAULT NULL,
        `col_varchar_10` varchar(10) DEFAULT NULL,
        `col_varchar_10_key` varchar(10) DEFAULT NULL,
        `col_varchar_64` varchar(64) DEFAULT NULL,
        `col_bigint` bigint(20) DEFAULT NULL,
        `pk` int(11) NOT NULL AUTO_INCREMENT,
        `col_smallint` smallint(6) DEFAULT NULL,
        PRIMARY KEY (`pk`),
        KEY `col_smallint_key` (`col_smallint_key`),
        KEY `col_bigint_key` (`col_bigint_key`),
        KEY `col_varchar_64_key` (`col_varchar_64_key`),
        KEY `col_varchar_10_key` (`col_varchar_10_key`)
      ) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=latin1

      (data dump is attached)

      10.0

      +------+-------------+-------------------------------+-------+-----------------------------------------+------------------+---------+------+------+----------+---------------------------------------------------------------------+
      | id   | select_type | table                         | type  | possible_keys                           | key              | key_len | ref  | rows | filtered | Extra                                                               |
      +------+-------------+-------------------------------+-------+-----------------------------------------+------------------+---------+------+------+----------+---------------------------------------------------------------------+
      |    1 | SIMPLE      | table10000_innodb_int_autoinc | range | PRIMARY,col_smallint_key,col_bigint_key | col_smallint_key | 7       | NULL |   21 |   100.00 | Using index condition; Using where; Using temporary; Using filesort |
      +------+-------------+-------------------------------+-------+-----------------------------------------+------------------+---------+------+------+----------+---------------------------------------------------------------------+

      Execution:
      21 rows in set (0.00 sec)

      +----------------------------+-------+
      | Variable_name              | Value |
      +----------------------------+-------+
      | Handler_commit             | 1     |
      | Handler_delete             | 0     |
      | Handler_discover           | 0     |
      | Handler_external_lock      | 0     |
      | Handler_icp_attempts       | 21    |
      | Handler_icp_match          | 21    |
      | Handler_mrr_init           | 0     |
      | Handler_mrr_key_refills    | 0     |
      | Handler_mrr_rowid_refills  | 0     |
      | Handler_prepare            | 0     |
      | Handler_read_first         | 0     |
      | Handler_read_key           | 22    |
      | Handler_read_last          | 0     |
      | Handler_read_next          | 21    |
      | Handler_read_prev          | 0     |
      | Handler_read_rnd           | 21    |
      | Handler_read_rnd_deleted   | 0     |
      | Handler_read_rnd_next      | 22    |
      | Handler_rollback           | 0     |
      | Handler_savepoint          | 0     |
      | Handler_savepoint_rollback | 0     |
      | Handler_tmp_update         | 0     |
      | Handler_tmp_write          | 21    |
      | Handler_update             | 0     |
      | Handler_write              | 0     |
      +----------------------------+-------+

      10.0-mdev6384

      -----------

      +------+-------------+-------------------------------+-------+-----------------------------------------+---------+---------+------+-------+----------+-------------+
      | id   | select_type | table                         | type  | possible_keys                           | key     | key_len | ref  | rows  | filtered | Extra       |
      +------+-------------+-------------------------------+-------+-----------------------------------------+---------+---------+------+-------+----------+-------------+
      |    1 | SIMPLE      | table10000_innodb_int_autoinc | index | PRIMARY,col_smallint_key,col_bigint_key | PRIMARY | 4       | NULL | 10000 |     0.21 | Using where |
      +------+-------------+-------------------------------+-------+-----------------------------------------+---------+---------+------+-------+----------+-------------+
      1 row in set, 1 warning (0.00 sec)

      Execution:
      21 rows in set (0.06 sec)

      +----------------------------+-------+
      | Variable_name              | Value |
      +----------------------------+-------+
      | Handler_commit             | 1     |
      | Handler_delete             | 0     |
      | Handler_discover           | 0     |
      | Handler_external_lock      | 0     |
      | Handler_icp_attempts       | 0     |
      | Handler_icp_match          | 0     |
      | Handler_mrr_init           | 0     |
      | Handler_mrr_key_refills    | 0     |
      | Handler_mrr_rowid_refills  | 0     |
      | Handler_prepare            | 0     |
      | Handler_read_first         | 1     |
      | Handler_read_key           | 0     |
      | Handler_read_last          | 0     |
      | Handler_read_next          | 10000 |
      | Handler_read_prev          | 0     |
      | Handler_read_rnd           | 0     |
      | Handler_read_rnd_deleted   | 0     |
      | Handler_read_rnd_next      | 0     |
      | Handler_rollback           | 0     |
      | Handler_savepoint          | 0     |
      | Handler_savepoint_rollback | 0     |
      | Handler_tmp_update         | 0     |
      | Handler_tmp_write          | 0     |
      | Handler_update             | 0     |
      | Handler_write              | 0     |
      +----------------------------+-------+

      The effect seems to be stable. Persistent statistics doesn't help.

      Attachments

        1. mdev6384_regression_1.dump
          667 kB
          Elena Stepanova
        2. mdev6384_regression_2.dump
          669 kB
          Elena Stepanova
        3. psergey-mdev6454-attempt3.diff
          13 kB
          Sergei Petrunia
        4. psergey-mdev6454-fix-crash2.diff
          4 kB
          Sergei Petrunia
        5. psergey-mdev6454-prototype1.diff
          13 kB
          Sergei Petrunia

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.