[MDEV-17355] explain partition Created: 2018-10-02  Updated: 2018-10-09

Status: Open
Project: MariaDB Server
Component/s: Documentation, Partitioning
Affects Version/s: 10.3.9
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Aurélien LEQUOY Assignee: Ian Gilfillan
Resolution: Unresolved Votes: 0
Labels: None
Environment:

ubuntu 16.04


 Description   

I got this table, with partition each day :

CREATE TABLE `ts_value_general_int` (
 
  `id_mysql_server` int(11) NOT NULL,
 
  `id_ts_variable` int(11) NOT NULL,
 
  `date` datetime NOT NULL,
 
  `value` bigint(20) unsigned NOT NULL,
 
  PRIMARY KEY (`date`,`id_mysql_server`,`id_ts_variable`)
 
) ENGINE=ROCKSDB DEFAULT CHARSET=latin1
 
 PARTITION BY RANGE (to_days(`date`))
 
(PARTITION `p737251` VALUES LESS THAN (737251) ENGINE = ROCKSDB,
 
 PARTITION `p737252` VALUES LESS THAN (737252) ENGINE = ROCKSDB,
 
 PARTITION `p737253` VALUES LESS THAN (737253) ENGINE = ROCKSDB,
 
 PARTITION `p737254` VALUES LESS THAN (737254) ENGINE = ROCKSDB,
 
 PARTITION `p737255` VALUES LESS THAN (737255) ENGINE = ROCKSDB,
 
 PARTITION `p737257` VALUES LESS THAN (737257) ENGINE = ROCKSDB,
 
 PARTITION `p737258` VALUES LESS THAN (737258) ENGINE = ROCKSDB,
 
 PARTITION `p737259` VALUES LESS THAN (737259) ENGINE = ROCKSDB,
 
 PARTITION `p737260` VALUES LESS THAN (737260) ENGINE = ROCKSDB,
 
 PARTITION `p737261` VALUES LESS THAN (737261) ENGINE = ROCKSDB,
 
 PARTITION `p737262` VALUES LESS THAN (737262) ENGINE = ROCKSDB,
 
 PARTITION `p737263` VALUES LESS THAN (737263) ENGINE = ROCKSDB,
 
 PARTITION `p737264` VALUES LESS THAN (737264) ENGINE = ROCKSDB,
 
 PARTITION `p737265` VALUES LESS THAN (737265) ENGINE = ROCKSDB,
 
 PARTITION `p737266` VALUES LESS THAN (737266) ENGINE = ROCKSDB,
 
 PARTITION `p737267` VALUES LESS THAN (737267) ENGINE = ROCKSDB,
 
 PARTITION `p737268` VALUES LESS THAN (737268) ENGINE = ROCKSDB,
 
 PARTITION `p737269` VALUES LESS THAN (737269) ENGINE = ROCKSDB,
 
 PARTITION `p737279` VALUES LESS THAN (737279) ENGINE = ROCKSDB,
 
 PARTITION `p737280` VALUES LESS THAN (737280) ENGINE = ROCKSDB,
 
 PARTITION `p737285` VALUES LESS THAN (737285) ENGINE = ROCKSDB,
 
 PARTITION `p737286` VALUES LESS THAN (737286) ENGINE = ROCKSDB,
 
 PARTITION `p737287` VALUES LESS THAN (737287) ENGINE = ROCKSDB,
 
 PARTITION `p737288` VALUES LESS THAN (737288) ENGINE = ROCKSDB,
 
 PARTITION `p737289` VALUES LESS THAN (737289) ENGINE = ROCKSDB,
 
 PARTITION `p737292` VALUES LESS THAN (737292) ENGINE = ROCKSDB,
 
 PARTITION `p737293` VALUES LESS THAN (737293) ENGINE = ROCKSDB,
 
 PARTITION `p737294` VALUES LESS THAN (737294) ENGINE = ROCKSDB,
 
 PARTITION `p737295` VALUES LESS THAN (737295) ENGINE = ROCKSDB,
 
 PARTITION `p737296` VALUES LESS THAN (737296) ENGINE = ROCKSDB,
 
 PARTITION `p737297` VALUES LESS THAN (737297) ENGINE = ROCKSDB,
 
 PARTITION `p737298` VALUES LESS THAN (737298) ENGINE = ROCKSDB,
 
 PARTITION `p737299` VALUES LESS THAN (737299) ENGINE = ROCKSDB,
 
 PARTITION `p737300` VALUES LESS THAN (737300) ENGINE = ROCKSDB,
 
 PARTITION `p737301` VALUES LESS THAN (737301) ENGINE = ROCKSDB,
 
 PARTITION `p737306` VALUES LESS THAN (737306) ENGINE = ROCKSDB,
 
 PARTITION `p737307` VALUES LESS THAN (737307) ENGINE = ROCKSDB,
 
 PARTITION `p737308` VALUES LESS THAN (737308) ENGINE = ROCKSDB,
 
 PARTITION `p737309` VALUES LESS THAN (737309) ENGINE = ROCKSDB,
 
 PARTITION `p737310` VALUES LESS THAN (737310) ENGINE = ROCKSDB,
 
 PARTITION `p737313` VALUES LESS THAN (737313) ENGINE = ROCKSDB,
 
 PARTITION `p737314` VALUES LESS THAN (737314) ENGINE = ROCKSDB,
 
 PARTITION `p737315` VALUES LESS THAN (737315) ENGINE = ROCKSDB,
 
 PARTITION `p737317` VALUES LESS THAN (737317) ENGINE = ROCKSDB,
 
 PARTITION `p737318` VALUES LESS THAN (737318) ENGINE = ROCKSDB,
 
 PARTITION `p737320` VALUES LESS THAN (737320) ENGINE = ROCKSDB,
 
 PARTITION `p737321` VALUES LESS THAN (737321) ENGINE = ROCKSDB,
 
 PARTITION `p737322` VALUES LESS THAN (737322) ENGINE = ROCKSDB,
 
 PARTITION `p737330` VALUES LESS THAN (737330) ENGINE = ROCKSDB,
 
 PARTITION `p737331` VALUES LESS THAN (737331) ENGINE = ROCKSDB,
 
 PARTITION `p737332` VALUES LESS THAN (737332) ENGINE = ROCKSDB,
 
 PARTITION `p737333` VALUES LESS THAN (737333) ENGINE = ROCKSDB,
 
 PARTITION `p737334` VALUES LESS THAN (737334) ENGINE = ROCKSDB,
 
 PARTITION `p737335` VALUES LESS THAN (737335) ENGINE = ROCKSDB)
 
1 row in set (0.000 sec)

when i do a explain :

explain partitions(
 
  SELECT 
    a.`id_mysql_server`, 
    a.`id_ts_variable`, 
    '' as connection_name, 
    a.`date`, 
    a.`value` 
  FROM 
    `ts_value_general_int` a 
    INNER JOIN `ts_date_by_server` b on a.`date` = b.`date` 
    AND a.`id_mysql_server` = b.`id_mysql_server` 
    AND b.`id_ts_file` = 3 
  WHERE 
    id_ts_variable = 484 
    AND a.id_mysql_server = 95 
    AND a.`date` >
 
 date_sub(now(), INTERVAL 6 hour)
 
);
 
+------+-------------+-------+-----------------+--------+-----------------+-----------------+---------+-------------------------------+------+--------------------------+
 
| id   | select_type | table | partitions      | type   | possible_keys   | key             | key_len | ref                           | rows | Extra                    |
 
+------+-------------+-------+-----------------+--------+-----------------+-----------------+---------+-------------------------------+------+--------------------------+
 
|    1 | SIMPLE      | b     | NULL            | range  | id_mysql_server | id_mysql_server | 13      | NULL                          |  229 | Using where; Using index |
 
|    1 | SIMPLE      | a     | p737252,p737335 | eq_ref | PRIMARY         | PRIMARY         | 13      | pmacontrol.b.date,const,const |    1 |                          |
 
+------+-------------+-------+-----------------+--------+-----------------+-----------------+---------+-------------------------------+------+--------------------------+
 
2 rows in set (0.004 sec)
 
 

I don't understand why MySQL check p737252, he should check only the partition p737335. Any idea ?

 Comments   
Comment by Elena Stepanova [ 2018-10-03 ]

I suppose that's how it processes an open range. Partition pruning works best with closed ranges, where both lowest and highest values are defined. I expect if you convert your WHERE condition into something like 

  WHERE 
    id_ts_variable = 484 
    AND a.id_mysql_server = 95 
    AND a.`date` >
 
 date_sub(now(), INTERVAL 6 hour)
 
   AND a.`date` <= now()

(which you probably mean anyway), you'll get the result you're looking for.

MySQL manual has a quite informative page on partition pruning, it's worth reading. MariaDB KB, however, documents it very poorly, so I'm passing this issue over to documentation.

Comment by Aurélien LEQUOY [ 2018-10-09 ]

strangely it's worked :

MariaDB [pmacontrol]> explain partitions(    SELECT       a.`id_mysql_server`,       a.`id_ts_variable`,       '' as connection_name,       a.`date`,       a.`value`     FROM       `ts_value_general_int` a       INNER JOIN `ts_date_by_server` b on a.`date` = b.`date`       AND a.`id_mysql_server` = b.`id_mysql_server`       AND b.`id_ts_file` = 3     WHERE       id_ts_variable = 484       AND a.id_mysql_server = 95       AND a.`date` >   date_sub(now(), INTERVAL 6 hour)  and a.date <= now() );
 
+------+-------------+-------+------------+--------+----------------------+-----------------+---------+-------------------------------+------+-------------+
 
| id   | select_type | table | partitions | type   | possible_keys        | key             | key_len | ref                           | rows | Extra       |
 
+------+-------------+-------+------------+--------+----------------------+-----------------+---------+-------------------------------+------+-------------+
 
|    1 | SIMPLE      | a     | p737342    | range  | PRIMARY              | PRIMARY         | 13      | NULL                          |    1 | Using where |
 
|    1 | SIMPLE      | b     | NULL       | eq_ref | id_mysql_server,date | id_mysql_server | 13      | const,const,pmacontrol.a.date |    1 | Using index |
 
+------+-------------+-------+------------+--------+----------------------+-----------------+---------+-------------------------------+------+-------------+
 
2 rows in set (0.034 sec)
 
 
 
MariaDB [pmacontrol]> show create table ts_value_general_int\G
 
*************************** 1. row ***************************
 
       Table: ts_value_general_int
 
Create Table: CREATE TABLE `ts_value_general_int` (
 
  `id_mysql_server` int(11) NOT NULL,
 
  `id_ts_variable` int(11) NOT NULL,
 
  `date` datetime NOT NULL,
 
  `value` bigint(20) unsigned NOT NULL,
 
  PRIMARY KEY (`id_mysql_server`,`id_ts_variable`,`date`)
 
) ENGINE=ROCKSDB DEFAULT CHARSET=latin1
 
 PARTITION BY RANGE (to_days(`date`))
 
(PARTITION `p737338` VALUES LESS THAN (737338) ENGINE = ROCKSDB,
 
 PARTITION `p737339` VALUES LESS THAN (737339) ENGINE = ROCKSDB,
 
 PARTITION `p737340` VALUES LESS THAN (737340) ENGINE = ROCKSDB,
 
 PARTITION `p737341` VALUES LESS THAN (737341) ENGINE = ROCKSDB,
 
 PARTITION `p737342` VALUES LESS THAN (737342) ENGINE = ROCKSDB)
 
1 row in set (0.003 sec)
 
 
 
MariaDB [pmacontrol]> explain partitions(    SELECT       a.`id_mysql_server`,       a.`id_ts_variable`,       '' as connection_name,       a.`date`,       a.`value`     FROM       `ts_value_general_int` a       INNER JOIN `ts_date_by_server` b on a.`date` = b.`date`       AND a.`id_mysql_server` = b.`id_mysql_server`       AND b.`id_ts_file` = 3     WHERE       id_ts_variable = 484       AND a.id_mysql_server = 95       AND a.`date` >   date_sub(now(), INTERVAL 6 hour)   );
 
+------+-------------+-------+-----------------+--------+----------------------+-----------------+---------+-------------------------------+------+--------------------------+
 
| id   | select_type | table | partitions      | type   | possible_keys        | key             | key_len | ref                           | rows | Extra                    |
 
+------+-------------+-------+-----------------+--------+----------------------+-----------------+---------+-------------------------------+------+--------------------------+
 
|    1 | SIMPLE      | b     | NULL            | range  | id_mysql_server,date | id_mysql_server | 13      | NULL                          |    1 | Using where; Using index |
 
|    1 | SIMPLE      | a     | p737338,p737342 | eq_ref | PRIMARY              | PRIMARY         | 13      | const,const,pmacontrol.b.date |    1 |                          |
 
+------+-------------+-------+-----------------+--------+----------------------+-----------------+---------+-------------------------------+------+--------------------------+
 
2 rows in set (0.032 sec)

Generated at Thu Feb 08 08:35:50 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.