Claw Blogs
PostgreSQL Deep Dive

PostgreSQL Deep Dive: Partition Pruning — Planning Time vs Execution Time

postgresql partitioning partition-pruning query-planning performance

You partition a table by date, write a query filtering on that date column, and PostgreSQL only scans the one partition that holds the relevant data. This is partition pruning, and it works because the planner can prove that certain partitions cannot contain matching rows based on the partition bounds and the query’s WHERE clause.

What most people don’t realize is that this pruning happens in two distinct phases: planning time and execution time. The two phases have different capabilities, different limitations, and different diagnostic techniques. Understanding the difference is what separates “it’s fast because partitions” from knowing why a specific query is slow and how to fix it.

The setup

To show the difference concretely, I’ll use a range-partitioned table that mirrors a common pattern: time-series data partitioned by month.

CREATE TABLE measurement (
    city_id     int not null,
    logdate     date not null,
    peaktemp    int,
    unitsales   int
) PARTITION BY RANGE (logdate);

CREATE TABLE measurement_y2006m02
    PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

CREATE TABLE measurement_y2006m03
    PARTITION OF measurement
    FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');

-- ... more monthly partitions through 2008 ...
CREATE TABLE measurement_y2008m01
    PARTITION OF measurement
    FOR VALUES FROM ('2008-01-01') TO ('2008-02-01');

Each partition’s bounds are stored internally. When the planner evaluates a query against measurement, it checks the WHERE clause against these bounds and eliminates partitions that cannot contain matching rows.

Planning-time pruning

Planning-time pruning happens when PostgreSQL generates the query plan, before any execution begins. The planner examines the WHERE clause, the partition bounds, and proves (statically) which partitions are irrelevant.

SET enable_partition_pruning = on;

EXPLAIN SELECT count(*) FROM measurement
WHERE logdate >= DATE '2008-01-01';
                                 QUERY PLAN
--------------------------------------------------------------------------
 Aggregate  (cost=37.75..37.76 rows=1 width=8)
   -> Seq Scan on measurement_y2008m01  (cost=0.00..33.12 rows=617 width=0)
         Filter: (logdate >= '2008-01-01'::date)

One partition scanned. Every earlier partition was pruned. This is planning-time pruning at work.

For comparison, here’s the same query with pruning disabled:

SET enable_partition_pruning = off;

EXPLAIN SELECT count(*) FROM measurement
WHERE logdate >= DATE '2008-01-01';
                                 QUERY PLAN
--------------------------------------------------------------------------
 Aggregate  (cost=188.76..188.77 rows=1 width=8)
   -> Append  (cost=0.00..181.05 rows=3085 width=0)
         -> Seq Scan on measurement_y2006m02  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         -> Seq Scan on measurement_y2006m03  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         -- ... every partition, including ones that can't match ...
         -> Seq Scan on measurement_y2008m01  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)

Every partition gets a Seq Scan node under the Append. The cost jumps from ~37 to ~188 because the planner assumes it might need to visit all of them.

Planning-time pruning works because the filter value is a literal constant. The planner can evaluate DATE '2008-01-01' at plan time, compare it against each partition’s range bounds, and prove that measurement_y2006m02 (range '2006-02-01' to '2006-03-01') cannot contain any row with logdate >= '2008-01-01'.

What planning-time pruning can handle

Planning-time pruning works with any WHERE clause condition where the partition key is compared against a value the planner can evaluate during planning. This includes:

Literal constants, as shown above.

Immutable function calls on constants, like WHERE logdate >= date_trunc('month', DATE '2008-01-15'). The planner can evaluate date_trunc at plan time because it’s marked immutable.

Externally supplied parameters from PREPARE statements when the planner has access to the parameter values (custom plans). If you use EXECUTE stmt('2008-01-01'), and the planner generates a custom plan, it knows the parameter value and can prune at plan time.

AND combinations of prunable conditions. If you partition by (region, logdate) and query WHERE region = 'US' AND logdate >= '2008-01-01', the planner can prune on both dimensions.

What planning-time pruning cannot handle

Here’s where it breaks down. Planning-time pruning fails when the filter value is not known at plan time. Three common cases:

Generic plans for prepared statements. When PostgreSQL uses a generic plan for a prepared statement, it doesn’t have the parameter values. It must assume that any partition could match. With plan_cache_mode = force_generic_plan, this means zero pruning at plan time.

Subquery results. If your WHERE clause filters on a value from a subquery, the planner cannot evaluate the subquery during planning. WHERE logdate >= (SELECT max(logdate) FROM recent_imports) means the planner can’t prove anything about which partitions to skip.

Parameterized nested loop joins. When a partitioned table appears on the inner side of a nested loop join, the join key value changes with every outer row. The planner sees a parameterized value, not a constant, so it cannot prune during planning.

These are the cases where execution-time pruning takes over.

Execution-time pruning

