Declarative partitioning comes with pruning built in: query one month of data and PostgreSQL scans one month of partitions. What it does not include is smarts about joins and aggregates across partitions. By default, PostgreSQL scans the matching partitions of each table, stacks them under an Append, and only then joins or aggregates the combined stream — even when the tables are partitioned identically and every row that could ever join lives in the matching partition on the other side. enable_partitionwise_join and enable_partitionwise_aggregate exist to close that gap, and both are off by default. Turning them on has made some of my queries dramatically faster. It has also made some slower, and once it blew up planning time so badly that the query spent longer planning than executing. Here is how to tell which side you are on.
The default plan shape, and why it wastes work
Without partitionwise planning, a join between two partitioned tables is planned as a join between two Appends. Pruning still happens — each Append contains only the partitions that survive the WHERE clause — but the join itself runs over the union of rows. The hash table is built over every matching row of the inner table's surviving partitions, even if the join key equals the partition key and the query touches a hundred partitions. The same story holds for aggregation: rows from every partition flow into one Aggregate node that maintains group state for all of them at once.
The memory picture alone justifies looking at the alternatives. A hash join over a hundred partitions' worth of inner rows wants a hundred partitions' worth of hash table, spilling to disk when it outgrows work_mem. An aggregate grouped by partition key keeps the whole grouping state in memory. Partitionwise planning attacks both by recognizing the structure you already declared in the schema.
enable_partitionwise_join: join the matching pairs
When enable_partitionwise_join is on and two partitioned tables join on their partition keys with compatible partition bounds — same boundaries, same types, effectively the same partitioning scheme — the planner considers joining the tables pairwise: partition A1 against partition B1, A2 against B2, and so on, then appending the results of those small joins. Each pairwise join is small, its hash table is small, and work_mem pressure per join drops by roughly the partition count. Combined with pruning, pairs that cannot produce rows never get planned at all.
SET enable_partitionwise_join = on;
EXPLAIN (COSTS OFF)
SELECT o.order_date, sum(o.total)
FROM orders o
JOIN order_events e
ON e.order_date = o.order_date
AND e.order_id = o.order_id
WHERE o.order_date >= date '2026-01-01'
GROUP BY o.order_date;The requirements are strict and worth reading twice: the join condition must include all partition keys, and the partition bounds must provably match pair-for-pair, or the planner rejects the scheme and you silently get the default plan. Tables partitioned "the same way" by human inspection but with different boundary expressions, different collations on the key, or a DEFAULT partition can disqualify the optimization. Check the plan shape, not the setting, to confirm it took effect — you want to see an Append of joins, not a join of Appends.
enable_partitionwise_aggregate: group where the data lives
The aggregate variant applies the same idea to GROUP BY. When the grouping keys include the partition key, each partition's groups are disjoint from every other partition's — a group for January in one partition can never merge with a January group elsewhere — so PostgreSQL can aggregate each partition independently and append the results. That is the full form. When the grouping keys leave out the partition key, the planner can still run a partial aggregate per partition and finalize the results above the Append, which already shrinks the intermediate state. Either way, the aggregate work per node drops from "every group in the table" toward "every group in one partition," which is often the difference between a hash aggregate that fits in memory and one that spills to disk in batches.
This flag stayed off by default through PostgreSQL 16 and 17 because it is not a universal win — which brings us to the honest half of this article.
The cost: planning time and memory scale with partitions
Partitionwise planning multiplies planning work by the partition count. Instead of planning one join, the planner considers plans for every surviving pair. With 365 daily partitions and no pruning, that is 365 join plans, each with its own path consideration, plus the executor state to hold them. On OLTP-ish queries against highly partitioned tables, enabling these flags globally is how you get 200-millisecond planning times on 5-millisecond queries. Planner memory is not free here either; I have seen planning memory for partitionwise joins on thousand-partition schemas reach into the hundreds of megabytes for a single query.
So the sane operating mode is per-query or per-role: leave both flags off globally, and SET them on for the reporting and ETL workloads that scan many partitions and aggregate or join heavily. That is where the feature pays — the query was going to touch hundreds of partitions anyway, planning time is noise next to execution time, and the memory savings on hash structures are the difference between fitting and spilling.
Pruning: plan time versus execution time
Pruning interacts with all of this and deserves precision, because the phases behave differently. Plan-time pruning removes partitions when the planner can prove from constants — a literal date in the WHERE clause — that they cannot match, and you see it as fewer plans under the Append. Execution-time pruning, with enable_partition_pruning on (the default), handles values that only appear once execution starts, and it comes in two moments. Values known at executor startup — prepared-statement arguments, initplan results — prune during initialization and show up as "Subplans Removed" on the Append. Values that only exist mid-run, like nested-loop parameters, prune during execution itself; those you diagnose through the loops counter in EXPLAIN ANALYZE, where pruned partitions show zero loops and a "(never executed)" marker. Execution-time pruning is what keeps generic prepared plans viable on partitioned tables; without it, every execution would scan everything the plan could not rule out at plan time.
Only plan-time pruning lightens the planner's share of partitionwise work: fewer surviving partitions means fewer pairwise joins to consider, which is exactly the lever that makes enable_partitionwise_join affordable on big schemas. Execution pruning still saves real executor work, but it happens after planning, so it cannot shrink the pairwise joins the planner already built. Prune at plan time first, then go partitionwise on what remains — that ordering is the whole tuning strategy in one sentence.
When it backfires
From experience, the failure cases repeat. Many partitions — hundreds to thousands — with little data per partition: planning overhead dominates, and each pairwise join was trivially cheap anyway. Partition bounds that almost but not quite align between the two tables: the feature silently does nothing while everyone believes it is on. Queries that touch one or two partitions per execution: partitionwise adds planning cost to save memory you were never going to use. And mixed workloads where a global SET leaks into latency-sensitive paths. The fix in every case is the same: enable it narrowly, verify the plan shape changed, and measure both execution time and planning time. pg_stat_statements tracks planning time separately since PostgreSQL 13, which makes the regression measurable rather than a feeling.
For a deeper tour of how the planner weighs these alternatives, our EXPLAIN ANALYZE guide is the right companion, and the comparison pages show how other engines approach partition elimination.
Measuring the effect with MonPG
Every partitionwise toggle is a plan-shape change, and plan-shape changes are where slow regressions hide. That is exactly the kind of before-and-after MonPG is built to catch: it monitors PostgreSQL in production today, keeping query runtime and planning-time history from pg_stat_statements alongside the plans themselves. You can see the day a reporting query flipped from join-of-appends to append-of-joins — or the day planning time crept past execution time because someone enabled the flag globally. If partitioned reporting is a big part of your workload, the slow query monitoring workflow and the broader PostgreSQL monitoring surface are built for this kind of comparison.