enable_partitionwise_join

enable_partitionwise_join — Enables partitionwise join. Observed in PG11–19 Beta 3; its last measured boot default is off in PG19 Beta 3, with user context. This is a beta-snapshot fact and can change before PostgreSQL 19 GA.

Note

Fact — official short description: “Enables partitionwise join.”

Identity

Type , Valuebool: Upstream pg_settings type
Context , Valueuser: Settable by an ordinary user
Unit , Value—: Raw unit
Range , Value—: Raw limits in the last observed version
Enum values , Value—: — for non-enum types
Category , ValueQuery Tuning / Planner Method Configuration: Upstream classification
Latest boot value , Valueoff: off

Lifecycle

Fact	Value
First observed	PG11
Present in	PG11–19 Beta 3
Removed in	No
Introduction commit	`2fb1abaeb016` — Rename enable_partition_wise_join to enable_partitionwise_join
Commit date	2018-02-16
Discussion	thread 1

Default history

Measured PG9.0–19 Beta 3 boot defaults
Versions	Raw `boot_val`	Unit	Human value
PG11–19 Beta 3	`off`	—	off

How it works

enable_partitionwise_join controls whether the planner may choose joining one-to-one matching partitions when join keys contain compatible partition keys.

It is consulted while a plan is built. A session-level change is useful for comparing EXPLAIN alternatives, but an already cached plan is not retroactively rewritten; invalidation or replanning is required to observe a different choice.

It can multiply work_mem-limited nodes and planning effort roughly with the number of participating partitions. Its user context permits session- or transaction-local changes; newly performed or newly planned work sees the value.

Tuning advice

Tip

Advice. These are workload-specific starting points and must be validated with measurements.

Workload	Guidance
OLTP	Keep the upstream default off for routine OLTP. Test on at session scope only when both sides have one-to-one compatible partition layouts and the join includes every partition key; budget planning CPU and the work_mem-limited nodes created per partition.
OLAP	Partition-aligned analytical joins can improve with on, which is why the current Pigsty OLAP template enables it. Compare planning memory, planning time, total execution memory, and partition count—not execution time alone.
Small nodes	Leave off unless a specific partition-aligned join repeatedly benefits. Many partitions can make planning and per-node memory disproportionate on a small host even when each local join is efficient.

Pigsty

Values use the fixed 8-vCPU, 32-GiB, 100-GiB SSD fixture and render the current Pigsty templates for PG19 Beta 3; this does not assert current Pigsty support for that historical or beta release.

Template	Effective value	Versus upstream boot	Source expression
OLTP	Unmodified	—	—
OLAP	`on`	different	`'on'`
CRIT	Unmodified	—	—
TINY	Unmodified	—	—

Caution

Advice — pending human review. Fact from the current Pigsty template projection: OLTP: PG11–19 Beta 3 unmodified; OLAP: PG11–19 Beta 3 = on (dcs); CRIT: PG11–19 Beta 3 unmodified; TINY: PG11–19 Beta 3 unmodified. Advice, pending human review — Editorial inference, pending maintainer review: The OLAP-only override is intended to exploit matching partition layouts for analytical joins while avoiding wider planning and memory cost in other profiles.

Common pitfalls

Treating enable_partitionwise_join as a query hint even though it affects every plan built in its scope.
Testing an already cached prepared plan and concluding that the setting has no effect.
Masking stale statistics or cardinality errors by disabling a plan method globally.
It can multiply work_mem-limited nodes and planning effort roughly with the number of participating partitions.

enable_partitionwise_aggregate · enable_partition_pruning · work_mem · join_collapse_limit · max_parallel_workers_per_gather