Partition Pruning and Joins

Partition Pruning and Partition-Wise Joins

Partition pruning skips partitions whose bounds cannot match the WHERE clause: while planning for constants, and at execution time for values known only then (parameters, subqueries, nested-loop outer rows).

454-pruning.sql: pruning at plan time and at run time, then a partition-wise joinSQL

EXPLAIN (COSTS OFF)                                       -- pruned while planning
SELECT sum(gross_amount) FROM mart.sales_m WHERE order_date >= '2026-06-01';
PREPARE last_week(date) AS
  SELECT sum(gross_amount) FROM mart.sales_m WHERE order_date >= $1;
SET plan_cache_mode = force_generic_plan;                 -- one plan for any $1
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
EXECUTE last_week('2026-06-24');                          -- pruned at run time
SET enable_partitionwise_join = on;
SET enable_partitionwise_aggregate = on;
EXPLAIN (COSTS OFF)
SELECT o.order_id, sum(i.qty) FROM p_orders o JOIN p_items i USING (order_id)
GROUP BY o.order_id;

Output

 Aggregate
   ->  Seq Scan on sales_2026_06 sales_m
...
         Subplans Removed: 17
         ->  Seq Scan on sales_2026_06 sales_m_1 (actual rows=1767.00 loops=1)
...
 Append
   ->  HashAggregate
         ->  Hash Join
               ->  Seq Scan on p_items_0 i
               ->  Hash
                     ->  Seq Scan on p_orders_0 o
...

The generic plan of last_week included all 18 partitions, since $1 was unknown; Subplans Removed: 17 shows the executor pruning them at startup. Pruning needs the key itself in the predicate: extract(month FROM order_date), or a join to dim_date filtered on year, prunes nothing at plan time.

A partition-wise join joins co-partitioned tables one matching pair at a time (p_orders_0 with p_items_0), so each hash table is a quarter of the size; partition-wise aggregation does the same for GROUP BY. Both are off by default because they multiply the plans considered: enable them per session and confirm the gain.

MENU