Partition pruning
Scanning every partition of a large partitioned table wastes I/O and slows queries — even when most partitions cannot contain matching rows. Partition pruning solves this: the planner evaluates the WHERE clause against each partition's definition and excludes any partition whose range or list cannot satisfy the condition. Excluded partitions are never scanned, which reduces disk I/O and shortens query execution time.
How it works
When partition pruning is enabled, the planner evaluates the conditional expression in the WHERE clause to determine which partitions to include in the query plan. The evaluation happens at different points depending on the expression type:
Immutable expressions (for example, a constant like
DATE '2023-10-01') are evaluated during plan optimization. The planner prunes partitions before execution begins.Stable expressions (for example,
now()) cannot be resolved at plan time but can be resolved at executor initialization. The planner includes all partitions in the plan, then prunes before scanning starts.Volatile expressions (for example, subqueries or
random()) can only be resolved at executor runtime. Pruning happens during query execution.
PolarDB for PostgreSQL supports two pruning modes based on when the WHERE clause value is known:
Static pruning: occurs at plan time when the partition key value is a constant. Pruned partitions do not appear in the query plan at all.
Dynamic pruning: occurs at executor initialization or runtime when the value depends on a function or subquery.
Partition pruning vs. constraint exclusion
Both partition pruning and constraint exclusion reduce the number of partitions scanned, but they differ in scope and capability:
| Partition pruning | Constraint exclusion | |
|---|---|---|
| Partition awareness | Understands relationships between partitions in a partitioned table | Treats each partition independently; checks all partition constraints separately |
| Optimizer stage | Runs early in the optimizer stage | Runs later in the optimizer stage |
Pruning at each stage
The following examples use a range-partitioned table to show how pruning works at each stage and how to identify the results in EXPLAIN output.
Set up the example table
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');Pruning during optimization
Use a constant value in the WHERE clause to trigger pruning at plan time. The pruned partitions are omitted entirely from the query plan.
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)The first three quarterly partitions (measurement_y2023q1 through measurement_y2023q3) do not appear in the plan. Because DATE '2023-10-01' is an immutable constant, the planner determines at plan time that those partitions cannot contain matching rows and removes them before generating the plan.
Pruning at executor initialization
Use a stable expression like now() to trigger pruning at executor initialization. The plan includes all partitions initially, but the executor removes ineligible ones before scanning.
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)How to identify this in the output: Look for the Subplans Removed field in the Append node. A value of 2 means two partitions were pruned at executor initialization. In this example, assuming the current date is in July 2023, the first two quarterly partitions are removed before any scan runs.
Pruning at executor runtime
Use a volatile expression such as a subquery to trigger pruning at executor runtime. Run EXPLAIN ANALYZE to see which partitions were actually executed.
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)How to identify this in the output: Look for (never executed) next to each Seq Scan node in the EXPLAIN ANALYZE output. Partitions marked (never executed) were pruned at runtime. In this example, the subquery (select to_date('2023-10-1', 'YYYY-MM-DD')) is evaluated at runtime and resolves to a date in Q4, so only measurement_y2023q4 is scanned.