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

Estimates for range conditions from histograms are way off

    XMLWordPrintable

    Details

      Description

      Here is a simple dataset used:

      CREATE TABLE t1(a INT);
      INSERT INTO t1 SELECT 5 from seq_1_to_99;
      INSERT INTO t1 VALUES (10);
      

      set optimizer_use_condition_selectivity=4;
      set use_stat_tables='preferably';
      set histogram_size=255;
      ANALYZE TABLE t1 PERSISTENT FOR ALL;  # Collect EITS
      

      MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM t1 WHERE a < 5;
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 100  |    98.44 | Using where |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
      1 row in set, 1 warning (0.001 sec)
      

      Well there are no rows with a < 5 and we end up with filtered as 98.44

      MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > 5;
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 100  |    99.22 | Using where |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
      1 row in set, 1 warning (0.002 sec)
      

      Well there is 1 row with a > 5 and we end up with filtered as 99.22 %

        Attachments

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            varun Varun Gupta (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration