Partition Pruning

The partition pruning mechanism uses the following two pruning techniques:

• Constraint exclusion

• Fast pruning

Constraint exclusion

The constraint_exclusion parameter is used to control constraint exclusion. Valid values are on, off, and partition. The default value is partition. To enable constraint exclusion, set the constraint_exclusion parameter to partition or on. To disable constraint exclusion, set the constraint_exclusion parameter to off.

If you do not use partitioned tables, we recommend that you disable constraint exclusion to improve performance.

Fast pruning

The fast pruning technique can optimize query statements only if the statements contain WHERE clauses and the qualifiers in the WHERE clauses match a specific format. This limit is the same as the limit on the constraint exclusion technique.

The polar_comp_enable_pruning parameter is used to control fast pruning. Valid values are on and off. The default value is on. If the polar_comp_enable_pruning parameter is set to on, fast pruning is enabled to fast prune specific queries. If the polar_comp_enable_pruning parameter is set to off, fast pruning is disabled.

The following part describes how to use fast pruning:

• For queries on list-partitioned tables, PolarDB for PostgreSQL (Compatible with Oracle) can fast prune queries that contain WHERE clauses. The operators that can be used in the WHERE clauses are the equal sign (=), IS NULL, and IS NOT NULL.

  The following code provides an example:

  1. Create a list-partitioned table.

     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)
     )                

  2. Use fast pruning to extract the partitions that satisfy each of the following WHERE clauses from the list-partitioned table.

     WHERE country = 'US' WHERE country IS NULL;

     If the first WHERE clause is used, fast pruning excludes the europe partition, the asia partition, and the others partition. This is because these partitions do not satisfy WHERE country = 'US'.

     If the second WHERE clause is used, fast pruning excludes the americas partition, the europe partition, and the asia partition. This is because these partitions do not satisfy WHERE country IS NULL.

• For range-partitioned tables, PolarDB for PostgreSQL (Compatible with Oracle) can fast prune queries that contain WHERE clauses. The operators that can be used in the WHERE clauses are the equal sign (=), greater-than (>) sign, greater-than-or-equal-to (>=) sign, less-than (<) sign, less-than-or-equal-to (<=) sign, IS NULL, and IS NOT NULL. The WHERE clauses can also contain more complex expressions that contain the AND or BETWEEN operator. In the following WHERE clauses, the AND and BETWEEN operators are used to form complex expressions.

  WHERE size > 100 AND size <= 200 WHERE size BETWEEN 100 AND 200

  Note

  Fast pruning cannot be performed based on expressions that contain the OR or IN operator.

  The following code provides an example:

  1. Create a range-partitioned table.

     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)
     )

  2. Use fast pruning to extract the partitions that satisfy each of the following WHERE clauses from the range-partitioned table.

     WHERE size > 100-- Scans the medium partition and the large partition
     WHERE size >= 100-- Scans the medium partition and the large partition
     WHERE size =100-- Scans the medium partition
     WHERE size <= 100-- Scans the small partition and the medium partition
     WHERE size < 100-- Scans the small partition
     WHERE size > 100 AND size < 199-- Scans the medium partition
     WHERE size BETWEEN 100 AND 199-- Scans the medium partition
     WHERE color = 'red' AND size = 100  -- Scans the medium partition
     WHERE color = 'red' AND (size > 100 AND size < 199) -- Scans the medium partition