Partition Pruning at Plan/Execution Time and Query Shape Requirements

Learning outcomes

Partitioning only helps a query when PostgreSQL can prove that some leaves cannot contain matching rows. That proof is partition pruning. It is based on partition bounds—not on whether the partition key has an index—and it can happen while the plan is built, when execution initializes, or repeatedly during execution as parameter values change.

1. Verify the Chapter 16 partitioned dataset

SELECT count(*) AS rowsFROM app.ch16_work_orders;SELECT relid::regclass AS relation, isleafFROM pg_partition_tree('app.ch16_work_orders'::regclass)ORDER BY relation::text;SHOW enable_partition_pruning;

If the objects are absent, run Lesson 1's bootstrap first. enable_partition_pruning defaults to on. Disabling it is useful for diagnosis, not a normal tuning strategy.

2. Plan-time pruning with constants

EXPLAIN (COSTS ON, VERBOSE, SETTINGS)SELECT count(*)FROM app.ch16_work_ordersWHERE opened_on >= DATE '2026-08-01'  AND opened_on <  DATE '2026-09-01';

The plan should contain only the August leaf (and possibly no DEFAULT leaf, because its bound is known to exclude the explicit August partition). Exact node choices depend on table statistics and costs. The correctness evidence is which child relations remain, not whether the surviving leaf uses an index or sequential scan.

BEGIN;SET LOCAL enable_partition_pruning = off;EXPLAIN (COSTS OFF)SELECT count(*)FROM app.ch16_work_ordersWHERE opened_on >= DATE '2026-08-01'  AND opened_on <  DATE '2026-09-01';ROLLBACK;

With pruning disabled, an Append may expose all leaves. No index was added or removed; the difference is partition-bound elimination.

3. Execution-time pruning with a generic prepared plan

A prepared statement can use parameter values that are unknown when a generic plan is constructed. PostgreSQL can retain an Append capable of pruning subplans during initialization or execution.

BEGIN;SET LOCAL plan_cache_mode = force_generic_plan;PREPARE ch16_month(date, date) ASSELECT count(*)FROM app.ch16_work_ordersWHERE opened_on >= $1  AND opened_on <  $2;EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON)EXECUTE ch16_month(DATE '2026-07-01', DATE '2026-08-01');DEALLOCATE ch16_month;ROLLBACK;

Look for Subplans Removed or child subplans shown as (never executed), depending on when pruning happens. A partition removed during executor initialization can disappear from the displayed child list while still contributing to initial locking behavior. Read the full plan rather than assuming “one leaf shown” always means plan-time pruning.

4. Wrong query shape: applying a function to the partition key

The business question “all rows opened during August” can be written in multiple equivalent ways. A function-wrapped key often prevents PostgreSQL from proving simple partition-bound contradiction.

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON)SELECT count(*)FROM app.ch16_work_ordersWHERE date_trunc('month', opened_on::timestamp)      = TIMESTAMP '2026-08-01 00:00:00';

This expression is semantically valid, but the partition key is no longer compared directly with constants using the bound's ordering semantics. The planner can therefore scan more leaves than necessary.

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON)SELECT count(*)FROM app.ch16_work_ordersWHERE opened_on >= DATE '2026-08-01'  AND opened_on <  DATE '2026-09-01';

The half-open range also handles month length cleanly and mirrors the partition definition. The rewrite changes query shape, not business semantics.

5. Cast direction matters

Do not “solve” parameter types by casting the partition key to text or another broad representation. Cast the parameter/literal into the partition key's type whenever that preserves semantics.

-- Avoid hiding the key behind a text cast:EXPLAIN (COSTS OFF)SELECT *FROM app.ch16_work_ordersWHERE opened_on::text = '2026-08-15';-- Prefer a value of the key's type:EXPLAIN (COSTS OFF)SELECT *FROM app.ch16_work_ordersWHERE opened_on = DATE '2026-08-15';

The second form gives both type checking and partition-bound reasoning a much cleaner expression.

6. Partitionwise aggregation: useful but not free

PostgreSQL can perform aggregation separately per partition and finalize later. The feature is disabled by default because the number of executor nodes—and nodes that may each consume up to work_mem—can grow with partition count.

BEGIN;EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON)SELECT opened_on, count(*)FROM app.ch16_work_ordersGROUP BY opened_on;SET LOCAL enable_partitionwise_aggregate = on;EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON, SETTINGS)SELECT opened_on, count(*)FROM app.ch16_work_ordersGROUP BY opened_on;ROLLBACK;

Do not enable it globally because one test plan looked attractive. Compare planning time, executor shape, buffers, memory-sensitive node count, and concurrency under the real workload.

7. Partitionwise joins require aligned partitioning

enable_partitionwise_join can let PostgreSQL join matching leaves directly when the join condition includes all partition keys and the child layouts are compatible. It is also off by default because planning CPU/memory and executor-node count can rise substantially.

SHOW enable_partitionwise_join;SHOW enable_partitionwise_aggregate;SHOW enable_partition_pruning;SELECT name, setting, sourceFROM pg_settingsWHERE name IN (  'enable_partition_pruning',  'enable_partitionwise_join',  'enable_partitionwise_aggregate',  'work_mem');

8. Pruning acceptance test

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON)SELECT count(*) FROM app.ch16_work_ordersWHERE opened_on >= DATE '2026-06-01' AND opened_on < DATE '2026-07-01';EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON)SELECT count(*) FROM app.ch16_work_ordersWHERE opened_on >= DATE '2026-07-01' AND opened_on < DATE '2026-08-01';EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON)SELECT count(*) FROM app.ch16_work_ordersWHERE opened_on >= DATE '2026-08-01' AND opened_on < DATE '2026-09-01';

Record planning time, execution time, surviving leaf names, buffers, and estimated versus actual rows. That becomes a regression test for later schema/query changes.

Authoritative references

Partitioning behavior is planner-, lock-, constraint-, and version-sensitive. These PostgreSQL 18 primary sources define the mechanisms used in this lesson.

• PostgreSQL 18 — Partition Pruning
• PostgreSQL 18 — Query Planning Settings
• PostgreSQL 18 — EXPLAIN