The partition pruning feature helps avoid accessing irrelevant partitions, significantly improving SQL execution efficiency. This topic describes the principles and applications of partition pruning.
When users access a partitioned table, they often only need to access a subset of the partitions. The optimization process by which the optimizer avoids accessing irrelevant partitions is called partition pruning. Partition pruning is an important optimization mechanism provided by partitioned tables, which can greatly improve SQL execution efficiency. You can leverage the feature of partition pruning by including partition location conditions in your queries to avoid accessing irrelevant data and optimize query performance.
Partition pruning itself is a complex process. The optimizer needs to extract relevant partition information based on the partitioning information of the user table and the conditions specified in the SQL. Since the conditions in SQL are often complex, the complexity of the extraction logic also increases. This process is performed by the Query Range sub-module in OceanBase AI Database.
When users access a partitioned table, since all data where col1 is 1 resides in partition p1, we only need to access that partition, and there is no need to access partitions 0, 2, 3, or 4. As shown below:
obclient> CREATE TABLE tbl1(col1 INT, col2 INT) PARTITION BY HASH(col1) PARTITIONS 5;
obclient> SELECT * FROM tbl1 WHERE col1 = 1;
You can use EXPLAIN to view the execution plan and see the result of partition pruning:
obclient> EXPLAIN SELECT * FROM tbl1 WHERE col1 = 1;
The query result is as follows:
+------------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------------+
| =============================================== |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ----------------------------------------------- |
| |0 |TABLE FULL SCAN|tbl1|1 |4 | |
| =============================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([tbl1.col1], [tbl1.col2]), filter([tbl1.col1 = 1]), rowset=16 |
| access([tbl1.col1], [tbl1.col2]), partitions(p1) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([tbl1.__pk_increment]), range(MIN ; MAX)always true |
+------------------------------------------------------------------------------------+
11 rows in set
Basic principles of partition pruning for primary partitions
HASH and LIST partitions
Partition pruning works by evaluating the conditions in the WHERE clause to determine the value of the partitioning key, and then determining which partitions need to be accessed based on the result. If the partition condition is an expression that appears as a whole in an equality condition, partition pruning can also be performed.
The following example shows partition pruning when the partition condition is the expression c1 + c2, and this expression appears as a whole in an equality condition:
obclient> CREATE TABLE t1_h (c1 INT, c2 INT) PARTITION BY HASH(c1 + c2) PARTITIONS 5;
obclient> EXPLAIN SELECT * FROM t1_h WHERE c1 + c2 = 1;
The query result is as follows:
+------------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------------+
| =============================================== |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ----------------------------------------------- |
| |0 |TABLE FULL SCAN|t1_h|1 |4 | |
| =============================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t1_h.c1], [t1_h.c2]), filter([t1_h.c1 + t1_h.c2 = 1]), rowset=16 |
| access([t1_h.c1], [t1_h.c2]), partitions(p1) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([t1_h.__pk_increment]), range(MIN ; MAX)always true |
+------------------------------------------------------------------------------------+
11 rows in set
RANGE partitions
You can determine the partitions that need to be accessed by finding the intersection between the range of the partitioning key in the WHERE clause and the partitioning ranges defined in the table.
For RANGE partitions:
When the partition condition is a column, partition pruning is supported regardless of whether the query condition is an equality or inequality condition.
When the partition condition is an expression and the query condition is an equality condition, partition pruning is supported.
For example, if the partitioning condition is the expression c1 and the query condition is a non-equality condition c1 < 150 and c1 > 100, partition pruning can be performed. Example:
obclient> CREATE TABLE t1_r (c1 INT, c2 INT) PARTITION BY RANGE(c1)
(PARTITION p0 VALUES LESS THAN(100),
PARTITION p1 VALUES LESS THAN(200)
);
obclient> EXPLAIN SELECT * FROM t1_r WHERE c1 < 150 and c1 > 110;
The query result is as follows:
+------------------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------------------+
| =============================================== |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ----------------------------------------------- |
| |0 |TABLE FULL SCAN|t1_r|1 |4 | |
| =============================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t1_r.c1], [t1_r.c2]), filter([t1_r.c1 < 150], [t1_r.c1 > 110]), rowset=16 |
| access([t1_r.c1], [t1_r.c2]), partitions(p1) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false,false], |
| range_key([t1_r.__pk_increment]), range(MIN ; MAX)always true |
+------------------------------------------------------------------------------------------+
11 rows in set
Furthermore, if the partitioning condition expression uses time-type functions such as YEAR(), TO_DAYS(), or TO_SECONDS(), partition pruning can also be performed based on the range specified by the query conditions.
Example:
obclient> CREATE TABLE tbl_r (log_id BIGINT NOT NULL, log_value VARCHAR(50),log_date datetime NOT NULL)
PARTITION BY RANGE(to_days(log_date))
(PARTITION M202001 VALUES LESS THAN(to_days('2020/02/01'))
, PARTITION M202002 VALUES LESS THAN(to_days('2020/03/01'))
, PARTITION M202003 VALUES LESS THAN(to_days('2020/04/01'))
, PARTITION M202004 VALUES LESS THAN(to_days('2020/05/01'))
, PARTITION M202005 VALUES LESS THAN(to_days('2020/06/01'))
, PARTITION M202006 VALUES LESS THAN(to_days('2020/07/01'))
, PARTITION M202007 VALUES LESS THAN(to_days('2020/08/01'))
, PARTITION M202008 VALUES LESS THAN(to_days('2020/09/01'))
, PARTITION M202009 VALUES LESS THAN(to_days('2020/10/01'))
, PARTITION M202010 VALUES LESS THAN(to_days('2020/11/01'))
, PARTITION M202011 VALUES LESS THAN(to_days('2020/12/01'))
, PARTITION M202012 VALUES LESS THAN(to_days('2021/01/01'))
);
obclient> EXPLAIN SELECT * FROM tbl_r WHERE log_date > '2020/07/15' and log_date <'2020/10/07';
The query result is as follows:
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| ============================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------- |
| |0 |PX COORDINATOR | |1 |16 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10000|1 |16 | |
| |2 | └─PX PARTITION ITERATOR| |1 |16 | |
| |3 | └─TABLE FULL SCAN |tbl_r |1 |16 | |
| ============================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(tbl_r.log_id, tbl_r.log_value, tbl_r.log_date)]), filter(nil), rowset=16 |
| 1 - output([INTERNAL_FUNCTION(tbl_r.log_id, tbl_r.log_value, tbl_r.log_date)]), filter(nil), rowset=16 |
| dop=1 |
| 2 - output([tbl_r.log_date], [tbl_r.log_id], [tbl_r.log_value]), filter(nil), rowset=16 |
| force partition granule |
| 3 - output([tbl_r.log_date], [tbl_r.log_id], [tbl_r.log_value]), filter([tbl_r.log_date > cast('2020/07/15', MYSQL_DATETIME(-1, -1))], [tbl_r.log_date |
| < cast('2020/10/07', MYSQL_DATETIME(-1, -1))]), rowset=16 |
| access([tbl_r.log_date], [tbl_r.log_id], [tbl_r.log_value]), partitions(p[6-9]) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false,false], |
| range_key([tbl_r.__pk_increment]), range(MIN ; MAX)always true |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
20 rows in set
Basic principles of subpartition pruning
For subpartitions, first determine the partitions to access based on the primary partitioning key, and then determine the subpartitions to access based on the subpartitioning key. Finally, determine all the physical subpartitions to access based on the product of the partitions and subpartitions.
In the following example, after calculation, the primary partition pruning result is p0, and the subpartition pruning result is sp0. Therefore, the physical partitions to access are p0sp0.
Note
In this example, the identifier of the physical partition to access is p0sp0, which is not the name of the subpartition. For a template-based subpartitioned table, the naming convention for subpartitions is ($part_name)s($subpart_name), for example, p0ssp0.
obclient> CREATE TABLE tbl2_rr(col1 INT,col2 INT)
PARTITION BY RANGE(col1)
SUBPARTITION BY RANGE(col2)
SUBPARTITION TEMPLATE
(SUBPARTITION sp0 VALUES LESS THAN(1000),
SUBPARTITION sp1 VALUES LESS THAN(2000)
)
(PARTITION p0 VALUES LESS THAN(100),
PARTITION p1 VALUES LESS THAN(200)
);
obclient> EXPLAIN SELECT * FROM tbl2_rr WHERE (col1 = 1 or col1 = 2) and (col2 > 101 and col2 < 150);
The query result is as follows:
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| ================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| -------------------------------------------------- |
| |0 |TABLE FULL SCAN|tbl2_rr|1 |4 | |
| ================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([tbl2_rr.col1], [tbl2_rr.col2]), filter([tbl2_rr.col2 > 101], [tbl2_rr.col2 < 150], [tbl2_rr.col1 = 1 OR tbl2_rr.col1 = 2]), rowset=16 |
| access([tbl2_rr.col1], [tbl2_rr.col2]), partitions(p0sp0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false,false,false], |
| range_key([tbl2_rr.__pk_increment]), range(MIN ; MAX)always true |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
11 rows in set
In some scenarios, partition pruning may result in a certain degree of amplification, but the optimizer ensures that the pruned result is a superset of the data to be accessed, with no data loss occurring.