Verify that partition pruning works (plan time and run time)

Command

SHOW enable_partition_pruning;

-- Plan-time pruning: only the relevant partition should appear in the plan
EXPLAIN (COSTS OFF)
SELECT count(*) FROM sales WHERE created_at >= DATE '2026-03-01' AND created_at < DATE '2026-04-01';

-- Run-time pruning: generic plan / parameterized query, "Subplans Removed: N"
PREPARE q_sales(date) AS SELECT count(*) FROM sales WHERE created_at = $1;
SET plan_cache_mode = force_generic_plan;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) EXECUTE q_sales('2026-03-15');
RESET plan_cache_mode;
DEALLOCATE q_sales;

-- Pruning killer: a function on the key column scans every partition
EXPLAIN (COSTS OFF)
SELECT count(*) FROM sales WHERE date_trunc('month', created_at) = DATE '2026-03-01';

What it does

Compares plan-time pruning, run-time pruning with a generic plan ('Subplans Removed: N'), and how pruning is lost when a function is applied to the partition key.

When to use it

When queries on a partitioned table are slower than expected, the plan scans every partition, or the application uses an ORM/prepared statements.

Caution

Applying a function or cast to the partition key (date_trunc(created_at), created_at::text) disables pruning; write the condition as a range on the key. With a generic plan, pruning happens at executor startup and shows only as 'Subplans Removed'; for STABLE expressions such as now() it is also visible in EXPLAIN without ANALYZE. With enable_partition_pruning = off no pruning happens at all. With many partitions, measure Planning Time too.