Execution-time pruning happens after planning, while the query is actually running. PostgreSQL can evaluate conditions whose values only become known during execution, and use those values to skip partitions.

This happens at two sub-phases:

Initialization-time pruning

During the executor’s initialization phase, PostgreSQL can prune partitions using parameter values that are known by the time execution starts but weren’t available during planning. This covers the PREPARE/EXECUTE case: by the time the executor initializes, it has the parameter value, and it can prune before touching any data.

PREPARE monthly_count(date) AS
SELECT count(*) FROM measurement WHERE logdate >= $1;

EXPLAIN (ANALYZE, COSTS OFF) EXECUTE monthly_count('2008-01-01');

The output might show Subplans Removed: 24 in the EXPLAIN properties. Those 24 partitions were pruned during executor initialization. They don’t appear as scan nodes at all, and they don’t show up in EXPLAIN ANALYZE timing.

But here’s the subtlety: even though these partitions were pruned before any data was read, they were still locked at the beginning of execution. If you have hundreds of partitions and your query only touches one, you’re still acquiring locks on all of them. On a busy system with DDL operations happening concurrently, this can cause contention that you wouldn’t expect from a “pruned” plan.

Runtime pruning

During actual execution, PostgreSQL can prune partitions every time a parameterized value changes. This is where nested loop joins and subqueries come in.

Consider a query joining a partitioned table to a smaller table:

EXPLAIN (ANALYZE)
SELECT m.city_id, m.logdate, m.peaktemp
FROM measurement m
JOIN targets t ON m.city_id = t.city_id
WHERE t.is_active = true
  AND m.logdate >= t.start_date
  AND m.logdate < t.end_date;

If measurement is on the inner side of a nested loop join, t.start_date and t.end_date change for every row in targets. The executor re-evaluates which partitions to scan each time. Some partitions may never be touched, showing as (never executed) in EXPLAIN ANALYZE. Others may be scanned multiple times for different targets rows.

-> Append
     -> Seq Scan on measurement_y2008m01
           Filter: (logdate >= t.start_date AND logdate < t.end_date)
           Loops: 3
     -> Seq Scan on measurement_y2007m11
           Filter: (logdate >= t.start_date AND logdate < t.end_date)
           Loops: 1
     -> Seq Scan on measurement_y2006m02
           Filter: (logdate >= t.start_date AND logdate < t.end_date)
           (never executed)

The Loops column tells you how many times each partition was actually entered. (never executed) means the partition was pruned in every iteration. This is execution-time pruning working correctly.

The diagnostic difference

The two pruning phases require different diagnostic approaches. If you only look at EXPLAIN (without ANALYZE), you’re only seeing planning-time pruning. Execution-time pruning is invisible in a plain EXPLAIN because it hasn’t happened yet.

Planning-time pruning: Look at which partitions appear in the EXPLAIN output. If a partition doesn’t appear, it was pruned during planning. Toggle enable_partition_pruning = off to see the unpruned plan and count what’s missing.

Initialization-time pruning: Look for the Subplans Removed: N property in EXPLAIN output. This tells you how many partitions were pruned between planning and execution start. The EXPLAIN (SETTINGS) output can confirm that enable_partition_pruning is on.

Runtime pruning: You need EXPLAIN ANALYZE. Check the Loops column on each partition’s scan node. A value of (never executed) means that partition was pruned every time the executor considered it. Different partitions showing different loop counts means runtime pruning is working, selecting different partitions for different parameter values.

When pruning fails: common causes

Partition pruning requires the planner to prove that a partition cannot contain matching rows. When the proof fails, the partition gets scanned even if it’s empty. Here are the most common reasons this happens.

OR conditions. Pruning works on AND conditions but generally not on OR. WHERE logdate = '2008-01-15' OR city_id = 3 can prune on the first condition but not the second (if partitioning is only by logdate). The OR means the planner must consider that any partition might match through the city_id condition. PostgreSQL 14 improved this by supporting partition pruning for OR clauses in some cases, but it’s not universal.

Type mismatches. If your partition key is date but you filter with WHERE logdate >= '2008-01-01' (a string literal without an explicit cast), the planner may not recognize the connection to the partition bounds. Always use explicit types: DATE '2008-01-01' or logdate >= '2008-01-01'::date.

Expression-based partition keys. If you partition by RANGE (date_trunc('month', logdate)), the planner must prove that your WHERE clause condition is equivalent to the partition bound expression. WHERE date_trunc('month', logdate) = '2008-01-01'::timestamp works, but WHERE logdate >= '2008-01-01' may not, because the planner can’t prove that truncating a date that’s >= January 1 produces >= January 1.

Immutable function requirements. The partition key expression must be immutable. If you try to partition by RANGE (logdate) but filter with WHERE logdate >= CURRENT_DATE, the planner cannot prune because CURRENT_DATE is stable, not immutable. Use a literal or a parameterized value instead.

