# 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 {#identity}

| Field | Value | Meaning |
| --- | --- | --- |
| Type | `bool` | Upstream pg_settings type |
| Context | `user` | Settable by an ordinary user |
| Unit | — | Raw unit |
| Range | — | Raw limits in the last observed version |
| Enum values | — | — for non-enum types |
| Category | Query Tuning / Planner Method Configuration | Upstream classification |
| Latest boot value | `off` | off |
{.fields meta="-"}

## Lifecycle {#lifecycle}

| Fact | Value |
| --- | --- |
| First observed | PG11 |
| Present in | PG11–19 Beta 3 |
| Removed in | No |
| Introduction commit | [`2fb1abaeb016`](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2fb1abaeb016aeb45b9e6d0b81b7a7e92bb251b9) — Rename enable_partition_wise_join to enable_partitionwise_join |
| Commit date | 2018-02-16 |
| Discussion | [thread 1](https://www.postgresql.org/message-id/flat/ad24e4f4-6481-066e-e3fb-6ef4a3121882%402ndquadrant.com) |

## Default history {#default-history}

| Versions | Raw `boot_val` | Unit | Human value |
| --- | --- | --- | --- |
| PG11–19 Beta 3 | `off` | — | off |
{.full-width caption="Measured PG9.0–19 Beta 3 boot defaults"}

## How it works {#mechanism}

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 {#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. |
{.full-width}

## Pigsty {#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 | — | — |
{.full-width}

> [!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 {#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.

## Related parameters {#related-parameters}

[`enable_partitionwise_aggregate`](/parameters/enable-partitionwise-aggregate/) · [`enable_partition_pruning`](/parameters/enable-partition-pruning/) · [`work_mem`](/parameters/work-mem/) · [`join_collapse_limit`](/parameters/join-collapse-limit/) · [`max_parallel_workers_per_gather`](/parameters/max-parallel-workers-per-gather/)

## References {#references}

- [PostgreSQL 19 Beta 3 — enable_partitionwise_join](https://www.postgresql.org/docs/19/runtime-config-query.html#GUC-ENABLE-PARTITIONWISE-JOIN)
- [PostgreSQL 19 release notes](https://www.postgresql.org/docs/19/release-19.html)
- [Machine-readable GUC export](/data/guc.jsonl)