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%

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.9
    • Fix Version/s: 10.0.11
    • Component/s: None
    • Labels:

      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

            People

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

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration