Bug #70001 Partitioned tables do not use ICP - severe performance loss after partitioning

Description:
mysql> explain SELECT cust_id AS expr$0,MAX(odometer_reading - issued_odometer) AS expr_3466088376 FROM cr AS `car_read`  JOIN cc AS `cust_car` ON( car_read.car_id = cust_car.car_id AND read_datetime between assigned_datetime AND unassigned_datetime ) \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cust_car
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 25944
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: car_read
         type: ref
possible_keys: idx_car_read_datetime
          key: idx_car_read_datetime
      key_len: 4
          ref: test.cust_car.car_id
         rows: 168
        Extra: Using index condition
2 rows in set (0.00 sec)

mysql> explain SELECT cust_id AS expr$0,MAX(odometer_reading - issued_odometer) AS expr_3466088376 FROM car_read AS `car_read`  JOIN cust_car AS `cust_car` ON( car_read.car_id = cust_car.car_id AND read_datetime between assigned_datetime AND unassigned_datetime ) \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cust_car
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 25554
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: car_read
         type: ref
possible_keys: idx_car_read_datetime
          key: idx_car_read_datetime
      key_len: 4
          ref: test.cust_car.car_id
         rows: 13
        Extra: Using where
2 rows in set (0.00 sec)

How to repeat:
see above.  data will be attached.

Suggested fix:
Use the same query plans for partitioned tables as regular tables.

see support/incoming/70001.sql

Hello Justin,

Thank you for the bug report and the test case. 
Verified as described.

Thanks,
Umesh

Fixed in 5.7. Documented in the 5.7.3 changelog as follows:

      Index condition pushdown did not work with partitioned tables.

Closed.

This issue is till present on 5.7.17
Any estimations on when it will be fixed?

Rimantas,

Can you, please, upload a test case that shows the problem with 5.7.17? In my primitive test everything works as expected:

mysql> show create table cr\G
*************************** 1. row ***************************
       Table: cr
