Hash Join

Description

Probes a hash table built from its inner input while reading its outer input.

Core node tag

T_HashJoin

Structured EXPLAIN Node Type

Hash Join

Inputs

Outer child and inner Hash plan

Output

Joined tuples according to the selected join type

Executor initializer

ExecInitHashJoin

Memory mechanism

hash-batches

EXPLAIN names and attributes

Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.

Text names recorded by this source: Hash.

Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node.

Memory and temporary storage

Hash-join execution can partition work into batches backed by temporary files. This describes hash-join batching, not the spill policy of aggregate or Memoize nodes.

If the inner side tuples of a hash join do not fit in memory, the hash join can be executed in multiple batches.

If the statistics on the inner side relation are accurate, planner chooses a multi-batch strategy and estimates the number of batches.

The query executor measures the real size of the hashtable and increases the number of batches if the hashtable grows too large.

The number of batches is always a power of two, so an increase in the number of batches doubles it.

Parallel execution and instrumentation

The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.

Callbacks in this build: ExecHashJoinEstimate, ExecHashJoinInitializeDSM, ExecHashJoinInitializeWorker, ExecHashJoinReInitializeDSM.

Same-version manual discussion

Here, the planner has chosen to use a hash join, in which rows of one table are entered into an in-memory hash table, after which the other table is scanned and the hash table is probed for matches to each row. Again note how the indentation reflects the plan structure: the bitmap scan on tenk1 is the input to the Hash node, which constructs the hash table. That's then returned to the Hash Join node, which reads rows from its outer child plan and searches the hash table for each one.

which shows that the planner thinks that hash join would be nearly 50% more expensive than merge join for this case. Of course, the next question is whether it's right about that. We can investigate that using EXPLAIN ANALYZE , as discussed below .

Here, the subplan is run a single time and its output is loaded into an in-memory hash table, which is then probed by the outer ANY operator. This requires that the sub- SELECT not reference any variables of the outer query, and that the ANY 's comparison operator be amenable to hashing.

In some cases EXPLAIN ANALYZE shows additional execution statistics beyond the plan node execution times and row counts. For example, Sort and Hash nodes provide extra information:

The Sort node shows the sort method used (in particular, whether the sort was in-memory or on-disk) and the amount of memory or disk space needed. The Hash node shows the number of hash buckets and batches as well as the peak amount of memory used for the hash table. (If the number of batches exceeds one, there will also be disk space usage involved, but that is not shown.)

Just as in a non-parallel plan, the driving table may be joined to one or more other tables using a nested loop, hash join, or merge join. The inner side of the join may be any kind of non-parallel plan that is otherwise supported by the planner provided that it is safe to run within a parallel worker. Depending on the join type, the inner side may also be a parallel plan.

Examples from this manual build

Example copied from the PostgreSQL 18.6 manual; it was not executed for this collection.

If we change the query's selectivity a bit, we might get a very different join plan:

EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;

                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Hash Join  (cost=226.23..709.73 rows=100 width=488)
   Hash Cond: (t2.unique2 = t1.unique2)
   ->  Seq Scan on tenk2 t2  (cost=0.00..445.00 rows=10000 width=244)
   ->  Hash  (cost=224.98..224.98 rows=100 width=244)
         ->  Bitmap Heap Scan on tenk1 t1  (cost=5.06..224.98 rows=100 width=244)
               Recheck Cond: (unique1 < 100)
               ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)
                     Index Cond: (unique1 < 100)

Example copied from the PostgreSQL 18.6 manual; it was not executed for this collection.

One way to look at variant plans is to force the planner to disregard whatever strategy it thought was the cheapest, using the enable/disable flags described in Section 19.7.1 . (This is a crude tool, but useful. See also Section 14.3 .) For example, if we're unconvinced that merge join is the best join type for the previous example, we could try

SET enable_mergejoin = off;

EXPLAIN SELECT *
FROM tenk1 t1, onek t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;

                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Hash Join  (cost=226.23..344.08 rows=10 width=488)
   Hash Cond: (t2.unique2 = t1.unique2)
   ->  Seq Scan on onek t2  (cost=0.00..114.00 rows=1000 width=244)
   ->  Hash  (cost=224.98..224.98 rows=100 width=244)
         ->  Bitmap Heap Scan on tenk1 t1  (cost=5.06..224.98 rows=100 width=244)
               Recheck Cond: (unique1 < 100)
               ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)
                     Index Cond: (unique1 < 100)

Executor implementation notes

This is based on the "hybrid hash join" algorithm described shortly in the following page

"An Adaptive Hash Join Algorithm for Multiuser Environments" Hansjörg Zeller; Jim Gray (1990). Proceedings of the 16th VLDB conference. Brisbane: 186–197.

If the inner side tuples of a hash join do not fit in memory, the hash join can be executed in multiple batches.

If the statistics on the inner side relation are accurate, planner chooses a multi-batch strategy and estimates the number of batches.

The query executor measures the real size of the hashtable and increases the number of batches if the hashtable grows too large.

EXPLAIN identity in core source

case T_HashJoin:
			pname = "Hash";		/* "Join" gets added by jointype switch */
			sname = "Hash Join";
			break;

Related entries

• EXPLAIN
• Using EXPLAIN
• Parallel plans
• enable_hashjoin
• work_mem

Documentation and source

• src/backend/commands/explain.c:1428
• src/backend/executor/execProcnode.c:307
• src/backend/executor/nodeHashjoin.c
• src/include/nodes/plannodes.h
• PostgreSQL 18.6 · using-explain
• PostgreSQL 18.6 · using-explain
• PostgreSQL 18.6 · parallel-plans

Compare versions

PostgreSQL 17 → 18: unchanged.

Compares recorded interfaces and attributes. Source fingerprints and build metadata are excluded; an absent sample is not proof of the introduction or removal release.

Related entries

• HashHash
• Merge JoinMergeJoin
• Nested LoopNestLoop