Partitionwise Join and Aggregate Plans #

When two partitioned tables are partitioned the same way on the same key, joining them partition-by-partition is strictly less work than joining the combined relations: each pair is smaller, each hash table fits in memory, and the pairs are independent. PostgreSQL can do this, but it will not unless you ask, because the planning cost is real.

This page shows what the enabled plan looks like, the conditions that must hold, and how to tell whether the trade paid off. It builds on the elimination mechanics in partition pruning analysis.

The Conditions #

enable_partitionwise_join applies only when the two relations are partition-compatible:

enable_partitionwise_aggregate has a related requirement: the GROUP BY must include the partition key, so that no group can span two partitions. When it does, each partition can be aggregated independently and the results simply appended.

Both settings default to off. The cost they add is planning: instead of costing one join, the planner costs one per partition pair, and with dozens of partitions and several join orders that multiplies quickly.

Where the Append sits with and without partitionwise join On the left, two Appends feed one hash join that spills into thirty-two batches. On the right, the Append is above the plan and each child is its own hash join with a single batch. OFF — one join over everything ON — one join per partition pair Hash Join · Batches: 32 Append events (12) Append sessions (12) 48M rows hashed at once → spills Execution Time: 42.6 s Append (12 children) Hash Join · Batches: 1 Hash Join · Batches: 1 … one per matching partition pair … 4M rows hashed per pair → fits in memory Execution Time: 11.9 s the Append moving above the join is the structural signature of a partitionwise plan

Annotated Evidence #

Without the setting, the join sits above two Append nodes and the hash spills:

EXPLAIN (ANALYZE)
SELECT e.account_id, count(*)
  FROM events e JOIN sessions s
    ON s.occurred_at = e.occurred_at AND s.session_id = e.session_id
 GROUP BY e.account_id;

Finalize HashAggregate  (actual time=42104.2..42418.9 rows=88142 loops=1)
  ->  Hash Join  (actual time=18220.4..39810.1 rows=4120334 loops=1)
        Hash Cond: (e.session_id = s.session_id AND e.occurred_at = s.occurred_at)
        ->  Append  (actual rows=48000000 loops=1)
        ->  Hash  (actual rows=12000000 loops=1)
              Batches: 32  Memory Usage: 65536kB       -- 31 spill rounds
Execution Time: 42610.8 ms

Enable it and the structure inverts:

SET LOCAL enable_partitionwise_join = on;
SET LOCAL enable_partitionwise_aggregate = on;

Append  (actual time=940.2..11702.4 rows=88142 loops=1)
  ->  HashAggregate  (actual rows=7340 loops=1)
        ->  Hash Join  (actual rows=343361 loops=1)
              Hash Cond: (e_1.session_id = s_1.session_id)
              ->  Seq Scan on events_2026_01 e_1  (actual rows=4001200 loops=1)
              ->  Hash  (actual rows=1002140 loops=1)
                    Batches: 1  Memory Usage: 5904kB    -- fits comfortably
  ->  HashAggregate  (actual rows=7118 loops=1)
        ->  Hash Join  (actual rows=341902 loops=1)  …
  …  ten more partition pairs …
Planning Time: 41.2 ms          -- up from 4.8 ms: this is the cost you pay
Execution Time: 11940.6 ms

Three things changed. The Append moved to the top. Every hash reports Batches: 1, so all the spilling disappeared. And Planning Time rose by an order of magnitude — small in absolute terms here, but decisive for a query executed thousands of times a minute.

Step-by-Step Workflow #

  1. Verify partition compatibility before spending time on plans:

    SELECT c.relname, pg_get_expr(c.relpartbound, c.oid) AS bounds
  FROM pg_class c JOIN pg_inherits i ON i.inhrelid = c.oid
 WHERE i.inhparent IN ('events'::regclass, 'sessions'::regclass)
 ORDER BY c.relname;

    Bounds must line up exactly, pair for pair.

  2. Enable both settings for the transaction and re-plan:

    BEGIN;
SET LOCAL enable_partitionwise_join = on;
SET LOCAL enable_partitionwise_aggregate = on;
EXPLAIN (ANALYZE, BUFFERS) SELECT … ;
ROLLBACK;

  3. Confirm the structure changed. If the Append is still below the join, compatibility failed silently — recheck bounds and the join condition.

  4. Check Batches on each per-partition hash. The whole benefit is those joins fitting in memory; if they still spill, the partitions are too coarse or work_mem is too small.

  5. Weigh the planning cost. Compare Planning Time in both plans and multiply by the statement’s real execution frequency before enabling anything globally.

  6. Enable per workload, not globally. Set it for the reporting role rather than in postgresql.conf:

    ALTER ROLE analytics SET enable_partitionwise_join = on;

  7. Re-verify after any partition change. Attaching a partition to one table but not the other silently disables the optimisation.

What changes when partitionwise join is enabled Three paired metrics: hash batches drop from thirty-two to one, execution time falls from forty-two seconds to twelve, and planning time rises from under five milliseconds to forty-one. hash batches execution time planning time 32 1 42.6 s 11.9 s 4.8 ms 41.2 ms off on — two metrics improve, one regresses

Before and After #

-- BEFORE
Hash Join above two Appends   Batches: 32   Memory Usage: 65536kB
Planning 4.8 ms · Execution 42610.8 ms

-- AFTER
Append above twelve Hash Joins  Batches: 1  Memory Usage: ~5900kB each
Planning 41.2 ms · Execution 11940.6 ms

The decisive metric is Batches, not time: eliminating 31 spill rounds is what produced the 3.6× improvement, and it is the reason the win holds under concurrency where temporary I/O contends.

Common Pitfalls #

Enabling it globally. Every partitioned query pays the planning cost, including the OLTP statements that gain nothing. Diagnostic signal: Planning Time rising across unrelated queries. Fix: set it per role or per transaction.

Mismatched bounds after a schema change. One extra or differently bounded partition disables the optimisation for the whole query, silently. Diagnostic signal: the plan reverts to a single join above two Appends with the setting on. Fix: compare bounds pair for pair.

Joining on only part of the partition key. A composite-key partition scheme needs equality on every key column. Diagnostic signal: the setting has no effect on a query that appears compatible. Fix: include all key columns in the join condition.

Assuming memory pressure disappears. Twelve partition joins running in parallel each claim their own allowance. Diagnostic signal: total memory use rising even as individual Batches fall. Fix: account for concurrency, as covered in tuning work_mem for hash aggregates.

Expecting partitionwise aggregation without the key in GROUP BY. Per-partition aggregation is only valid when no group can span partitions, which requires the partition key in the grouping columns. Diagnostic signal: enable_partitionwise_aggregate on with a single Finalize aggregate above one Append. Fix: include the partition key in GROUP BY, or accept the two-stage aggregate.

Enabling it on tables with hundreds of partitions. The planner’s work grows with the number of partition pairs, and planning memory grows with it. Diagnostic signal: planning time in seconds, or a planner memory spike during EXPLAIN. Fix: keep the optimisation for coarse partition schemes, and reduce partition counts where the granularity is not earning anything.

Leaving statistics stale on individual partitions. Each per-partition join is planned from that partition’s own statistics, so one neglected child produces one badly planned join inside an otherwise healthy Append. Diagnostic signal: eleven per-partition joins reporting one batch and the twelfth spilling. Fix: analyze partitions individually, as covered in autovacuum and plan stability.

All five must hold, or nothing happens Five requirements listed as a chain: same strategy, same partition count, identical bounds, matching key types, and equality on the full partition key in the join condition. samestrategy samepartition count identicalbounds matchingkey types join on thefull key one broken link and the plan silently reverts — with no warning anywhere

Frequently Asked Questions #

Why are partitionwise joins off by default? Because the planner must cost a join per matching partition pair rather than a single join, which raises planning time and memory. With many partitions that overhead can exceed the execution saving, so PostgreSQL makes it an explicit choice.

What exactly must match between the two tables? Partitioning strategy, partition count, exact bounds, and key data types — plus a join condition with equality on the full partition key. Any mismatch disables the optimisation without a warning.

Does a partitionwise plan use less memory? Per join, yes: each per-partition hash table is far smaller, so joins that spilled into many batches often complete in one. Total concurrent memory can still be high, particularly when parallel workers multiply the allowances.