Create Table: CREATE TABLE `cr` (
  `id` int(11) NOT NULL,
  `r1` int(11) DEFAULT NULL,
  `r2` int(11) DEFAULT NULL,
  `dt1` datetime DEFAULT NULL,
  `dt2` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table cc\G
*************************** 1. row ***************************
       Table: cc
Create Table: CREATE TABLE `cc` (
  `id` int(11) NOT NULL,
  `cr_id` int(11) DEFAULT NULL,
  `rt` datetime DEFAULT NULL,
  `other` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `k2` (`cr_id`,`rt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select count(*) from cc;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from cr;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> explain select cc.*, max(r2 - r1) from cr join cc on (cr.id = cc.cr_id and rt between dt1 and dt2);
+----+-------------+-------+------------+------+---------------+------+---------+------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref        | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+------+---------+------------+------+----------+-----------------------+
|  1 | SIMPLE      | cr    | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL       |    1 |   100.00 | NULL                  |
|  1 | SIMPLE      | cc    | NULL       | ref  | k2            | k2   | 5       | test.cr.id |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+------+---------+------------+------+----------+-----------------------+
2 rows in set, 1 warning (0.00 sec)

So, we have ICP used. Now, let me partition the cc table in a simple way:

mysql> alter table cc partition by hash(id) partitions 4;
Query OK, 0 rows affected (1.58 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select cc.*, max(r2 - r1) from cr join cc on (cr.id = cc.cr_id and rt between dt1 and dt2);
+----+-------------+-------+-------------+------+---------------+------+---------+------------+------+----------+-----------------------+
| id | select_type | table | partitions  | type | possible_keys | key  | key_len | ref        | rows | filtered | Extra                 |
+----+-------------+-------+-------------+------+---------------+------+---------+------------+------+----------+-----------------------+
|  1 | SIMPLE      | cr    | NULL        | ALL  | PRIMARY       | NULL | NULL    | NULL       |    1 |   100.00 | NULL                  |
|  1 | SIMPLE      | cc    | p0,p1,p2,p3 | ref  | k2            | k2   | 5       | test.cr.id |    1 |   100.00 | Using index condition |
+----+-------------+-------+-------------+------+---------------+------+---------+------------+------+----------+-----------------------+
2 rows in set, 1 warning (0.27 sec)

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

As you can see, ICP is again used.

Sure. I used the same table structure for two tables - one is partitioned by date ("test_partitioned")
and the other one isn't ("test_solid"). See SQL queries bellow:

CREATE TABLE `test_partitioned` (
  `service_id` int(10) unsigned NOT NULL,
  `uid` varchar(32) NOT NULL DEFAULT '',
  `date` date NOT NULL DEFAULT '0000-00-00',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ord_rev` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`service_id`,`uid`,`ord_rev`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii ROW_FORMAT=COMPRESSED
/*!50100 PARTITION BY RANGE (TO_DAYS(`date`))
(PARTITION p20161001 VALUES LESS THAN (736634) ENGINE = InnoDB,
 PARTITION p20161101 VALUES LESS THAN (736664) ENGINE = InnoDB,
 PARTITION p20161201 VALUES LESS THAN (736695) ENGINE = InnoDB,
 PARTITION p20170101 VALUES LESS THAN (736726) ENGINE = InnoDB,
 PARTITION p20170201 VALUES LESS THAN (736754) ENGINE = InnoDB,
 PARTITION p20170301 VALUES LESS THAN (736785) ENGINE = InnoDB,
 PARTITION p20170401 VALUES LESS THAN (736815) ENGINE = InnoDB,
 PARTITION p20170501 VALUES LESS THAN (736846) ENGINE = InnoDB) */;

INSERT INTO `test_partitioned` (`service_id`, `uid`, `date`, `created`, `ord_rev`)
VALUES
	(1,'user123','2016-12-12','2016-12-12 07:38:07',9223223884026074696),
	(1,'user123','2017-01-04','2017-01-04 13:55:37',9223223683041068956),
	(1,'user123','2017-02-14','2017-02-14 11:17:38',9223223329748968486);

CREATE TABLE `test_solid` (
  `service_id` int(10) unsigned NOT NULL,
  `uid` varchar(32) NOT NULL DEFAULT '',
  `date` date NOT NULL DEFAULT '0000-00-00',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ord_rev` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`service_id`,`uid`,`ord_rev`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii ROW_FORMAT=COMPRESSED;

INSERT INTO `test_solid` (`service_id`, `uid`, `date`, `created`, `ord_rev`)
VALUES
	(1,'user123','2017-02-14','2017-02-14 11:17:38',9223223329748968486),
	(1,'user123','2017-01-04','2017-01-04 13:55:37',9223223683041068956),
	(1,'user123','2016-12-12','2016-12-12 07:38:07',9223223884026074696);

mysql> select * from test_partitioned where service_id=1;
+------------+---------+------------+---------------------+---------------------+
| service_id | uid     | date       | created             | ord_rev             |
+------------+---------+------------+---------------------+---------------------+
|          1 | user123 | 2016-12-12 | 2016-12-12 07:38:07 | 9223223884026074696 |
|          1 | user123 | 2017-01-04 | 2017-01-04 13:55:37 | 9223223683041068956 |
|          1 | user123 | 2017-02-14 | 2017-02-14 11:17:38 | 9223223329748968486 |
+------------+---------+------------+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql> select * from test_solid where service_id=1;
+------------+---------+------------+---------------------+---------------------+
| service_id | uid     | date       | created             | ord_rev             |
+------------+---------+------------+---------------------+---------------------+
|          1 | user123 | 2017-02-14 | 2017-02-14 11:17:38 | 9223223329748968486 |
|          1 | user123 | 2017-01-04 | 2017-01-04 13:55:37 | 9223223683041068956 |
|          1 | user123 | 2016-12-12 | 2016-12-12 07:38:07 | 9223223884026074696 |
+------------+---------+------------+---------------------+---------------------+
3 rows in set (0.01 sec)

As you can see from my example, the data from "test_solid" only is returned ordered ascending by primary key as expected.