PolarDB for PostgreSQL supports partition pruning to significantly improve query performance on partitioned tables.
Overview
PolarDB for PostgreSQL provides partition pruning. When this feature is enabled, the planner checks each partition's definition to determine whether it can be skipped. A partition is skipped if it contains no rows that match the WHERE clause. The planner then excludes, or prunes, these partitions from the query plan. Partition pruning significantly reduces the amount of data retrieved from disk and shortens processing time. This improves query performance and optimizes resource use.
Depending on the SQL statement, the PolarDB for PostgreSQL database supports static or dynamic pruning.
Static pruning occurs at compile-time. It uses partition information that is available before the query runs. An example is an SQL statement that contains a WHERE clause with a constant value for the partition key column.
Dynamic pruning occurs at runtime when the exact partitions to access are not known in advance. An example is an SQL statement that uses an operator or function in its WHERE clause.
Partition pruning affects the statistics of the pruned objects and the statement's execution plan.
Partition pruning limits the search scope to the partitions that might contain the required data. Both pruning techniques improve performance by removing partitions from the query's execution plan.
Differences between partition pruning and constraint exclusion
The differences between partition pruning and constraint exclusion are:
Partition pruning understands the relationships between partitions in a partitioned table. Constraint exclusion does not.
For example, when a query searches for a specific value in a list-partitioned table, partition pruning can determine that only one specific partition can hold that value. Constraint exclusion must check the constraints defined for every partition.
Partition pruning occurs early in the optimizer stage to reduce the number of partitions the planner must consider. Constraint exclusion occurs later in the optimizer stage.
Partition pruning at different stages
In PolarDB for PostgreSQL, conditional expressions are classified into three levels: Immutable, Stable, and Volatile. These three levels correspond to three types of pruning:
If the conditional expression value is immutable, such as a static constant value, pruning occurs early in the optimizer stage.
If the conditional expression value is stable, such as
now(), pruning occurs during the executor initialization phase.If the conditional expression is volatile, such as
random(), pruning occurs at executor runtime.
Pruning during optimization
The following example shows how pruning works during the optimization stage.
This example creates a table named measurement in PolarDB for PostgreSQL. It uses logdate as the partition key and creates four partitions: measurement_y2023q1, measurement_y2023q2, measurement_y2023q3, and measurement_y2023q4. These partitions correspond to the four quarters of 2023.
CREATE TABLE measurement(
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);
CREATE TABLE measurement_y2023q1 PARTITION OF measurement
FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');
CREATE TABLE measurement_y2023q2 PARTITION OF measurement
FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');
CREATE TABLE measurement_y2023q3 PARTITION OF measurement
FOR VALUES FROM ('2023-07-01') TO ('2023-10-01');
CREATE TABLE measurement_y2023q4 PARTITION OF measurement
FOR VALUES FROM ('2023-10-01') TO ('2024-04-01');When you query for data where logdate is greater than or equal to '2023-10-01' and use EXPLAIN to view the query plan, the plan shows that the partitions for the first three quarters are pruned. The query does not scan these partitions because their ranges do not satisfy logdate >= DATE '2023-10-01'. This is an example of partition pruning during the optimization stage. This is possible because the condition is on the partition key logdate, and the value in the conditional expression, DATE '2023-10-01', is static or immutable and can be calculated during optimization.
EXPLAIN SELECT * FROM measurement WHERE logdate >= DATE '2023-10-01';
QUERY PLAN
-----------------------------------------------------------------------------
Append (cost=0.00..34.09 rows=567 width=20)
-> Seq Scan on measurement_y2023q4 (cost=0.00..31.25 rows=567 width=20)
Filter: (logdate >= '01-OCT-23 00:00:00'::date)
(3 rows)Pruning at executor initialization
This example also uses the measurement table to show pruning at executor initialization.
As shown in the following query, the same measurement table is used, but the value in the conditional expression is changed from a static value to now(). The `now()` function returns a stable expression. It cannot be calculated during optimization, but it can be calculated when the executor initializes. Assume the current date is in July 2023. The plan shows that the first two quarterly partitions are pruned, as indicated by Subplans Removed: 2. Only the third and fourth quarter partitions remain. This is an example of partition pruning at executor initialization. The pruning occurs at this stage because the condition is on the partition key logdate, and the value in the conditional expression, now(), is stable.
EXPLAIN SELECT * FROM measurement WHERE logdate >= now();
QUERY PLAN
-----------------------------------------------------------------------------
Append (cost=0.00..153.34 rows=2268 width=20)
Subplans Removed: 2
-> Seq Scan on measurement_y2023q3 (cost=0.00..35.50 rows=567 width=20)
Filter: (logdate >= now())
-> Seq Scan on measurement_y2023q4 (cost=0.00..35.50 rows=567 width=20)
Filter: (logdate >= now())
(6 rows)
Pruning at executor runtime
This example also uses the measurement table to show pruning at executor runtime.
In the following query, the same measurement table is used, but the value in the conditional expression is changed to the volatile subquery (select to_date('2023-10-1', 'YYYY-MM-DD')). This value cannot be calculated during optimization or at executor initialization. It can only be calculated at executor runtime.
When you use EXPLAIN ANALYZE, the results show that the partitions for the first three quarters are marked as (never executed). This is an example of pruning at executor runtime, which applies to volatile expression values, subqueries, and join condition expressions. This type of pruning is necessary because although the condition in the SQL statement is on the partition key logdate, the value in the conditional expression, (select to_date('2023-10-1', 'YYYY-MM-DD')), is a volatile subquery that can only be calculated when the executor runs.
EXPLAIN ANALYZE SELECT * FROM measurement WHERE logdate >= (select to_date('2023-10-1', 'YYYY-MM-DD'));
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Append (cost=0.01..136.35 rows=2268 width=20) (actual time=0.067..0.068 rows=0 loops=1)
InitPlan 1 (returns $0)
-> Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.051..0.053 rows=1 loops=1)
-> Seq Scan on measurement_y2023q1 (cost=0.00..31.25 rows=567 width=20) (never executed)
Filter: (logdate >= $0)
-> Seq Scan on measurement_y2023q2 (cost=0.00..31.25 rows=567 width=20) (never executed)
Filter: (logdate >= $0)
-> Seq Scan on measurement_y2023q3 (cost=0.00..31.25 rows=567 width=20) (never executed)
Filter: (logdate >= $0)
-> Seq Scan on measurement_y2023q4 (cost=0.00..31.25 rows=567 width=20) (actual time=0.004..0.004 rows=0 loops=1)
Filter: (logdate >= $0)