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

EITS: bad estimate for very skewed distributions

Details

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

    Description

      After fix for MDEV-5926, we see a regression for the filtered% column for testcase from MDEV-4350:

      create table t1 (a int);
      		 
      insert into t1 values (1), (1);
      		 
      insert into t1 select * from t1; 
      insert into t1 select * from t1; 
      insert into t1 select * from t1; 
      insert into t1 select * from t1; 
      insert into t1 select * from t1; 
      insert into t1 select * from t1; 
      insert into t1 select * from t1; 
      insert into t1 select * from t1; 
      insert into t1 select * from t1; 
      insert into t1 values (0);
      		 
      set use_stat_tables='preferably';
      		 
      set histogram_size=127;
      		 
      set histogram_type='SINGLE_PREC_HB';
      		 
      analyze table t1;
      		 
      flush table t1;
      		 
      set optimizer_use_condition_selectivity=4;
      		 

      mysql> explain extended select * from t1 where a=0;
      		 
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
      		 
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
      		 
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
      		 
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 1025 |    99.22 | Using where |
      		 
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+

      filtered% used to be 50%, now it's 99.22%. Both estimates are very wrong:

      mysql> select a,count(*) from  t1 group by a;
      		 
      +------+----------+
      		 
      | a    | count(*) |
      		 
      +------+----------+
      		 
      |    0 |        1 |
      		 
      |    1 |     1024 |
      		 
      +------+----------+

      But the new one is even worse than before.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-5926 EITS: Histogram estimates for column=least_possible_value are wrong

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            psergei Sergei Petrunia created issue -
            psergei Sergei Petrunia made changes -
            Field Original Value New Value
            Labels eits
            psergei Sergei Petrunia made changes -
            Description After fix for MDEV-5926, we see a regression for the filtered% column for testcase from MDEV-4350:

            {noformat}
            create table t1 (a int);
            insert into t1 values (1), (1);
            insert into t1 select * from t1;
            insert into t1 select * from t1;
            insert into t1 select * from t1;
            insert into t1 select * from t1;
            insert into t1 select * from t1;
            insert into t1 select * from t1;
            insert into t1 select * from t1;
            insert into t1 select * from t1;
            insert into t1 select * from t1;
            insert into t1 values (0);
            set use_stat_tables='preferably';
            set histogram_size=127;
            set histogram_type='SINGLE_PREC_HB';
            analyze table t1;
            flush table t1;
            set optimizer_use_condition_selectivity=4;
            {noformat}

            {noformat}
            mysql> explain extended select * from t1 where a=0;
            +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
            | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
            +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
            | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1025 | 99.22 | Using where |
            +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
            {noformat}
            		 After fix for MDEV-5926, we see a regression for the filtered% column for testcase from MDEV-4350:

            {noformat}
            create table t1 (a int);
            insert into t1 values (1), (1);
            insert into t1 select * from t1;
            insert into t1 select * from t1;
            insert into t1 select * from t1;
            insert into t1 select * from t1;
            insert into t1 select * from t1;
            insert into t1 select * from t1;
            insert into t1 select * from t1;
            insert into t1 select * from t1;
            insert into t1 select * from t1;
            insert into t1 values (0);
            set use_stat_tables='preferably';
            set histogram_size=127;
            set histogram_type='SINGLE_PREC_HB';
            analyze table t1;
            flush table t1;
            set optimizer_use_condition_selectivity=4;
            {noformat}

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

            filtered% used to be 50%, now it's 99.22%. Both estimates are very wrong:

            {noformat}
            mysql> select a,count(*) from t1 group by a;
            +------+----------+
            | a | count(*) |
            +------+----------+
            | 0 | 1 |
            | 1 | 1024 |
            +------+----------+
            {noformat}

            But the new one is even worse than before.
            psergei Sergei Petrunia made changes -
            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 -
            Fix Version/s 10.0.10 [ 14500 ]
            psergei Sergei Petrunia made changes -
            psergei Sergei Petrunia made changes -
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow defaullt [ 37401 ] MariaDB v2 [ 42805 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Workflow MariaDB v2 [ 42805 ] MariaDB v3 [ 62515 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 62515 ] MariaDB v4 [ 147696 ]

            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.