PolarDB:Partition pruning

Pruning during optimization

Immutable expressions—those based on constant values—can be evaluated at planning time. The planner prunes partitions before generating the execution plan.

The following example uses a measurement table partitioned by logdate across 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');

Query with a constant filter on the partition key:

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 Q1, Q2, and Q3 partitions are pruned at planning time. Only measurement_y2023q4 appears in the execution plan because DATE '2023-10-01' is a constant the planner can evaluate immediately.

To verify: Planning-time pruning is visible directly in the EXPLAIN output. Pruned partitions do not appear in the plan at all.

Pruning when the executor is initialized

Stable expressions—such as now()—cannot be evaluated at planning time because their value depends on when the query runs. The planner includes all potentially relevant partitions in the plan; actual pruning happens when the executor starts.

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)

Subplans Removed: 2 confirms that two partitions (Q1 and Q2) were pruned at executor initialization. If the query runs in July 2023, only the Q3 and Q4 partitions need to be scanned.

To verify: Look for Subplans Removed: N in the EXPLAIN output. This field indicates how many partitions were pruned during executor initialization.

Pruning when the executor is run

Volatile expressions—including subqueries, subjoins, and expressions of join conditions—cannot be evaluated until the executor runs. Pruning happens per execution, not at plan time or initialization.

EXPLAIN ANALYZE SELECT * FROM measurement WHERE logdate >= (SELECT to_date('2023-10-1'));

                                                      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)

(never executed) next to Q1, Q2, and Q3 confirms that run-time pruning eliminated those partitions.

To verify: Use EXPLAIN ANALYZE and look for (never executed) annotations on partition scans. The Subplans Removed field does not appear for run-time pruning—use (never executed) instead.