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

Estimates are higher for partitions of a table with @@use_stat_tables= PREFERABLY

    XMLWordPrintable

    Details

      Description

      DATASET

      set @@use_stat_tables=PREFERABLY;
      CREATE TABLE t1 (i int, a VARCHAR(1000)  DEFAULT "AAA")
              PARTITION BY RANGE COLUMNS (a)(
          PARTITION p0 VALUES LESS THAN ('g') COMMENT "p0",
          PARTITION p1 VALUES LESS THAN ('m'),
          PARTITION p2 VALUES LESS THAN ('t'),
          PARTITION p3 VALUES LESS THAN ('w'));
       
      ALTER TABLE t1 ADD PARTITION (PARTITION p4 VALUES LESS THAN (MAXVALUE));
       
      INSERT INTO t1 VALUES (1,REPEAT('a',100)),(2,REPEAT('v',200)),(3,REPEAT('r',300)),(4,NULL);
      INSERT INTO t1 VALUES (5,REPEAT('k',500)),(6,'April'),(7,7),(8,""),(9,"M"),(10,DEFAULT);
       
      DELETE FROM t1 where a="";
      DELETE FROM t1 where a=(REPEAT('a',100));
      DELETE FROM t1 where a like "%v";
      ALTER TABLE t1 ANALYZE PARTITION p1; 
      

      Then I run:

      Case 1

      MariaDB [test]> set @@use_stat_tables=PREFERABLY;
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [test]> EXPLAIN PARTITIONS SELECT i,SUBSTRING(a,1,10) FROM t1 where (a>'m');
      +------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
      | id   | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
      +------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
      |    1 | SIMPLE      | t1    | p2,p3,p4   | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where |
      +------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
      

      Case 2

       
      MariaDB [test]> set @@use_stat_tables=NEVER;
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [test]> EXPLAIN PARTITIONS SELECT i,SUBSTRING(a,1,10) FROM t1 where (a>'m');
      +------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
      | id   | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
      +------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
      |    1 | SIMPLE      | t1    | p2,p3,p4   | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
      +------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
      1 row in set (0.001 sec)
      

      The rows in case 1 return all records of the table, that is 7 in this case
      while rows in case 2 return records that belong to the partitions p2,,p3 and p4.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              varun Varun Gupta
              Reporter:
              varun Varun Gupta
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: