Explain Statements Using Partitions
The EXPLAIN statement displays the partitions that TiDB needs to access in order to execute a query. Because of partition pruning, the displayed partitions are often only a subset of the overall partitions. This document describes some of the optimizations for common partitioned tables, and how to interpret the output of EXPLAIN.
The sample data used in this document:
CREATE TABLE t1 (
id BIGINT NOT NULL auto_increment,
d date NOT NULL,
pad1 BLOB,
pad2 BLOB,
pad3 BLOB,
PRIMARY KEY (id,d)
) PARTITION BY RANGE (YEAR(d)) (
PARTITION p2016 VALUES LESS THAN (2017),
PARTITION p2017 VALUES LESS THAN (2018),
PARTITION p2018 VALUES LESS THAN (2019),
PARTITION p2019 VALUES LESS THAN (2020),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
INSERT INTO t1 (d, pad1, pad2, pad3) VALUES
('2016-01-01', RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024)),
('2016-06-01', RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024)),
('2016-09-01', RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024)),
('2017-01-01', RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024)),
('2017-06-01', RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024)),
('2017-09-01', RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024)),
('2018-01-01', RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024)),
('2018-06-01', RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024)),
('2018-09-01', RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024)),
('2019-01-01', RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024)),
('2019-06-01', RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024)),
('2019-09-01', RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024)),
('2020-01-01', RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024)),
('2020-06-01', RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024)),
('2020-09-01', RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024));
INSERT INTO t1 SELECT NULL, a.d, RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 10000;
INSERT INTO t1 SELECT NULL, a.d, RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 10000;
INSERT INTO t1 SELECT NULL, a.d, RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 10000;
INSERT INTO t1 SELECT NULL, a.d, RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 10000;
SELECT SLEEP(1);
ANALYZE TABLE t1;
The following example shows a statement against the newly created partitioned table:
EXPLAIN SELECT COUNT(*) FROM t1 WHERE d = '2017-06-01';
+------------------------------+---------+-----------+---------------------------+-------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------+---------+-----------+---------------------------+-------------------------------------------+
| StreamAgg_21 | 1.00 | root | | funcs:count(Column#8)->Column#6 |
| └─TableReader_22 | 1.00 | root | | data:StreamAgg_10 |
| └─StreamAgg_10 | 1.00 | cop[tikv] | | funcs:count(1)->Column#8 |
| └─Selection_20 | 8.87 | cop[tikv] | | eq(test.t1.d, 2017-06-01 00:00:00.000000) |
| └─TableFullScan_19 | 8870.00 | cop[tikv] | table:t1, partition:p2017 | keep order:false |
+------------------------------+---------+-----------+---------------------------+-------------------------------------------+
5 rows in set (0.01 sec)
Starting from the inner-most (└─TableFullScan_19) operator and working back towards the root operator (StreamAgg_21):
- TiDB successfully identified that only one partition (
p2017) needed to be accessed. This is noted underaccess object. - The partition itself was scanned in the operator
└─TableFullScan_19and then└─Selection_20was applied to filter for rows that have a start date of2017-06-01 00:00:00.000000. - The rows that match
└─Selection_20are then stream aggregated in the coprocessor, which natively understands thecountfunction. - Each coprocessor request then sends back one row to
└─TableReader_22inside TiDB, which is then stream aggregated underStreamAgg_21and one row is returned to the client.
In the following example, partition pruning does not eliminate any partitions:
EXPLAIN SELECT COUNT(*) FROM t1 WHERE YEAR(d) = 2017;
+------------------------------------+----------+-----------+---------------------------+----------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------------+----------+-----------+---------------------------+----------------------------------+
| HashAgg_20 | 1.00 | root | | funcs:count(Column#7)->Column#6 |
| └─PartitionUnion_21 | 5.00 | root | | |
| ├─StreamAgg_36 | 1.00 | root | | funcs:count(Column#9)->Column#7 |
| │ └─TableReader_37 | 1.00 | root | | data:StreamAgg_25 |
| │ └─StreamAgg_25 | 1.00 | cop[tikv] | | funcs:count(1)->Column#9 |
| │ └─Selection_35 | 6000.00 | cop[tikv] | | eq(year(test.t1.d), 2017) |
| │ └─TableFullScan_34 | 7500.00 | cop[tikv] | table:t1, partition:p2016 | keep order:false |
| ├─StreamAgg_55 | 1.00 | root | | funcs:count(Column#11)->Column#7 |
| │ └─TableReader_56 | 1.00 | root | | data:StreamAgg_44 |
| │ └─StreamAgg_44 | 1.00 | cop[tikv] | | funcs:count(1)->Column#11 |
| │ └─Selection_54 | 14192.00 | cop[tikv] | | eq(year(test.t1.d), 2017) |
| │ └─TableFullScan_53 | 17740.00 | cop[tikv] | table:t1, partition:p2017 | keep order:false |
| ├─StreamAgg_74 | 1.00 | root | | funcs:count(Column#13)->Column#7 |
| │ └─TableReader_75 | 1.00 | root | | data:StreamAgg_63 |
| │ └─StreamAgg_63 | 1.00 | cop[tikv] | | funcs:count(1)->Column#13 |
| │ └─Selection_73 | 3977.60 | cop[tikv] | | eq(year(test.t1.d), 2017) |
| │ └─TableFullScan_72 | 4972.00 | cop[tikv] | table:t1, partition:p2018 | keep order:false |
| ├─StreamAgg_93 | 1.00 | root | | funcs:count(Column#15)->Column#7 |
| │ └─TableReader_94 | 1.00 | root | | data:StreamAgg_82 |
| │ └─StreamAgg_82 | 1.00 | cop[tikv] | | funcs:count(1)->Column#15 |
| │ └─Selection_92 | 20361.60 | cop[tikv] | | eq(year(test.t1.d), 2017) |
| │ └─TableFullScan_91 | 25452.00 | cop[tikv] | table:t1, partition:p2019 | keep order:false |
| └─StreamAgg_112 | 1.00 | root | | funcs:count(Column#17)->Column#7 |
| └─TableReader_113 | 1.00 | root | | data:StreamAgg_101 |
| └─StreamAgg_101 | 1.00 | cop[tikv] | | funcs:count(1)->Column#17 |
| └─Selection_111 | 8892.80 | cop[tikv] | | eq(year(test.t1.d), 2017) |
| └─TableFullScan_110 | 11116.00 | cop[tikv] | table:t1, partition:pmax | keep order:false |
+------------------------------------+----------+-----------+---------------------------+----------------------------------+
27 rows in set (0.00 sec)
From the output above:
- TiDB believes that it needs to access all of the partitions
(p2016..pMax). This is because the predicateYEAR(d) = 2017is considered non-sargable. This issue is not specific to TiDB. - As each partition is scanned, a
Selectionoperator filters out rows that do not match the year of 2017. - A stream aggregation on each partition is performed to count the number of rows that match.
- The operator
└─PartitionUnion_21unions the results from accessing each partition.