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

EITS: Incorrect filtered% value for single-table select with range access

    XMLWordPrintable

Details

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

    Description

      filtered% value is incorrect for single-table select and two-keypart key.

      create table ten(a int);
      insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
       
      create table t2 (a int, b int, col1 varchar(64), col2 varchar(64), key(a,b));
      insert into t2 select A.a+10*B.a, C.a+10*D.a, 'filler-data1', 'filler-data2' from ten A, ten B, ten C, ten D;
       
      set histogram_size=100;
      set optimizer_use_condition_selectivity=4;
      set use_stat_tables='preferably';
      analyze table t2 persistent for all;

      Then, try this:

      explain extended select * from t2 where a in (1,2,3) and b in (1,2,3);
      +------+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
      +------+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------+
      |    1 | SIMPLE      | t2    | range | a             | a    | 10      | NULL |    9 |     3.37 | Using index condition |
      +------+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------+

      The entire WHERE clause is used to construct range access. If we use range access, it will give us 9 rows, and after that 'filtered' should be 100%. Instead, it is 3.37%, and again the optimizer will grossly underestimate #rows.

      Attachments

        Activity

          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.