enable_partitionwise_join — PostgreSQL configuration parameter

Enables or disables the query planner’s use of partitionwise join, which allows a join between partitioned tables to be performed by joining the matching partitions.

At a glance

What it does

Enables or disables the query planner’s use of partitionwise join, which allows a join between partitioned tables to be performed by joining the matching partitions. Partitionwise join currently applies only when the join conditions include all the partition keys, which must be of the same data type and have one-to-one matching sets of child partitions. With this setting enabled, the number of nodes whose memory usage is restricted by work_mem appearing in the final plan can increase linearly according to the number of partitions being scanned. This can result in a large increase in overall memory consumption during the execution of the query. Query planning also becomes significantly more expensive in terms of memory and CPU. The default value is off.

(Description quoted from the official PostgreSQL documentation.)

How to apply a change

Can be set per session with SET, per role/database with ALTER ROLE/DATABASE ... SET, or globally in postgresql.conf.

Inspect the current value and source with SHOW enable_partitionwise_join; or SELECT name, setting, unit, context, source FROM pg_settings WHERE name = 'enable_partitionwise_join';.

Tuning guidance

This is a diagnostic switch, not a production tuning knob. Turn it off briefly (per session) to confirm why the planner avoids or prefers a plan, then leave it on. Forcing it off in production masks bad estimates instead of fixing them — fix statistics, costs or indexes instead.

Reference

PostgreSQL documentation — enable_partitionwise_join.