The query planner of PolarDB-O uses partition pruning to compute an effective plan to locate a row that matches the conditions specified in the WHERE clause of a SELECT statement.
- Constraint exclusion
- Fast pruning
Partition pruning methods limit the search for data to only the partitions where the values for which you are searching can reside. The preceding two pruning methods remove partitions from a query execution plan to increase performance.
The difference between fast pruning and constraint exclusion is that fast pruning understands the relationship between the partitions in an Oracle-partitioned table, whereas exclusion constraint does not. For example, when a query searches for a specific value in a list-partitioned table, fast pruning involves only searching a specific partition. However, constraint exclusion must examine the constraints defined for each partition. Fast pruning occurs early in the planning process to reduce the number of partitions that the planner must consider, whereas constraint exclusion occurs late in the planning process.
Use constraint exclusion
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 either partition or on. By default, the parameter is set to partition.
When constraint exclusion is enabled, the server examines the constraints defined for each partition to determine whether the partition can satisfy a query.
When you run a SELECT statement that does not contain a WHERE clause, the query planner must recommend an execution plan that searches through the entire table. When you run a SELECT statement that contains a WHERE clause, the query planner determines in which partition the row can be stored, and sends query fragments to that partition. This prunes the partitions that cannot contain the row from the execution plan. If you are not using partitioned tables, disabling constraint exclusion can improve performance.
Use fast pruning
Like constraint exclusion, fast pruning can only optimize queries that contain a WHERE (or join) clause, and only when the qualifiers in the WHERE clause match a specific form. In both cases, the query planner will avoid searching for data within partitions that cannot hold the data required by the query.
Fast pruning is controlled by a boolean configuration parameter named polardb partition pruning. If polardb partition pruning is ON, PolarDB-O will fast prune specific queries. If polardb partition pruning is OFF, PolarDB-O will disable fast pruning.
Note that fast pruning cannot optimize queries against subpartitioned tables or optimize queries against range-partitioned tables that are partitioned on more than one column.
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;
Given the first WHERE clause, fast pruning can eliminate partitions europe, asia, and others because these partitions cannot hold rows that satisfy the qualifier: WHERE country = 'US'. Given the second WHERE clause, fast pruning can eliminate partitions americas, europe, and asia because these partitions cannot hold rows where country IS NULL. The operator specified in the WHERE clause must be an equal sign (=) or the equality operator suitable for the data type of the partitioning column.
For a range-partitioned table, PolarDB-O can fast prune queries that contain a WHERE clause that constrains a partitioning column to a literal value. The operator may be any of the following: 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 be a partitioning column and literal value (or IS NULL/IS NOT NULL). Note that fast pruning can also optimize DELETE and UPDATE statements containing WHERE clauses of the forms described above.