Multi-column partition keys with partial filters. Partitioning by (city_id, logdate) and querying WHERE city_id = 5 will prune based on city_id but cannot prune on logdate because the partition bounds are compound ranges. The planner would need both columns in the WHERE clause to fully prune.

Constraint exclusion vs partition pruning

PostgreSQL has two mechanisms that sound similar but work differently. Partition pruning is the modern mechanism, built into declarative partitioning. It uses the internal partition bounds and can operate at both plan time and execution time.

Constraint exclusion is the older mechanism, designed for inheritance-based partitioning. It works by examining CHECK constraints on each child table. It only operates at plan time and is slower because it must parse and evaluate each CHECK constraint individually.

For declaratively partitioned tables, you should rely on partition pruning, not constraint exclusion. The constraint_exclusion setting defaults to partition, which means it only kicks in for inheritance-style queries. Setting it to on forces the planner to check CHECK constraints on all queries, which adds planning overhead for queries that don’t benefit.

There is one edge case where constraint exclusion can help with declarative partitioning. Since you can add explicit CHECK constraints to individual partitions, constraint exclusion might be able to prune a partition that the partition bounds alone can’t. This is rare and adds planning cost for every query, so it’s generally not worth it unless you have a specific partition that needs additional filtering logic.

The generic plan problem

The interaction between prepared statements and partition pruning is one of the more common sources of confusion.

When a prepared statement first executes, PostgreSQL may generate a custom plan using the actual parameter values. This plan gets full planning-time pruning. If the statement executes again with a different parameter value, PostgreSQL might reuse the generic plan instead, because the five-execution heuristic decided a generic plan is cheaper. The generic plan has no parameter values, so it gets no planning-time pruning. The query goes from scanning one partition to scanning all of them.

This is the same generic vs custom plan problem that affects all prepared statements, but partition pruning makes the cost difference much larger. With 100 partitions and generic plans, you might scan all 100 when only one is needed.

Monitor this with pg_prepared_statements in PostgreSQL 14+, which exposes generic_plans and custom_plans counters. If you see a prepared statement using many generic plans against a partitioned table, set plan_cache_mode = force_custom_plan for your session or for specific queries.

Partition count and planning overhead

Each partition that the planner considers adds to planning time. With planning-time pruning, most of those partitions are eliminated quickly, but the planner still has to evaluate each partition’s bounds. With 1,000 partitions, this evaluation takes real time.

PostgreSQL handles this reasonably well because the partition bound check is a simple range comparison, not a constraint parse like constraint exclusion uses. But there are practical limits. Tables with tens of thousands of partitions will have noticeably slower planning. The official documentation recommends keeping the number of partitions in the hundreds, not the thousands.

The effect compounds with generic plans. A generic plan must consider all partitions because it can’t prune. The planning time for a generic plan against a 500-partition table is measurably higher than for a custom plan against the same table. Add EXPLAIN (TIMING OFF, FORMAT TEXT) to your diagnostic queries to see planning time separately from execution time.

Monitoring pruning effectiveness

Beyond EXPLAIN ANALYZE, you can monitor pruning at scale with pg_stat_all_tables. After running a representative workload, check seq_scan and idx_scan counters on individual partitions. Partitions that should never be queried but show high scan counts are either not being pruned or are being accessed by queries without proper WHERE clauses.

The pg_stat_user_tables view is more useful here because it excludes system catalogs. Look for partitions with n_tup_ins > 0 (they contain data) but seq_scan and idx_scan both near zero (they’re being pruned successfully). If partitions with data show high scan counts that don’t match your expected query pattern, something is bypassing the pruning logic.

For a quick check, run a query with enable_partition_pruning = off and compare the EXPLAIN output to the normal plan. The difference in the number of subplans under the Append node tells you exactly how many partitions are being pruned. If the numbers are close, pruning isn’t helping and you should investigate the WHERE clause conditions.

What to take away

Planning-time pruning is fast and free. It happens before any locks are acquired (beyond the initial plan-phase locks) and before any data is read. When your WHERE clause contains literals or the planner has parameter values, this is what you get. The diagnostic is simple: look at EXPLAIN output and count the subplans.

Execution-time pruning is more expensive but covers cases that planning-time cannot. It handles prepared statements with unknown parameters, subquery-driven filters, and nested loop join parameters. The diagnostic requires EXPLAIN ANALYZE and attention to the Loops column and (never executed) annotations.

When partition pruning isn’t working, check for OR conditions, type mismatches, expression-based partition keys, and stable (not immutable) functions in the WHERE clause. For prepared statements, watch for generic plan usage. For nested loop joins, verify that runtime pruning is actually happening by checking the Loops column.

Partition pruning doesn’t require indexes on the partition key. The pruning is driven entirely by partition bounds, not by B-tree lookups. Whether you create indexes on partition key columns depends on what the query does within each surviving partition: scan a large fraction (skip the index) or select a few rows (add the index).