A parallel-query toggle that refines the hash join from enable_hashjoin, and it rewards a moment of precision, because “a hash join running in parallel” and “a parallel hash join” are two genuinely different things. Default on, context user, same family framing as enable_async_append: a diagnostic instrument, not a tuning knob.
Replicated hash table versus shared hash table
Recall the hash join: build a hash table from the smaller input, then stream the larger input through it, probing for matches. Now put that in a parallel plan, and there are two ways to handle the build.
The older way — a plain hash join that merely happens to run under parallel workers — has every worker build its own complete, identical copy of the hash table. All the workers scan the entire inner side, each constructing the same hash table in its own private memory, and then they divide up the outer side to probe in parallel. The probing is parallel, but the build is replicated: N workers do the same build N times, and each copy has to fit within that worker’s own work_mem × hash_mem_multiplier. If the inner side is large, that’s both wasteful (the same work done N times) and constraining (each private copy bounded by one worker’s memory budget).
A parallel hash join, added in PostgreSQL 11, does something better: the workers cooperate to build a single shared hash table in dynamically-allocated shared memory that all of them can read. Each worker scans part of the inner side, and together they populate one hash table exactly once. In EXPLAIN the tell is the node name — a Parallel Hash node under the Parallel Hash Join means the shared, cooperative build; a plain Hash node under a parallel Hash Join means the old replicated one.
Two advantages follow, and the second is the important one. First, the inner side is built once cooperatively instead of N times redundantly — less total work. Second, and more consequentially, because the hash table lives in shared memory the workers pool their memory allowances to build it: the budget becomes roughly work_mem × hash_mem_multiplier times the number of participating processes, all contributing to one table, rather than each worker confined to its own copy. That larger combined pool means a big inner side is far more likely to fit in memory in a single pass, avoiding the batch-and-spill-to-disk that a hash join falls into when the table won’t fit. On large joins — the kind parallel query exists for — this is frequently the difference between a fast single-pass shared build and a slow multi-batch one. Making all this work cooperatively required new synchronization machinery under the hood: a barrier primitive so the workers can agree on when the hash table is complete before any of them starts probing, since it’s not safe to probe a table another worker is still loading.
Symptoms that warrant flipping it
Parallel hash join earns its keep on large hash joins under parallel plans, exactly where building one shared table beats building many private ones. It’s irrelevant on small joins and on any plan that isn’t going parallel in the first place.
The diagnostic reason to flip it is the usual family probe. If a large parallel join is underperforming and EXPLAIN (ANALYZE) shows a Parallel Hash Join whose Parallel Hash node is spilling — Batches: greater than 1 with disk activity, meaning even the pooled shared memory wasn’t enough to hold the table in one pass — you can SET enable_parallel_hash = off for the session and compare. With it off, the planner falls back to a non-parallel-hash strategy (a replicated-build hash join, or a different join method entirely), and if that’s somehow faster, you’ve learned the parallel hash wasn’t helping. But far more often the spilling Parallel Hash is telling you the honest truth — the join is big — and the real fix is memory: raise work_mem or hash_mem_multiplier so the pooled shared table fits in one pass. Because the shared table pools per-worker budgets, work_mem here is multiplied not just by your connection count but by the workers per query, so raise it deliberately and watch total memory.
The other diagnostic is expecting a parallel hash join and not getting one. As with the rest of the parallel family, the switch won’t conjure it — a missing Parallel Hash Join usually means the planner costed it out (stale statistics, or parallel_setup_cost), or the query didn’t qualify for parallelism at all, which points at max_parallel_workers_per_gather and the min_parallel_*_scan_size thresholds rather than at this parameter.
As ever, enable_parallel_hash = off is a probe, not a setting. The shared-hash-table build is the right, and often dramatically faster, plan for large parallel hash joins, and disabling it forces every parallel worker back to building its own redundant copy under its own memory limit — the exact inefficiency the feature was written to remove. Diagnose with the switch, fix the memory or the statistics, and set it back.