Partition Pruning in PostgreSQL and Checking It Works

Partitioning is a promise that queries touch only the data they need. Pruning is how the promise is kept. It is also the first thing to verify after partitioning a table, because a query that quietly reads every partition has all the costs of partitioning and none of the benefits. This article explains when pruning happens, how to see it in a plan, the ways it fails and a debugging checklist. It belongs to the series that table partitioning in PostgreSQL that actually pays off opens.

What pruning is

Each partition has a bound: a date range, a set of list values or a hash modulus and remainder. When the planner sees a condition on the partition key in the WHERE clause, it checks the condition against each partition's bound and discards partitions where the combination is impossible. For WHERE occurred_at >= '2026-04-10' AND occurred_at < '2026-04-17', partitions covering March and May can never contain a matching row, so only April remains.

Pruning is on by default (enable_partition_pruning = on). It applies to SELECT, UPDATE and DELETE, and a pruned partition is neither scanned nor locked by the executor beyond what planning needs. Pruned partitions do not appear in the plan (plan time) or appear with their subplans removed (run time).

Two moments: plan time and run time

Plan time pruning happens when the planner knows the values: literals, constants folded from immutable expressions ('2026-04-10'::timestamptz, date '2026-04-10' + 7). The plan lists only the surviving partitions:

EXPLAIN SELECT * FROM event WHERE occurred_at >= '2026-04-10' AND occurred_at < '2026-04-17';

Seq Scan on event_2026_04 event  (cost=0.00..1500.00 rows=40000 width=64)
  Filter: ((occurred_at >= '2026-04-10') AND (occurred_at < '2026-04-17'))

Run time pruning happens when the values are known only at execution: parameters of a prepared statement, stable functions such as now() and current_date, and values from the outer side of a nested loop join. The plan contains all candidate partitions, and the executor removes those that cannot match once values are known. EXPLAIN alone shows all partitions with a note; EXPLAIN ANALYZE shows Subplans Removed:

EXPLAIN (ANALYZE) SELECT * FROM event WHERE occurred_at >= now() - interval '7 days';

Append  (actual time=0.05..4.2 rows=120000 loops=1)
  Subplans Removed: 22
  ->  Seq Scan on event_2026_04 ...
  ->  Seq Scan on event_2026_05 ...

Here 22 of 24 partitions were removed at execution time, and two scanned. For prepared statements with generic plans, run time pruning is the mechanism that gives the benefit: the plan covers all partitions and prunes per execution.

Operators and key types

Pruning understands the btree comparison operators (=, <, <=, >, >=, BETWEEN) for range partitioning, = and IN for list partitioning, and equality for hash partitioning. It handles AND and OR combinations, IS NULL, and ANY (array) comparisons. It does not prune on LIKE, regular expressions, <> or arbitrary functions of the key. Partition by an expression (PARTITION BY RANGE (date_trunc('day', ts))) and the query must use the same expression to prune.

What defeats pruning

A function applied to the key. WHERE date(occurred_at) = '2026-04-12' or WHERE extract(month FROM occurred_at) = 4 cannot be compared to partition bounds, so all partitions are scanned. Rewrite as a range: occurred_at >= '2026-04-12' AND occurred_at < '2026-04-13'.

A type mismatch that adds a cast to the key. Comparing a timestamptz key with a timestamp value or text can place a cast on the key column. Cast the constant instead, and compare like types. A cross-type comparison between timestamptz and date is handled with a stable conversion, so it prunes at run time, not plan time.

No condition on the key. A query filtering only on user_id reads every partition. If queries must stay this way, partitioning does not suit them.

A join condition instead of a filter. JOIN d ON e.occurred_at = d.day can prune at run time through a nested loop (Subplans Removed) but not through a hash join; PostgreSQL does not use join conditions to prune with hash joins (partition-wise join helps in other ways). Add an explicit range filter on the key when you know it.

Volatile functions. random() or a volatile user function in the comparison prevents pruning.

Stale constraints on a default partition. A default partition cannot be pruned when other partitions' values might leak into it; if the default is present, queries on ranges not covered by defined partitions must scan it. Keep it empty and small.

Disabled setting. SET enable_partition_pruning = off in a session or role.

Too complex expressions. OR across different columns, correlated subqueries and CTE boundaries (materialized) can hide the condition. A CTE that materializes the unfiltered table before filtering does not prune.

Reading a plan for pruning

Check these in EXPLAIN output:

1. Which Seq Scan or Index Scan nodes name partitions? Count them against the expected number.

2. Is there an Append or Merge Append node with many children (not pruned) or one child (pruned, the Append may disappear entirely)?

3. In EXPLAIN ANALYZE, is Subplans Removed present, and does the number match expectation?

4. Do the partitions listed contain the date range you filtered?

5. In nested loops, does the inner Append show (never executed) for partitions skipped? That is the run time pruning of the inner side.

For parameterized queries, test both the custom plan (literals) and the generic plan: EXPLAIN (GENERIC_PLAN) SELECT ... WHERE occurred_at >= $1 in version 16 and later shows the generic plan, where run time pruning must do the work. Use PREPARE and EXPLAIN EXECUTE in older versions.

Planning cost

Even when pruning works, the planner starts with all partitions. Before pruning, it must open the partitioned table's partition descriptor; it prunes using the bounds (which is fast, a binary search for range and list) and then plans only survivors. In recent versions this is much cheaper than it once was. Measure Planning Time in EXPLAIN (ANALYZE) for your main queries on the real partition count. A query on a table with 1,000 partitions that prunes to one still pays for more than a query on 24. Keep partition counts reasonable, and consider prepared statements for hot queries to amortize planning (generic plans with run-time pruning).

A debugging checklist

When a query reads more partitions than expected:

1. Run EXPLAIN with literals for the key. Does it prune? If yes, the issue is parameters or stable functions: use EXPLAIN ANALYZE and look for Subplans Removed.

2. Is there a condition on the partition key at all?

3. Is the key wrapped in a function, cast or expression?

4. Do the types match exactly, and are the constants typed correctly?

5. Is the condition inside a CTE or subquery that the planner cannot push into?

6. Is enable_partition_pruning on?

7. Are the partition bounds what you think? SELECT pg_get_expr(relpartbound, oid) FROM pg_class WHERE relname = '...'.

8. If using a join, give an explicit range filter.

Most failures are found in steps 2 to 4.

When pruning cannot help the query

If the query has no condition on the key, if it asks for all history, if it joins on a column other than the key with no range, or if it is a lookup by an id that does not include the key, pruning has nothing to prune. Those queries scan all partitions, and the remedy is either to change the query to include the key or to accept the cost. If many important queries are like that, the partition key is wrong for the workload, as partitioning mistakes that make queries slower explains. Pruning also cannot reduce planning time below a floor set by partition count, and it cannot help writes that spread across partitions.

What to do next

Take your five main queries on a partitioned table, run EXPLAIN (ANALYZE) and count the partitions each scans. Fix any that scan more than expected using the checklist. The manual covers the mechanism in partition pruning and the setting in planner method configuration. Next, read moving an existing table to partitions safely.