Parameter · Query Tuning / Planner Method Configuration
enable_partitionwise_join
Enables partitionwise join.
- Default:
off - Type: bool
- Applies: Session (any user)
In plain words
If two tables are partitioned the same way (say both by month), PostgreSQL can join January with January, February with February, and so on, instead of joining two huge tables. Each small join is cheaper. It is off by default because it makes planning slower.
For DBAs and operators
Partitionwise join requires both sides to be partitioned on the join key with matching bounds (since PostgreSQL 13, bounds need not be identical as long as partitions can be matched). The join must include equality on all partition keys. The plan becomes an Append of per-partition joins, each with a small hash table, and each pair can be pruned or run by different parallel workers.
Measured on 18.6 with two tables of four matching partitions, joined on (id, d) in SELECT count(*) (no parallelism): default Hash Join of two Appends, cost 31,596; with enable_partitionwise_join = on: an Aggregate over an Append of four Hash Joins, cost 21,407.
Costs: planning time and memory grow with the number of partition pairs, which is why it defaults to off. It pays off for large analytic joins between co-partitioned tables, for example orders and order_lines both partitioned by month. When off, these paths are never generated.
How to change it
Any user can change it for their session with SET.
See it in context
Related parameters
enable_partitionwise_aggregate enable_partition_pruning enable_hashjoin work_mem