MySQL Bugs: #53806: Wrong estimates for range query in partitioned MyISAM table

Bug #53806	Wrong estimates for range query in partitioned MyISAM table
Submitted:	19 May 2010 14:00	Modified:	6 Oct 2010 12:35
Reporter:	Øystein Grøvlen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1.46, mysql-next-mr	OS:	Any
Assigned to:	Mattias Jonsson	CPU Architecture:	Any

Description:
If I have a test file with just the following query (in addition to
create/insert):

EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;

I get the following with seemingly correct row estimate in EXPLAIN:

EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
id    select_type    table    partitions    type    possible_keys    key    key_len    ref    rows Extra
1    SIMPLE    t1    p0,p1    index    PRIMARY    PRIMARY    4    NULL    3    Using where; Using index

If I add another query before this, I get a different result:

EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 7;
id    select_type    table    partitions    type    possible_keys    key    key_len    ref rows    Extra
1    SIMPLE    t1    p0,p1,p2,p3,p4,p5,max    range    PRIMARY    PRIMARY    4    NULL    9    Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
id    select_type    table    partitions    type    possible_keys    key    key_len    ref rows    Extra
1    SIMPLE    t1    p0,p1    index    PRIMARY    PRIMARY    4    NULL    10    Using where; Using index

And it gets even stranger.  The estimate seem to be dependent on the estimate
of the previous query. E.g,:

EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 5;
id    select_type    table    partitions    type    possible_keys    key    key_len    ref rows    Extra
1    SIMPLE    t1    p0,p1,p2,p3,p4    index    PRIMARY    PRIMARY    4    NULL    6    Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
id    select_type    table    partitions    type    possible_keys    key    key_len    ref rows    Extra
1    SIMPLE    t1    p0,p1    index    PRIMARY    PRIMARY    4    NULL    6    Using where; Using index

These queries are part of the partition_pruning test.  From looking at the
result file, correct estimates are given for '<' operator while '<=', '>', and '>=' give incorrect values.

How to repeat:
-- source include/have_partition.inc

CREATE TABLE t1 (a INT PRIMARY KEY)
PARTITION BY RANGE (a) (
PARTITION p0 VALUES LESS THAN (1),
PARTITION p1 VALUES LESS THAN (2),
PARTITION p2 VALUES LESS THAN (3),
PARTITION p3 VALUES LESS THAN (4),
PARTITION p4 VALUES LESS THAN (5),
PARTITION p5 VALUES LESS THAN (6),
PARTITION max VALUES LESS THAN MAXVALUE)
;

INSERT INTO t1 VALUES (-1),(0),(1),(2),(3),(4),(5),(6),(7),(8);

EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 7;
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;

DROP TABLE t1;

Thank you for the problem report. What storage engine is used for the t1 table? Is it InnoDB?

Since, I did not specify any storage engine in my script, it must have been MyIsam.

OK, just wanted to clarify as I've got different estimations with InnoDB table. Verified also with 5.1.46:

mysql> CREATE TABLE t1 (a INT PRIMARY KEY) engine=MyISAM
    -> PARTITION BY RANGE (a) (
    -> PARTITION p0 VALUES LESS THAN (1),
    -> PARTITION p1 VALUES LESS THAN (2),
    -> PARTITION p2 VALUES LESS THAN (3),
    -> PARTITION p3 VALUES LESS THAN (4),
    -> PARTITION p4 VALUES LESS THAN (5),
    -> PARTITION p5 VALUES LESS THAN (6),
    -> PARTITION max VALUES LESS THAN MAXVALUE)
    -> ;
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO t1 VALUES (-1),(0),(1),(2),(3),(4),(5),(6),(7),(8);
Query OK, 10 rows affected (0.03 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
+----+-------------+-------+------------+-------+---------------+---------+-----
----+------+------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_
len | ref  | rows | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+-----
----+------+------+--------------------------+
|  1 | SIMPLE      | t1    | p0,p1      | index | PRIMARY       | PRIMARY | 4
    | NULL |    3 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+-----
----+------+------+--------------------------+
1 row in set (0.02 sec)

mysql> EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 7;
+----+-------------+-------+-----------------------+-------+---------------+----
-----+---------+------+------+--------------------------+
| id | select_type | table | partitions            | type  | possible_keys | key
     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-----------------------+-------+---------------+----
-----+---------+------+------+--------------------------+
|  1 | SIMPLE      | t1    | p0,p1,p2,p3,p4,p5,max | range | PRIMARY       | PRI
MARY | 4       | NULL |    9 | Using where; Using index |
+----+-------------+-------+-----------------------+-------+---------------+----
-----+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
+----+-------------+-------+------------+-------+---------------+---------+-----
----+------+------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_
len | ref  | rows | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+-----
----+------+------+--------------------------+
|  1 | SIMPLE      | t1    | p0,p1      | index | PRIMARY       | PRIMARY | 4
    | NULL |   10 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+-----
----+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 5;
+----+-------------+-------+----------------+-------+---------------+---------+-
--------+------+------+--------------------------+
| id | select_type | table | partitions     | type  | possible_keys | key     |
key_len | ref  | rows | Extra                    |
+----+-------------+-------+----------------+-------+---------------+---------+-
--------+------+------+--------------------------+
|  1 | SIMPLE      | t1    | p0,p1,p2,p3,p4 | index | PRIMARY       | PRIMARY |
4       | NULL |   10 | Using where; Using index |
+----+-------------+-------+----------------+-------+---------------+---------+-
--------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
+----+-------------+-------+------------+-------+---------------+---------+-----
----+------+------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_
len | ref  | rows | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+-----
----+------+------+--------------------------+
|  1 | SIMPLE      | t1    | p0,p1      | index | PRIMARY       | PRIMARY | 4
    | NULL |   10 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+-----
----+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 2;
+----+-------------+-------+------------+-------+---------------+---------+-----
----+------+------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_
len | ref  | rows | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+-----
----+------+------+--------------------------+
|  1 | SIMPLE      | t1    | p0,p1      | index | PRIMARY       | PRIMARY | 4
    | NULL |   10 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+-----
----+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.1.46-community |
+------------------+
1 row in set (0.00 sec)

Seems like the optimizer is using ::records in a wrong way, this seems to fix it (also fixes bug#46754):
=== modified file 'sql/sql_select.cc'
--- sql/sql_select.cc	2010-08-13 08:07:39 +0000
+++ sql/sql_select.cc	2010-08-25 11:23:28 +0000
@@ -16640,7 +16640,15 @@
         if (tab->select && tab->select->quick)
           examined_rows= tab->select->quick->records;
         else if (tab->type == JT_NEXT || tab->type == JT_ALL)
-          examined_rows= tab->limit ? tab->limit : tab->table->file->records();
+        {
+          if (tab->limit)
+            examined_rows= tab->limit;
+          else
+          {
+            tab->table->file->info(HA_STATUS_VARIABLE);
+            examined_rows= tab->table->file->stats.records;
+          }
+        }
         else
           examined_rows=(ha_rows)join->best_positions[i].records_read;

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/116908

3489 Mattias Jonsson	2010-08-26
      Bug#53806: Wrong estimates for range query in partitioned MyISAM table
      Bug#46754: 'rows' field doesn't reflect partition pruning
      
      The EXPLAIN's result in 'rows' field
      was evaluated to number of rows when the table was opened
      (not from the table cache) and only the partitions left
      after pruning was updated with its correct number
      of rows.
      
      The evaluation of the 'rows' field was using handler::records()
      which is a potentially expensive call, and ignores the partitioning
      pruning.
      
      The fix was to use the handlers stats.records after updating it
      with ::info(HA_STATUS_VARIABLE) instead.
     @ mysql-test/r/partition_pruning.result
        updated result
     @ mysql-test/t/partition_pruning.test
        Added test.
     @ sql/sql_select.cc
        Use ::info + stats.records instead of ::records().

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/116963

3490 Mattias Jonsson	2010-08-27
      Bug#53806: Wrong estimates for range query in partitioned MyISAM table
      Bug#46754: 'rows' field doesn't reflect partition pruning
        
      Update of test results after fixing the above bugs.
      (fix in separate commit).
     @ mysql-test/r/partition.result
        Updated test result after fixing bugs 46754 and 53806
     @ mysql-test/r/partition_hash.result
        Updated test result after fixing bugs 46754 and 53806
     @ mysql-test/r/partition_innodb.result
        Updated test result after fixing bugs 46754 and 53806
     @ mysql-test/r/partition_range.result
        Updated test result after fixing bugs 46754 and 53806
     @ mysql-test/suite/parts/r/partition_alter3_innodb.result
        Updated test result after fixing bugs 46754 and 53806
     @ mysql-test/suite/parts/r/partition_alter3_myisam.result
        Updated test result after fixing bugs 46754 and 53806

Approved.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/118153

3204 Mattias Jonsson	2010-09-14 [merge]
      post push patch, fixing test result for bug#53806/46754.

pushed into mysql-5.1-bugteam and mysql-5.5-merge

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100928153607-tdsxkdm5cmuym5sq) (version source revid:alik@sun.com-20100928153508-0saa6v93dinqx1u7) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alik@sun.com-20100928153646-pqp8o1a92mxtuj3h) (version source revid:alik@sun.com-20100928153532-lr3gtvnyp2en4y75) (pib:21)

Pushed into mysql-5.5 5.5.7-rc (revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (version source revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (merge vers: 5.5.7-rc) (pib:21)

Documented bugfix in the 5.5.7 and 5.6.1 changelogs, as follows:

        EXPLAIN PARTITIONS returned bad estimates for range queries on
        partitioned MyISAM tables. In addition, values in the rows
        column of EXPLAIN PARTITIONS output did not take partition
        pruning into account.

Set NM status, waiting for merge to 5.1.

Returned to NM status, still waiting for 5.1 merge.

Also documented in the 5.1.52 changelog. Closed.

Pushed into mysql-5.1 5.1.53 (revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (version source revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (merge vers: 5.1.53) (pib:21)