Partition Pruning in EDB Postgres 9.5

 

EDB Postgres supports two types of partition pruning:

 

1. Constraint exclusion pruning:

 

It is a feature introduced in PostgreSQL 8.1. This type of pruning works with the PostgreSQL-style of partition. With constraint exclusion enabled, the planner will examine the constraints of each partition and try to prove that the partition need not be scanned because it could not contain any rows meeting the query's WHERE clause. When the planner can prove this, it excludes the partition from the query plan.

 

However, it has some limitations. Following is the limitation of constraint_exclusion:

 

a. Constraint exclusion only works when the query's WHERE clause contains constants (or externally supplied parameters). For example, a comparison against a non-immutable function such as CURRENT_TIMESTAMP cannot be optimized, since the planner cannot know which partition the function value might fall into at run time.

b. Keep the partitioning constraints simple, else the planner may not be able to prove that partitions don't need to be visited. Use simple equality conditions for list partitioning, or simple range tests for range partitioning, as illustrated in the preceding examples. A good rule of thumb is that partitioning constraints should contain only comparisons of the partitioning column(s) to constants using B-tree-indexable operators.

 

For verification, the below shows the behavior of constraint_exclusion pruning:

1. Let's create a PostgreSQL-style partition table using table inheritance feature.

 

 

Execute simple query to verify the constraint_exclusion behavior based on above definition:

 

 

The above output of the query shows EDB Postgres considered all partitions of table measurements, even though we had included partition column and constant value in WHERE clause. This is due to check constraint which has date_part function. date_part function is not immutable in Postgres, therefore at planning time, EDB Postgres doesn't know what value it will return. And, if the user doesn't include proper WHERE clause as mentioned in check constraint, pruning will not work.

 

In Postgres you can make a function immutable by using ALTER FUNCTION command.

 

In the below example, we will make date_part function immutable to check if constraint_exclusion works with date_part immutable function or not:

 

 

Perform EXPLAIN command to check the behavior of constraint_exclusion using immutable function:

 

 

As you can see with immutable function EDB Postgres was able to perform constraint_exclusion pruning.

 

What if we change the WHERE clause a little bit and include < and = operator in our SQL queries (below are examples):

 

Lets modify the definition of the measurement table and verify the ,=, <= and = operator in WHERE clause.

 

 

Below is explain plan based on above definition:

 

 

The above clearly shows that with correct constraint definition, constriant_exclusion pruning can work for >,<,>=, <= and = operator in WHERE clause.

 

2. Fast pruning:

 

EDB Postgres has CREATE TABLE PARTITION SYNTAX since version 9.1. PARTITION SYNTAX in EDB Postgres uses one more pruning called fast pruning. Fast pruning uses the partition metadata and query predicates to efficiently reduce the set of partitions to scan. Fast pruning in EDB Postgres happens before query plan. Let's verify the behavior of fast pruning.

 

As mentioned fast pruning works with partition which user created using EDB Postgres CREATE TABLE PARTITION Syntax. Let's modify the above definition of measurement table to use CREATE TABLE PARTITION SYNTAX as given below:

 

For more information on EDB Postgres pruning please refer to the following link.

 

 

This post originally appeared on Vibhor's personal blog.