Using partition pruning v17

EDB Postgres Advanced Server's query planner uses partition pruning to compute an efficient plan to locate any rows that match the conditions specified in the WHERE clause of a SELECT statement. To successfully prune partitions from an execution plan, the WHERE clause must constrain the information that's compared to the partitioning key column specified when creating the partitioned table.

Partition pruning techniques

The partition pruning mechanism uses two optimization techniques:

• Constraint exclusion
• Fast pruning

Partition pruning techniques limit the search for data only to those partitions where the values you're searching for might reside. Both pruning techniques remove partitions from a query's execution plan, improving performance.

The difference between the fast pruning and constraint exclusion is that fast pruning understands the relationship between the partitions in an Oracle-partitioned table. Constraint exclusion doesn't. For example, when a query searches for a specific value in a list-partitioned table, fast pruning can reason that only a specific partition can hold that value. 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. Constraint exclusion occurs late in the planning process.

This example shows the efficiency of partition pruning, using the EXPLAIN statement to confirm that EDB Postgres Advanced Server is pruning partitions from the execution plan of a query.

Using constraint exclusion

The constraint_exclusion parameter controls constraint exclusion. The constraint_exclusion parameter can have a value of on, off, or partition. To enable constraint exclusion, you must set the parameter to either partition or on. By default, the parameter is set to partition.

For more information about constraint exclusion, see the PostgreSQL documentation.

When constraint exclusion is enabled, the server examines the constraints defined for each partition to determine if that partition can satisfy a query.

When you execute a SELECT statement that doesn't 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 to store the row
• Sends query fragments to that partition
• Prunes the partitions that can't contain that row from the execution plan

If you aren't using partitioned tables, disabling constraint exclusion might improve performance.

Using fast pruning

Like constraint exclusion, fast pruning can optimize only queries that include a WHERE or join clause. However, the qualifiers in the WHERE clause must match a certain form. In both cases, the query planner avoids searching for data in partitions that can't hold the data required by the query.

Fast pruning is controlled by a Boolean configuration parameter named edb_enable_pruning. Set edb_enable_pruning to ON to enable fast pruning of certain queries. Set edb_enable_pruning to OFF to disable fast pruning.

For LIST-partitioned tables, EDB Postgres Advanced Server can fast prune queries that contain a WHERE clause that constrains a partitioning column to a literal value. For example, given a LIST-partitioned table such as:

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

Fast pruning can reason about WHERE clauses such as:

WHERE country = 'US'

WHERE country IS NULL;

With the first WHERE clause, fast pruning eliminates partitions europe, asia, and others because those partitions can't hold rows that satisfy the qualifier WHERE country = 'US'.

With the second WHERE clause, fast pruning eliminates partitions americas, europe, and asia because those partitions can't hold rows where country IS NULL.

The operator specified in the WHERE clause must be an equals sign (=) or the equality operator appropriate for the data type of the partitioning column.

For range-partitioned tables, EDB Postgres Advanced Server can fast prune queries that contain a WHERE clause that constrains a partitioning column to a literal value. However, the operator can be any of the following:

>

>=

=

<=

<

Fast pruning also reasons about more complex expressions involving AND and BETWEEN operators, such as:

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

Fast pruning can't prune based on expressions involving OR or IN. For example, when querying a RANGE-partitioned table, such as:

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

Fast pruning can reason about WHERE clauses such as:

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 refer to a partitioning column and literal value (or IS NULL/IS NOT NULL).

• On this page
• Partition pruning techniques
• Using constraint exclusion
• Using fast pruning