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

EITS: Histogram estimates for column=least_possible_value are wrong

    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

      This is based on observations over MDEV-4362 and MDEV-4364.

      Populate the table with 100 rows with a=0, 100 rows with a=1, etc. (the data distribution is perfectly uniform)

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

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

      Not a precise estimate, but it is probably ok.

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

      OK.

      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 | 1000 |   100.00 | Using where |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+

      Boom. The number of rows with a=0 is 10% of the table. Not ok.

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

      -1 is not present at all. Not ok.

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: