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

EITS: bad estimate for very skewed distributions

    XMLWordPrintable

    Details

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

      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

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: