The sibling of enable_partitionwise_aggregate, and it shares that parameter’s defining trait: it defaults off, for the same reason. So rather than repeat the off-by-default argument in full, this post covers what’s different — the mechanism, a benefit the aggregate version doesn’t have, and a precondition strict enough to be the main reason the feature doesn’t fire when people expect it. Default off, context user. Like its sibling, enabling it is a real tuning decision, not a diagnostic probe.
Joining matching partitions
Join two partitioned tables the ordinary way and PostgreSQL appends all the partitions of each side into two complete relations, then joins those — one big hash or merge join over the whole thing. Partitionwise join, added in PostgreSQL 11, does it the other way around: it joins matching partitions pairwise and appends the results. Partition 1 of orders joins partition 1 of order_items, partition 2 with partition 2, and so on, and the per-pair join results concatenate through an Append.
This is valid because of what partitioning on the join key guarantees. If both tables are partitioned on the column being joined, then a row in partition 1 of one table can only have join partners in partition 1 of the other — the partition bounds ensure matching values land in corresponding partitions. So joining partition 1 against partition 1 finds every match that exists, and there’s no need to check partition 1’s rows against partition 2’s; they cannot possibly join. The big all-against-all join decomposes into a set of small independent joins that miss nothing.
The advantage this has that partitionwise aggregation doesn’t is worth calling out. Because each partition pair is joined as its own separate operation, the planner gets to choose the best join algorithm per pair, using statistics from the individual partitions rather than from the whole table. Smaller inputs mean more accurate per-partition statistics, and one pair might be best served by a hash join while another — perhaps one where a partition is small or well-indexed — is better as a nested loop with an indexed inner side. The planner picks each independently. Against a single whole-table join, which must commit to one algorithm for the entire thing, this per-pair freedom can produce a meaningfully better overall plan. The gain is largest when the partitions are foreign tables on different servers, where each partition-pair join can also be pushed to its own remote server.
The precondition that trips people up
The most common complaint about this parameter is that it’s enabled and the plan still shows a big whole-table join. The reason is almost always the precondition, which is stricter than people expect: partitionwise join applies only when the join conditions include all the partition keys, of the same data type, and the two tables have matching sets of child partitions. Miss any part of that and you get the ordinary join.
“Include all the partition keys” is the part people miss most. If both tables are partitioned by customer_id, the join must actually be ON a.customer_id = b.customer_id — a join on some other column, even between two partitioned tables, can’t use the technique, because the guarantee that matching rows share a partition only holds for the partition key. The tables also have to line up: originally, in PostgreSQL 11 and 12, their partition bounds had to match exactly — same boundaries, same number of partitions. PostgreSQL 13 relaxed this considerably with “advanced partition matching” (Ashutosh Bapat, Etsuro Fujita, Amit Langote, Tom Lane), which allows partitionwise join when each partition on one side has at most one matching partition on the other, so tables whose bounds align but aren’t byte-for-byte identical now qualify. If you’re on 11 or 12 and your bounds differ even slightly, that’s a reason the feature stays dormant that an upgrade to 13+ would fix.
Enabling it, and why it’s off
The off-by-default reasoning is identical to the aggregate sibling, so briefly: pushing the join into per-partition pairs multiplies the work_mem-bounded nodes in the plan by roughly the partition count, so peak memory can climb sharply, and evaluating all those per-pair join paths makes planning itself much more CPU- and memory-intensive — the planner’s work grows linearly in the number of partitions, and each partition pair is expensive to cost. The developers judged that too costly to impose on everyone, so you opt in.
Enable it — per-session, per-role, or per-database — when you have large partitioned tables joined on their partition keys and you’ve confirmed the pairwise plan is faster on your data. Set it, run EXPLAIN (ANALYZE), and look for the join happening below the Append, per partition pair, rather than a single join above a pair of Appends — and watch that the memory multiplied across your partition count is affordable at your concurrency. The reporting-role pattern fits again: ALTER ROLE reporting SET enable_partitionwise_join = on scopes the benefit and its memory cost to the workload that wants it. And note the two partitionwise parameters are frequently enabled together, since a query that joins partitioned tables and then aggregates the result benefits from pushing both operations down to the partition level. As with its sibling, the parameter is genuinely useful and genuinely off by default, because the memory multiplication it introduces is safe to accept deliberately for a known workload and unsafe to impose blindly on every query a server runs.