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

              varun Varun Gupta (Inactive)
              varun Varun Gupta (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.