Partition pruning applies to partitioned tables or partitioned indexes. The optimizer automatically extracts the partitions that need to be scanned from the FROM and WHERE clauses based on partition keys. This prevents full table scans and reduces the number of data blocks to be scanned to improve query performance.
The partition pruning mechanism uses the following two pruning techniques. They limit the search for data to only the partitions where the values for which you are searching can be stored. These two pruning techniques can delete partitions from the execution plan of a query to improve performance.
- Constraint exclusion
- Fast pruning
The constraint_exclusion parameter is used to control constraint exclusion. The value of the constraint_exclusion parameter can be on, off, or partition. To enable constraint exclusion, you must set the constraint_exclusion parameter to partition or on. By default, the parameter is set to partition.
When constraint exclusion is enabled, the server checks the constraints that are defined for each partition to determine whether the partition can satisfy a query.
When you execute a SELECT statement that does not contain a WHERE clause, the query planner must recommend an execution plan that searches the entire table. When you execute a SELECT statement that contains a WHERE clause, the query planner determines the partition where the record can be stored and sends query fragments to this partition. This prunes the partitions that cannot contain the record from the execution plan. If you do not use partitioned tables, the performance may be improved when constraint exclusion is disabled.
The fast pruning technique can optimize only the queries that contain a WHERE (or JOIN) clause only when the qualifiers in the WHERE clause match a specific format. This limit is the same as the limit on the constraint exclusion technique. In both cases, the query planner does not search for data in the partitions that may not contain the data for the query.
Fast pruning is controlled by a BOOLEAN configuration parameter that is named polardb partition pruning. If the polardb partition pruning parameter is set to ON, PolarDB fast prunes some specific queries. If the polardb partition pruning parameter is set to OFF, PolarDB disables fast pruning.
Take note that fast pruning cannot optimize queries against subpartitioned tables or optimize queries against range-partitioned tables that are partitioned on multiple columns.
CREATE TABLE sales_hist(..., country text, ...) PARTITION BY LIST(country) ( PARTITION americas VALUES('US', 'CA', 'MX'), PARTITION europe VALUES('BE', 'NL', 'FR'), PARTITION asia VALUES('JP', 'PK', 'CN'), PARTITION others VALUES(DEFAULT) )
WHERE country = 'US' WHERE country IS NULL;
If the first WHERE clause is used, fast pruning eliminates the europe, asia, and others
partitions. This is because these partitions cannot store rows that meet the
WHERE country = 'US' qualifier. If the second WHERE clause is used, fast pruning eliminates the americas,
europe, and asia partitions. This is because these partitions cannot store rows that
meet the WHERE country IS NULL qualifier. The operator specified in a WHERE clause
must be an equal sign (=) or an equality operator that is suitable for the data type
of the partition key column.
For range-partitioned tables, PolarDB can fast prune queries that contain a WHERE clause. This WHERE clause constrains partition key columns to literal values. The operator may be one of the following operators: greater than (>), greater than or equal to (>=), less than (<), and less than or equal to (<=).
WHERE size > 100 AND size <= 200 WHERE size BETWEEN 100 AND 200
CREATE TABLE boxes(id int, size int, color text) PARTITION BY RANGE(size) ( PARTITION small VALUES LESS THAN(100), PARTITION medium VALUES LESS THAN(200), PARTITION large VALUES LESS THAN(300) )
WHERE size > 100 -- scan partitions 'medium' and 'large' WHERE size >= 100 -- scan partitions 'medium' and 'large' WHERE size = 100 -- scan partition 'medium' WHERE size <= 100 -- scan partitions 'small' and 'medium' WHERE size < 100 -- scan partition 'small' WHERE size > 100 AND size < 199 -- scan partition 'medium' WHERE size BETWEEN 100 AND 199 -- scan partition 'medium' WHERE color = 'red' AND size = 100 -- scan 'medium' WHERE color = 'red' AND (size > 100 AND size < 199) -- scan 'medium'
In each case, fast pruning requires that the qualifier must refer to a partition key column and a literal value. IS NULL or IS NOT NULL are examples of literal values. Take note that fast pruning can also optimize DELETE and UPDATE statements that contain WHERE clauses in the preceding described formats.
The difference between fast pruning and constraint exclusion is that fast pruning understands the relationships between the partitions in a partitioned table but constraint exclusion does not.
For example, when a query searches for a specific value in a list-partitioned table, the fast pruning technique can reason that only a specific partition can have this value. However, constraint exclusion must check the constraints that are defined for each partition. Fast pruning occurs early in the planning process. This helps reduce the number of partitions that the planner must consider. However, constraint exclusion occurs late in the planning process.