Introduce parallel-aware hash joins that appear in EXPLAIN plans as Parallel Hash Join with Parallel Hash. While hash joins could already appear in parallel queries, they were previously always parallel-oblivious and had a partial subplan only on the outer side, meaning that the work of the inner subplan was duplicated in every worker.
After this commit, the planner will consider using a partial subplan on the inner side too, using the Parallel Hash node to divide the work over the available CPU cores and combine its results in shared memory. If the join needs to be split into multiple batches in order to respect work_mem, then workers process different batches as much as possible and then work together on the remaining batches.
The advantages of a parallel-aware hash join over a parallel-oblivious hash join used in a parallel query are that it:
- avoids wasting memory on duplicated hash tables
- avoids wasting disk space on duplicated batch files
- divides the work of building the hash table over the CPUs
One disadvantage is that there is some communication between the participating CPUs which might outweigh the benefits of parallelism in the case of small hash tables. This is avoided by the planner's existing reluctance to supply partial plans for small scans, but it may be necessary to estimate synchronization costs in future if that situation changes. Another is that outer batch 0 must be written to disk if multiple batches are required.
A potential future advantage of parallel-aware hash joins is that right and full outer joins could be supported, since there is a single set of matched bits for each hashtable, but that is not yet implemented.
A new GUC enable_parallel_hash is defined to control the feature, defaulting to on.
Author: Thomas Munro
1804284042 Add parallel-aware hash joins.
doc/src/sgml/config.sgml | 15 +
doc/src/sgml/monitoring.sgml | 62 +-
src/backend/executor/execParallel.c | 21 +
src/backend/executor/execProcnode.c | 3 +
src/backend/executor/nodeHash.c | 1659 +++++++++++++++++++++++--
src/backend/executor/nodeHashjoin.c | 617 ++++++++-
src/backend/nodes/copyfuncs.c | 1 +
src/backend/nodes/outfuncs.c | 1 +
src/backend/nodes/readfuncs.c | 1 +
src/backend/optimizer/path/costsize.c | 25 +-
src/backend/optimizer/path/joinpath.c | 36 +-
src/backend/optimizer/plan/createplan.c | 11 +
src/backend/optimizer/util/pathnode.c | 5 +-
src/backend/postmaster/pgstat.c | 45 +
src/backend/utils/misc/guc.c | 10 +-
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/include/executor/hashjoin.h | 175 ++-
src/include/executor/nodeHash.h | 24 +-
src/include/executor/nodeHashjoin.h | 6 +
src/include/nodes/execnodes.h | 6 +
src/include/nodes/plannodes.h | 1 +
src/include/nodes/relation.h | 2 +
src/include/optimizer/cost.h | 4 +-
src/include/optimizer/pathnode.h | 1 +
src/include/pgstat.h | 15 +
src/include/storage/lwlock.h | 1 +
src/test/regress/expected/join.out | 304 ++++-
src/test/regress/expected/sysviews.out | 3 +-
src/test/regress/sql/join.sql | 148 ++-
src/tools/pgindent/typedefs.list | 4 +
30 files changed, 3091 insertions(+), 116 deletions(-)