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

EITS: if condition is used for REF access, its selectivity is still in filtered%

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.9
    • 10.0.11
    • None

    Description

      create table ten(a int);
      		 
      insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      		 
      create table t1(key1 int, col1 int, key(key1));
      		 
      insert into t1 select A.a, A.a from ten A, ten B, ten C;
      		 
       
      		 
      set histogram_size=100;
      		 
      set use_stat_tables='preferably';
      		 
      set optimizer_use_condition_selectivity=4;
      		 
      analyze table t1 persistent for all;
      		 

      mysql> explain extended select * from t1 where col1=2;
      		 
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
      		 
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
      		 
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
      		 
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 1000 |     9.90 | Using where |
      		 
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+

      This is ok.

      mysql> explain extended select * from t1 where key1=2;
      		 
      +------+-------------+-------+------+---------------+------+---------+-------+------+----------+-------+
      		 
      | id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
      		 
      +------+-------------+-------+------+---------------+------+---------+-------+------+----------+-------+
      		 
      |    1 | SIMPLE      | t1    | ref  | key1          | key1 | 5       | const |  100 |    10.00 |       |
      		 
      +------+-------------+-------+------+---------------+------+---------+-------+------+----------+-------+

      This is not ok. "key1=2" is used for ref access. However, its selectivity is still present in "filtered%". Ref access is employed when a condition is very selective, counting its selectivity twice will cause gross under-estimation of output cardinality.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          psergei Sergei Petrunia created issue -
          psergei Sergei Petrunia made changes -
          Field Original Value New Value
          Assignee Igor Babaev [ igor ]
          psergei Sergei Petrunia made changes -
          Assignee Igor Babaev [ igor ] Sergei Petrunia [ psergey ]
          psergei Sergei Petrunia made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          psergei Sergei Petrunia made changes -
          Description
          {noformat}
          create table ten(a int);
          insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
          create table t1(key1 int, col1 int, key(key1));
          insert into t1 select A.a, A.a from ten A, ten B, ten C;

          set histogram_size=100;
          set use_stat_tables='preferably';
          set optimizer_use_condition_selectivity=4;
          analyze table t1 persistent for all;
          {noformat}

          {noformat}
          mysql> explain extended select * from t1 where col1=2;
          +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
          | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
          +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
          | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | 9.90 | Using where |
          +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
          {noformat}


          {noformat}
          mysql> explain extended select * from t1 where key1=2;
          +------+-------------+-------+------+---------------+------+---------+-------+------+----------+-------+
          | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
          +------+-------------+-------+------+---------------+------+---------+-------+------+----------+-------+
          | 1 | SIMPLE | t1 | ref | key1 | key1 | 5 | const | 100 | 10.00 | |
          +------+-------------+-------+------+---------------+------+---------+-------+------+----------+-------+
          {noformat}
          		 {noformat}
          create table ten(a int);
          insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
          create table t1(key1 int, col1 int, key(key1));
          insert into t1 select A.a, A.a from ten A, ten B, ten C;

          set histogram_size=100;
          set use_stat_tables='preferably';
          set optimizer_use_condition_selectivity=4;
          analyze table t1 persistent for all;
          {noformat}

          {noformat}
          mysql> explain extended select * from t1 where col1=2;
          +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
          | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
          +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
          | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | 9.90 | Using where |
          +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
          {noformat}

          This is ok.

          {noformat}
          mysql> explain extended select * from t1 where key1=2;
          +------+-------------+-------+------+---------------+------+---------+-------+------+----------+-------+
          | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
          +------+-------------+-------+------+---------------+------+---------+-------+------+----------+-------+
          | 1 | SIMPLE | t1 | ref | key1 | key1 | 5 | const | 100 | 10.00 | |
          +------+-------------+-------+------+---------------+------+---------+-------+------+----------+-------+
          {noformat}

          This is not ok. "key1=2" is used for ref access. However, its selectivity is still present in "filtered%". Ref access is employed when a condition is very selective, counting its selectivity twice will cause gross under-estimation of output cardinality.
          psergei Sergei Petrunia made changes -
          Labels eits
          serg Sergei Golubchik made changes -
          Fix Version/s 10.0.11 [ 15200 ]
          Fix Version/s 10.0.10 [ 14500 ]
          psergei Sergei Petrunia made changes -
          Resolution Fixed [ 1 ]
          Status In Progress [ 3 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow defaullt [ 37802 ] MariaDB v2 [ 43654 ]
          ratzpo Rasmus Johansson (Inactive) made changes -
          Workflow MariaDB v2 [ 43654 ] MariaDB v3 [ 63682 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 63682 ] MariaDB v4 [ 147719 ]

          People

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