Hash

Description

Builds the hash table consumed by a hash join.

Core node tag

T_Hash

Structured EXPLAIN Node Type

Hash

Inputs

The hash join inner child plan

Output

Hash table, not a normal tuple stream

Executor initializer

ExecInitHash

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.

It's time to begin hashing, or if we just arrived here then hashing is already underway, so join in that effort. While hashing we have to be prepared to help increase the number of batches or buckets at any time, and if we arrived here when that was already underway we'll have to help complete that work immediately so that it's safe to access batches and buckets below.

Make sure that any tuples we wrote to disk are visible to others before anyone tries to load them.

Elect one backend to disable any further growth. Batches are now fixed. While building them we made sure they'd fit in our memory budget when we load them back in later (or we tried to do that and gave up because we detected extreme skew).

Parallel Hash tries to use the combined hash_mem of all workers to avoid the need to batch. If that won't work, it falls back to hash_mem per worker and tries to process batches in parallel.

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: ExecHashEstimate, ExecHashInitializeDSM, ExecHashInitializeWorker, ExecHashRetrieveInstrumentation.

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

build hash table for hashjoin, doing partitioning if more than one batch is required.

We do not return the hash table directly because it's not a subtype of Node, and so would violate the MultiExecProcNode API. Instead, our parent Hashjoin node is expected to know how to fish it out of our node state. Ugly but not really worth cleaning up, since Hashjoin knows quite a bit more about Hash besides that.

parallel-oblivious version, building a backend-private hash table and (if necessary) batch files.

Get all tuples from the node below the Hash node and insert into the hash table (or temp files).

Account for the buckets in spaceUsed (reported in EXPLAIN ANALYZE)

EXPLAIN identity in core source

case T_Hash:
			pname = sname = "Hash";
			break;

Related entries

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

Documentation and source

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

Compare versions

PostgreSQL 10 → 11: changed.

--- PostgreSQL 10
+++ PostgreSQL 11
@@ -2,7 +2,12 @@
   "initializer": "ExecInitHash",
   "memory_mechanism": "hash-batches",
   "node_tag": "T_Hash",
-  "parallel_callbacks": [],
+  "parallel_callbacks": [
+    "ExecHashEstimate",
+    "ExecHashInitializeDSM",
+    "ExecHashInitializeWorker",
+    "ExecHashRetrieveInstrumentation"
+  ],
   "partial_modes": [],
   "strategies": [],
   "text_names": [

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

• Hash JoinHashJoin
• Merge JoinMergeJoin
• Nested LoopNestLoop