{"kind": "plan", "major": "18", "item": {"slug": "hash-join", "name": "Hash Join", "name_zh": "HashJoin", "category": "Join", "summary": "Probes a hash table built from its inner input while reading its outer input.", "aliases": ["Hash", "HashJoin", "T_HashJoin"], "content_hash": "7e608f95eb480ff225df08f08d773ba6dc855f0565b1bb5f6f3c457668a23b51", "versions": {"10": {"facts": [{"label": "Core node tag", "value": "T_HashJoin"}, {"label": "Structured EXPLAIN Node Type", "value": "Hash Join"}, {"label": "Inputs", "value": "Outer child and inner Hash plan"}, {"label": "Output", "value": "Joined tuples according to the selected join type"}, {"label": "Executor initializer", "value": "ExecInitHashJoin"}, {"label": "Memory mechanism", "value": "hash-batches"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v10.23/postgresql-10.23.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "d1045b55f1b48da754f3517988c725b5793f1e8106ac9399758198876f2e353f", "archive_sha256": "94a4b2528372458e5662c18d406629266667c437198160a18cdfd2c4a4d6eee9"}, {"url": "https://ftp.postgresql.org/pub/source/v10.23/postgresql-10.23.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "d1045b55f1b48da754f3517988c725b5793f1e8106ac9399758198876f2e353f", "archive_sha256": "94a4b2528372458e5662c18d406629266667c437198160a18cdfd2c4a4d6eee9"}], "mechanism": "hash-batches", "description": "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.", "source_notes": ["get the next outer tuple for hashjoin: either by executing the outer plan node in the first pass, or from the temp files for the hashjoin batches.", "Returns true if successful, false if there are no more batches.", "We no longer need the previous outer batch file; close it right away to free disk space.", "We can always skip over any batches that are completely empty on both sides. We can sometimes skip over batches that are empty on only one side, but there are exceptions:"]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Hash", "identity": "Hash Join"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=10", "label": "EXPLAIN"}, {"url": "/docs/10/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/10/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_hashjoin/?v=10", "label": "enable_hashjoin"}], "release": {"ref": "PostgreSQL 10.23 source archive", "label": "10.23", "major": "10", "channel": "historical", "revision": "94a4b2528372458e5662c18d406629266667c437198160a18cdfd2c4a4d6eee9", "source_url": "https://ftp.postgresql.org/pub/source/v10.23/postgresql-10.23.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v10.23/postgresql-10.23.tar.bz2", "line": 928, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:928", "sha256": "a785298532047cfeda969e78c3597a343dc1c56d61ba85830b0f16a02a14b5a1", "archive_sha256": "94a4b2528372458e5662c18d406629266667c437198160a18cdfd2c4a4d6eee9"}, {"url": "https://ftp.postgresql.org/pub/source/v10.23/postgresql-10.23.tar.bz2", "line": 300, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:300", "sha256": "cea76648bb38ae55f18f989768bee1a4ee025691ceea0f86bccb29dcdc166acc", "archive_sha256": "94a4b2528372458e5662c18d406629266667c437198160a18cdfd2c4a4d6eee9"}, {"url": "https://ftp.postgresql.org/pub/source/v10.23/postgresql-10.23.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "d1045b55f1b48da754f3517988c725b5793f1e8106ac9399758198876f2e353f", "archive_sha256": "94a4b2528372458e5662c18d406629266667c437198160a18cdfd2c4a4d6eee9"}, {"url": "https://ftp.postgresql.org/pub/source/v10.23/postgresql-10.23.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "d562c321108844798cd234303fffb618f13d4ee3f3a5ac79bfd963b077e47c22", "archive_sha256": "94a4b2528372458e5662c18d406629266667c437198160a18cdfd2c4a4d6eee9"}, {"url": "/docs/10/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 10.23 \u00b7 using-explain", "sha256": "a4b4304aedb0a2da0145cc7c35b03b319a5c7ee3df35a8ef84ab6d3a610f98f3"}, {"url": "/docs/10/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 10.23 \u00b7 using-explain", "sha256": "a4b4304aedb0a2da0145cc7c35b03b319a5c7ee3df35a8ef84ab6d3a610f98f3"}, {"url": "/docs/10/parallel-plans.html#PARALLEL-JOINS", "path": "parallel-plans.html", "label": "PostgreSQL 10.23 \u00b7 parallel-plans", "sha256": "cd37ed0ef7e2cf177707f50d9a7258574b4cefdcc087e6ee99a3bf377d960fcb"}], "node_tag": "T_HashJoin", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["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."]}, {"title": "Memory and temporary storage", "paragraphs": ["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.", "get the next outer tuple for hashjoin: either by executing the outer plan node in the first pass, or from the temp files for the hashjoin batches.", "Returns true if successful, false if there are no more batches.", "We no longer need the previous outer batch file; close it right away to free disk space.", "We can always skip over any batches that are completely empty on both sides. We can sometimes skip over batches that are empty on only one side, but there are exceptions:"]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["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: none extracted from this node implementation."]}, {"title": "Same-version manual discussion", "paragraphs": ["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.", "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. For example, if a nested loop join is chosen, the inner plan may be an index scan which looks up a value taken from the outer side of the join.", "Each worker will execute the inner side of the join in full. This is typically not a problem for nested loops, but may be inefficient for cases involving hash or merge joins. For example, for a hash join, this restriction means that an identical hash table is built in each worker process, which works fine for joins against small tables but may not be efficient when the inner table is large. For a merge join, it might mean that each worker performs a separate sort of the inner relation, which could be slow. Of course, in cases where a parallel plan of this type would be inefficient, the query planner will normally choose some other plan (possibly one which does not use parallelism) instead."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;\n\n QUERY PLAN\n------------------------------------------------------------------------------------------\n Hash Join (cost=230.47..713.98 rows=101 width=488)\n Hash Cond: (t2.unique2 = t1.unique2)\n -> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244)\n -> Hash (cost=229.20..229.20 rows=101 width=244)\n -> Bitmap Heap Scan on tenk1 t1 (cost=5.07..229.20 rows=101 width=244)\n Recheck Cond: (unique1 < 100)\n -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0)\n Index Cond: (unique1 < 100)", "source": {"url": "/docs/10/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 10.23 \u00b7 using-explain", "sha256": "a4b4304aedb0a2da0145cc7c35b03b319a5c7ee3df35a8ef84ab6d3a610f98f3"}, "paragraphs": ["Example copied from the PostgreSQL 10.23 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:"]}, {"code": "EXPLAIN ANALYZE SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous;\n\n QUERY PLAN\n--------------------------------------------------------------------------------------------------------------------------------------------\n Sort (cost=717.34..717.59 rows=101 width=488) (actual time=7.761..7.774 rows=100 loops=1)\n Sort Key: t1.fivethous\n Sort Method: quicksort Memory: 77kB\n -> Hash Join (cost=230.47..713.98 rows=101 width=488) (actual time=0.711..7.427 rows=100 loops=1)\n Hash Cond: (t2.unique2 = t1.unique2)\n -> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244) (actual time=0.007..2.583 rows=10000 loops=1)\n -> Hash (cost=229.20..229.20 rows=101 width=244) (actual time=0.659..0.659 rows=100 loops=1)\n Buckets: 1024 Batches: 1 Memory Usage: 28kB\n -> Bitmap Heap Scan on tenk1 t1 (cost=5.07..229.20 rows=101 width=244) (actual time=0.080..0.526 rows=100 loops=1)\n Recheck Cond: (unique1 < 100)\n -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0) (actual time=0.049..0.049 rows=100 loops=1)\n Index Cond: (unique1 < 100)\n Planning time: 0.194 ms\n Execution time: 8.008 ms", "source": {"url": "/docs/10/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 10.23 \u00b7 using-explain", "sha256": "a4b4304aedb0a2da0145cc7c35b03b319a5c7ee3df35a8ef84ab6d3a610f98f3"}, "paragraphs": ["Example copied from the PostgreSQL 10.23 manual; it was not executed for this collection.", "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:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["Note: the relation we build hash table on is the \"inner\" the other one is \"outer\".", "Reset per-tuple memory context to free any expression evaluation storage allocated in the previous tuple cycle.", "It's possible to iterate this loop many times before returning a tuple, in some pathological cases such as needing to move much of the current batch to a later batch. So let's check for interrupts each time through.", "If the outer relation is completely empty, and it's not right/full join, we can quit without building the hash table. However, for an inner join it is only a win to check this when the outer relation's startup cost is less than the projected cost of building the hash table. Otherwise it's best to build the hash table first and see if the inner relation is empty. (When it's a left join, we should always make this check, since we aren't going to be able to skip the join on the strength of an empty inner relation anyway.)", "If we are rescanning the join, we make use of information gained on the previous scan: don't bother to try the prefetch if the previous scan found the outer relation nonempty. This is not 100% reliable since with new parameters the outer relation might yield different results, but it's a good heuristic."]}, {"code": "case T_HashJoin:\n\t\t\tpname = \"Hash\";\t\t/* \"Join\" gets added by jointype switch */\n\t\t\tsname = \"Hash Join\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Probes a hash table built from its inner input while reading its outer input."], "evidence_kind": "source and documentation", "explain_names": ["Hash"], "partial_modes": [], "comparison_data": {"node_tag": "T_HashJoin", "strategies": [], "text_names": ["Hash"], "initializer": "ExecInitHashJoin", "partial_modes": [], "memory_mechanism": "hash-batches", "parallel_callbacks": []}, "comparison_hash": "733e61a55f51d5300ef22e028431f71e7183c94ce0719d23a50452dfa90acaf6", "explain_prefixes": ["Parallel"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeHashjoin.c"}, "parallel_callbacks": []}, "11": {"facts": [{"label": "Core node tag", "value": "T_HashJoin"}, {"label": "Structured EXPLAIN Node Type", "value": "Hash Join"}, {"label": "Inputs", "value": "Outer child and inner Hash plan"}, {"label": "Output", "value": "Joined tuples according to the selected join type"}, {"label": "Executor initializer", "value": "ExecInitHashJoin"}, {"label": "Memory mechanism", "value": "hash-batches"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "b85898c47ba59adaea352fd6f4d8ba5a3d7630820c35ae3e4875d6f3f00e3adf", "archive_sha256": "2cb7c97d7a0d7278851bbc9c61f467b69c094c72b81740b751108e7892ebe1f0"}, {"url": "https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "b85898c47ba59adaea352fd6f4d8ba5a3d7630820c35ae3e4875d6f3f00e3adf", "archive_sha256": "2cb7c97d7a0d7278851bbc9c61f467b69c094c72b81740b751108e7892ebe1f0"}], "mechanism": "hash-batches", "description": "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.", "source_notes": ["PHJ_BUILD_ELECTING -- initial state PHJ_BUILD_ALLOCATING -- one sets up the batches and table 0 PHJ_BUILD_HASHING_INNER -- all hash the inner rel PHJ_BUILD_HASHING_OUTER -- (multi-batch only) all hash the outer PHJ_BUILD_RUNNING -- building done, probing can begin PHJ_BUILD_DONE -- all work complete, one frees batches", "While in the phase PHJ_BUILD_HASHING_INNER a separate pair of barriers may be used repeatedly as required to coordinate expansions in the number of batches or buckets. Their phases are as follows:", "PHJ_GROW_BATCHES_ELECTING -- initial state PHJ_GROW_BATCHES_ALLOCATING -- one allocates new batches PHJ_GROW_BATCHES_REPARTITIONING -- all repartition PHJ_GROW_BATCHES_FINISHING -- one cleans up, detects skew", "If the planner got the number of batches and buckets right, those won't be necessary, but on the other hand we might finish up needing to expand the buckets or batches multiple times while hashing the inner relation to stay within our memory budget and load factor target. For that reason it's a separate pair of barriers using circular phases."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Hash", "identity": "Hash Join"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=11", "label": "EXPLAIN"}, {"url": "/docs/11/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/11/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_hashjoin/?v=11", "label": "enable_hashjoin"}, {"url": "/wiki/guc/work_mem/?v=11", "label": "work_mem"}], "release": {"ref": "PostgreSQL 11.22 source archive", "label": "11.22", "major": "11", "channel": "historical", "revision": "2cb7c97d7a0d7278851bbc9c61f467b69c094c72b81740b751108e7892ebe1f0", "source_url": "https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.bz2", "line": 1053, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1053", "sha256": "9df8400c1a4377179572ceb916d6020fca4e2760f74bf416d77ed97476523bbd", "archive_sha256": "2cb7c97d7a0d7278851bbc9c61f467b69c094c72b81740b751108e7892ebe1f0"}, {"url": "https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.bz2", "line": 300, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:300", "sha256": "95ef4d4a5df4c29f14af9763fae2c530449bdacdf3853d9ff297adf1fed6153b", "archive_sha256": "2cb7c97d7a0d7278851bbc9c61f467b69c094c72b81740b751108e7892ebe1f0"}, {"url": "https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "b85898c47ba59adaea352fd6f4d8ba5a3d7630820c35ae3e4875d6f3f00e3adf", "archive_sha256": "2cb7c97d7a0d7278851bbc9c61f467b69c094c72b81740b751108e7892ebe1f0"}, {"url": "https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "5e0511194183800e8d6eb293fd4b40639c7d3118e2d199c8e7865ba4fa4cf67f", "archive_sha256": "2cb7c97d7a0d7278851bbc9c61f467b69c094c72b81740b751108e7892ebe1f0"}, {"url": "/docs/11/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 11.22 \u00b7 using-explain", "sha256": "8411bc78085d4737e32d7cca103d859da6c6539e33ec5e2fba46e74c6f199d8a"}, {"url": "/docs/11/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 11.22 \u00b7 using-explain", "sha256": "8411bc78085d4737e32d7cca103d859da6c6539e33ec5e2fba46e74c6f199d8a"}, {"url": "/docs/11/parallel-plans.html#PARALLEL-JOINS", "path": "parallel-plans.html", "label": "PostgreSQL 11.22 \u00b7 parallel-plans", "sha256": "353df5869034b7665159a74037d9cf3e1800d15efcea3390daaa99aae89fa288"}], "node_tag": "T_HashJoin", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["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."]}, {"title": "Memory and temporary storage", "paragraphs": ["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.", "PHJ_BUILD_ELECTING -- initial state PHJ_BUILD_ALLOCATING -- one sets up the batches and table 0 PHJ_BUILD_HASHING_INNER -- all hash the inner rel PHJ_BUILD_HASHING_OUTER -- (multi-batch only) all hash the outer PHJ_BUILD_RUNNING -- building done, probing can begin PHJ_BUILD_DONE -- all work complete, one frees batches", "While in the phase PHJ_BUILD_HASHING_INNER a separate pair of barriers may be used repeatedly as required to coordinate expansions in the number of batches or buckets. Their phases are as follows:", "PHJ_GROW_BATCHES_ELECTING -- initial state PHJ_GROW_BATCHES_ALLOCATING -- one allocates new batches PHJ_GROW_BATCHES_REPARTITIONING -- all repartition PHJ_GROW_BATCHES_FINISHING -- one cleans up, detects skew", "If the planner got the number of batches and buckets right, those won't be necessary, but on the other hand we might finish up needing to expand the buckets or batches multiple times while hashing the inner relation to stay within our memory budget and load factor target. For that reason it's a separate pair of barriers using circular phases."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["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."]}, {"title": "Same-version manual discussion", "paragraphs": ["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.", "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.", "In a hash join (without the \"parallel\" prefix), the inner side is executed in full by every cooperating process to build identical copies of the hash table. This may be inefficient if the hash table is large or the plan is expensive. In a parallel hash join , the inner side is a parallel hash that divides the work of building a shared hash table over the cooperating processes."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;\n\n QUERY PLAN\n------------------------------------------------------------------------------------------\n Hash Join (cost=230.47..713.98 rows=101 width=488)\n Hash Cond: (t2.unique2 = t1.unique2)\n -> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244)\n -> Hash (cost=229.20..229.20 rows=101 width=244)\n -> Bitmap Heap Scan on tenk1 t1 (cost=5.07..229.20 rows=101 width=244)\n Recheck Cond: (unique1 < 100)\n -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0)\n Index Cond: (unique1 < 100)", "source": {"url": "/docs/11/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 11.22 \u00b7 using-explain", "sha256": "8411bc78085d4737e32d7cca103d859da6c6539e33ec5e2fba46e74c6f199d8a"}, "paragraphs": ["Example copied from the PostgreSQL 11.22 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:"]}, {"code": "EXPLAIN ANALYZE SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous;\n\n QUERY PLAN\n--------------------------------------------------------------------------------------------------------------------------------------------\n Sort (cost=717.34..717.59 rows=101 width=488) (actual time=7.761..7.774 rows=100 loops=1)\n Sort Key: t1.fivethous\n Sort Method: quicksort Memory: 77kB\n -> Hash Join (cost=230.47..713.98 rows=101 width=488) (actual time=0.711..7.427 rows=100 loops=1)\n Hash Cond: (t2.unique2 = t1.unique2)\n -> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244) (actual time=0.007..2.583 rows=10000 loops=1)\n -> Hash (cost=229.20..229.20 rows=101 width=244) (actual time=0.659..0.659 rows=100 loops=1)\n Buckets: 1024 Batches: 1 Memory Usage: 28kB\n -> Bitmap Heap Scan on tenk1 t1 (cost=5.07..229.20 rows=101 width=244) (actual time=0.080..0.526 rows=100 loops=1)\n Recheck Cond: (unique1 < 100)\n -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0) (actual time=0.049..0.049 rows=100 loops=1)\n Index Cond: (unique1 < 100)\n Planning time: 0.194 ms\n Execution time: 8.008 ms", "source": {"url": "/docs/11/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 11.22 \u00b7 using-explain", "sha256": "8411bc78085d4737e32d7cca103d859da6c6539e33ec5e2fba46e74c6f199d8a"}, "paragraphs": ["Example copied from the PostgreSQL 11.22 manual; it was not executed for this collection.", "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:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["Hash joins can participate in parallel query execution in several ways. A parallel-oblivious hash join is one where the node is unaware that it is part of a parallel plan. In this case, a copy of the inner plan is used to build a copy of the hash table in every backend, and the outer plan could either be built from a partial or complete path, so that the results of the hash join are correspondingly either partial or complete. A parallel-aware hash join is one that behaves differently, coordinating work between backends, and appears as Parallel Hash Join in EXPLAIN output. A Parallel Hash Join always appears with a Parallel Hash node.", "Parallel-aware hash joins use the same per-backend state machine to track progress through the hash join algorithm as parallel-oblivious hash joins. In a parallel-aware hash join, there is also a shared state machine that co-operating backends use to synchronize their local state machines and program counters. The shared state machine is managed with a Barrier IPC primitive. When all attached participants arrive at a barrier, the phase advances and all waiting participants are released.", "When a participant begins working on a parallel hash join, it must first figure out how much progress has already been made, because participants don't wait for each other to begin. For this reason there are switch statements at key points in the code where we have to synchronize our local state machine with the phase, and then jump to the correct part of the algorithm so that we can get started.", "One barrier called build_barrier is used to coordinate the hashing phases. The phase is represented by an integer which begins at zero and increments one by one, but in the code it is referred to by symbolic names as follows:", "PHJ_BUILD_ELECTING -- initial state PHJ_BUILD_ALLOCATING -- one sets up the batches and table 0 PHJ_BUILD_HASHING_INNER -- all hash the inner rel PHJ_BUILD_HASHING_OUTER -- (multi-batch only) all hash the outer PHJ_BUILD_RUNNING -- building done, probing can begin PHJ_BUILD_DONE -- all work complete, one frees batches"]}, {"code": "case T_HashJoin:\n\t\t\tpname = \"Hash\";\t\t/* \"Join\" gets added by jointype switch */\n\t\t\tsname = \"Hash Join\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Probes a hash table built from its inner input while reading its outer input."], "evidence_kind": "source and documentation", "explain_names": ["Hash"], "partial_modes": [], "comparison_data": {"node_tag": "T_HashJoin", "strategies": [], "text_names": ["Hash"], "initializer": "ExecInitHashJoin", "partial_modes": [], "memory_mechanism": "hash-batches", "parallel_callbacks": ["ExecHashJoinEstimate", "ExecHashJoinInitializeDSM", "ExecHashJoinInitializeWorker", "ExecHashJoinReInitializeDSM"]}, "comparison_hash": "f721dd250dc8d1d06a3d3dc814d77a301b3c9b8eb383affa6abbdd7a95c634df", "explain_prefixes": ["Parallel"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeHashjoin.c"}, "parallel_callbacks": ["ExecHashJoinEstimate", "ExecHashJoinInitializeDSM", "ExecHashJoinInitializeWorker", "ExecHashJoinReInitializeDSM"]}, "12": {"facts": [{"label": "Core node tag", "value": "T_HashJoin"}, {"label": "Structured EXPLAIN Node Type", "value": "Hash Join"}, {"label": "Inputs", "value": "Outer child and inner Hash plan"}, {"label": "Output", "value": "Joined tuples according to the selected join type"}, {"label": "Executor initializer", "value": "ExecInitHashJoin"}, {"label": "Memory mechanism", "value": "hash-batches"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v12.22/postgresql-12.22.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "8bc14e676bfe827dd9359a99526cf11b701e3c2b2002c5002ae2a2ad7dc8eaba", "archive_sha256": "8df3c0474782589d3c6f374b5133b1bd14d168086edbc13c6e72e67dd4527a3b"}, {"url": "https://ftp.postgresql.org/pub/source/v12.22/postgresql-12.22.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "8bc14e676bfe827dd9359a99526cf11b701e3c2b2002c5002ae2a2ad7dc8eaba", "archive_sha256": "8df3c0474782589d3c6f374b5133b1bd14d168086edbc13c6e72e67dd4527a3b"}], "mechanism": "hash-batches", "description": "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.", "source_notes": ["PHJ_BUILD_ELECTING -- initial state PHJ_BUILD_ALLOCATING -- one sets up the batches and table 0 PHJ_BUILD_HASHING_INNER -- all hash the inner rel PHJ_BUILD_HASHING_OUTER -- (multi-batch only) all hash the outer PHJ_BUILD_RUNNING -- building done, probing can begin PHJ_BUILD_DONE -- all work complete, one frees batches", "While in the phase PHJ_BUILD_HASHING_INNER a separate pair of barriers may be used repeatedly as required to coordinate expansions in the number of batches or buckets. Their phases are as follows:", "PHJ_GROW_BATCHES_ELECTING -- initial state PHJ_GROW_BATCHES_ALLOCATING -- one allocates new batches PHJ_GROW_BATCHES_REPARTITIONING -- all repartition PHJ_GROW_BATCHES_FINISHING -- one cleans up, detects skew", "If the planner got the number of batches and buckets right, those won't be necessary, but on the other hand we might finish up needing to expand the buckets or batches multiple times while hashing the inner relation to stay within our memory budget and load factor target. For that reason it's a separate pair of barriers using circular phases."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Hash", "identity": "Hash Join"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=12", "label": "EXPLAIN"}, {"url": "/docs/12/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/12/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_hashjoin/?v=12", "label": "enable_hashjoin"}, {"url": "/wiki/guc/work_mem/?v=12", "label": "work_mem"}], "release": {"ref": "PostgreSQL 12.22 source archive", "label": "12.22", "major": "12", "channel": "historical", "revision": "8df3c0474782589d3c6f374b5133b1bd14d168086edbc13c6e72e67dd4527a3b", "source_url": "https://ftp.postgresql.org/pub/source/v12.22/postgresql-12.22.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v12.22/postgresql-12.22.tar.bz2", "line": 1124, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1124", "sha256": "d02ea84fdaa201de5d9360645a9f24bfbd2c31f7d45a639e09560ac0e6b6471d", "archive_sha256": "8df3c0474782589d3c6f374b5133b1bd14d168086edbc13c6e72e67dd4527a3b"}, {"url": "https://ftp.postgresql.org/pub/source/v12.22/postgresql-12.22.tar.bz2", "line": 300, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:300", "sha256": "311b17379fe54e3f342fe5ad41c43afbdfa1b844978db2bb2eb22b82520d3256", "archive_sha256": "8df3c0474782589d3c6f374b5133b1bd14d168086edbc13c6e72e67dd4527a3b"}, {"url": "https://ftp.postgresql.org/pub/source/v12.22/postgresql-12.22.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "8bc14e676bfe827dd9359a99526cf11b701e3c2b2002c5002ae2a2ad7dc8eaba", "archive_sha256": "8df3c0474782589d3c6f374b5133b1bd14d168086edbc13c6e72e67dd4527a3b"}, {"url": "https://ftp.postgresql.org/pub/source/v12.22/postgresql-12.22.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "b0c4a0aeb48660ce06e5e700d5529ca9066fd16682bd15783d6e71b5420b07b0", "archive_sha256": "8df3c0474782589d3c6f374b5133b1bd14d168086edbc13c6e72e67dd4527a3b"}, {"url": "/docs/12/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 12.22 \u00b7 using-explain", "sha256": "06297525f2180b07e752837a3351be9c871b56559f535bcd0a67dec9baac10c0"}, {"url": "/docs/12/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 12.22 \u00b7 using-explain", "sha256": "06297525f2180b07e752837a3351be9c871b56559f535bcd0a67dec9baac10c0"}, {"url": "/docs/12/parallel-plans.html#PARALLEL-JOINS", "path": "parallel-plans.html", "label": "PostgreSQL 12.22 \u00b7 parallel-plans", "sha256": "fa33380814ee65998f524524f8681d70cf3b1198d54b39847b00462b01517ffb"}], "node_tag": "T_HashJoin", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["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."]}, {"title": "Memory and temporary storage", "paragraphs": ["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.", "PHJ_BUILD_ELECTING -- initial state PHJ_BUILD_ALLOCATING -- one sets up the batches and table 0 PHJ_BUILD_HASHING_INNER -- all hash the inner rel PHJ_BUILD_HASHING_OUTER -- (multi-batch only) all hash the outer PHJ_BUILD_RUNNING -- building done, probing can begin PHJ_BUILD_DONE -- all work complete, one frees batches", "While in the phase PHJ_BUILD_HASHING_INNER a separate pair of barriers may be used repeatedly as required to coordinate expansions in the number of batches or buckets. Their phases are as follows:", "PHJ_GROW_BATCHES_ELECTING -- initial state PHJ_GROW_BATCHES_ALLOCATING -- one allocates new batches PHJ_GROW_BATCHES_REPARTITIONING -- all repartition PHJ_GROW_BATCHES_FINISHING -- one cleans up, detects skew", "If the planner got the number of batches and buckets right, those won't be necessary, but on the other hand we might finish up needing to expand the buckets or batches multiple times while hashing the inner relation to stay within our memory budget and load factor target. For that reason it's a separate pair of barriers using circular phases."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["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."]}, {"title": "Same-version manual discussion", "paragraphs": ["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.", "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.", "In a hash join (without the \"parallel\" prefix), the inner side is executed in full by every cooperating process to build identical copies of the hash table. This may be inefficient if the hash table is large or the plan is expensive. In a parallel hash join , the inner side is a parallel hash that divides the work of building a shared hash table over the cooperating processes."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;\n\n QUERY PLAN\n------------------------------------------------------------------------------------------\n Hash Join (cost=230.47..713.98 rows=101 width=488)\n Hash Cond: (t2.unique2 = t1.unique2)\n -> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244)\n -> Hash (cost=229.20..229.20 rows=101 width=244)\n -> Bitmap Heap Scan on tenk1 t1 (cost=5.07..229.20 rows=101 width=244)\n Recheck Cond: (unique1 < 100)\n -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0)\n Index Cond: (unique1 < 100)", "source": {"url": "/docs/12/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 12.22 \u00b7 using-explain", "sha256": "06297525f2180b07e752837a3351be9c871b56559f535bcd0a67dec9baac10c0"}, "paragraphs": ["Example copied from the PostgreSQL 12.22 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:"]}, {"code": "EXPLAIN ANALYZE SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous;\n\n QUERY PLAN\n--------------------------------------------------------------------------------------------------------------------------------------------\n Sort (cost=717.34..717.59 rows=101 width=488) (actual time=7.761..7.774 rows=100 loops=1)\n Sort Key: t1.fivethous\n Sort Method: quicksort Memory: 77kB\n -> Hash Join (cost=230.47..713.98 rows=101 width=488) (actual time=0.711..7.427 rows=100 loops=1)\n Hash Cond: (t2.unique2 = t1.unique2)\n -> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244) (actual time=0.007..2.583 rows=10000 loops=1)\n -> Hash (cost=229.20..229.20 rows=101 width=244) (actual time=0.659..0.659 rows=100 loops=1)\n Buckets: 1024 Batches: 1 Memory Usage: 28kB\n -> Bitmap Heap Scan on tenk1 t1 (cost=5.07..229.20 rows=101 width=244) (actual time=0.080..0.526 rows=100 loops=1)\n Recheck Cond: (unique1 < 100)\n -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0) (actual time=0.049..0.049 rows=100 loops=1)\n Index Cond: (unique1 < 100)\n Planning time: 0.194 ms\n Execution time: 8.008 ms", "source": {"url": "/docs/12/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 12.22 \u00b7 using-explain", "sha256": "06297525f2180b07e752837a3351be9c871b56559f535bcd0a67dec9baac10c0"}, "paragraphs": ["Example copied from the PostgreSQL 12.22 manual; it was not executed for this collection.", "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:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["Hash joins can participate in parallel query execution in several ways. A parallel-oblivious hash join is one where the node is unaware that it is part of a parallel plan. In this case, a copy of the inner plan is used to build a copy of the hash table in every backend, and the outer plan could either be built from a partial or complete path, so that the results of the hash join are correspondingly either partial or complete. A parallel-aware hash join is one that behaves differently, coordinating work between backends, and appears as Parallel Hash Join in EXPLAIN output. A Parallel Hash Join always appears with a Parallel Hash node.", "Parallel-aware hash joins use the same per-backend state machine to track progress through the hash join algorithm as parallel-oblivious hash joins. In a parallel-aware hash join, there is also a shared state machine that co-operating backends use to synchronize their local state machines and program counters. The shared state machine is managed with a Barrier IPC primitive. When all attached participants arrive at a barrier, the phase advances and all waiting participants are released.", "When a participant begins working on a parallel hash join, it must first figure out how much progress has already been made, because participants don't wait for each other to begin. For this reason there are switch statements at key points in the code where we have to synchronize our local state machine with the phase, and then jump to the correct part of the algorithm so that we can get started.", "One barrier called build_barrier is used to coordinate the hashing phases. The phase is represented by an integer which begins at zero and increments one by one, but in the code it is referred to by symbolic names as follows:", "PHJ_BUILD_ELECTING -- initial state PHJ_BUILD_ALLOCATING -- one sets up the batches and table 0 PHJ_BUILD_HASHING_INNER -- all hash the inner rel PHJ_BUILD_HASHING_OUTER -- (multi-batch only) all hash the outer PHJ_BUILD_RUNNING -- building done, probing can begin PHJ_BUILD_DONE -- all work complete, one frees batches"]}, {"code": "case T_HashJoin:\n\t\t\tpname = \"Hash\";\t\t/* \"Join\" gets added by jointype switch */\n\t\t\tsname = \"Hash Join\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Probes a hash table built from its inner input while reading its outer input."], "evidence_kind": "source and documentation", "explain_names": ["Hash"], "partial_modes": [], "comparison_data": {"node_tag": "T_HashJoin", "strategies": [], "text_names": ["Hash"], "initializer": "ExecInitHashJoin", "partial_modes": [], "memory_mechanism": "hash-batches", "parallel_callbacks": ["ExecHashJoinEstimate", "ExecHashJoinInitializeDSM", "ExecHashJoinInitializeWorker", "ExecHashJoinReInitializeDSM"]}, "comparison_hash": "f721dd250dc8d1d06a3d3dc814d77a301b3c9b8eb383affa6abbdd7a95c634df", "explain_prefixes": ["Parallel"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeHashjoin.c"}, "parallel_callbacks": ["ExecHashJoinEstimate", "ExecHashJoinInitializeDSM", "ExecHashJoinInitializeWorker", "ExecHashJoinReInitializeDSM"]}, "13": {"facts": [{"label": "Core node tag", "value": "T_HashJoin"}, {"label": "Structured EXPLAIN Node Type", "value": "Hash Join"}, {"label": "Inputs", "value": "Outer child and inner Hash plan"}, {"label": "Output", "value": "Joined tuples according to the selected join type"}, {"label": "Executor initializer", "value": "ExecInitHashJoin"}, {"label": "Memory mechanism", "value": "hash-batches"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v13.23/postgresql-13.23.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "2338fb274caff86599ea52166501638cf24f47102ebdb6c02ce7cc3e25bbcf22", "archive_sha256": "6ec3c82726af92b7dec873fa1cdf881eca92a4219787dfad05acb6b10e041fd6"}, {"url": "https://ftp.postgresql.org/pub/source/v13.23/postgresql-13.23.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "2338fb274caff86599ea52166501638cf24f47102ebdb6c02ce7cc3e25bbcf22", "archive_sha256": "6ec3c82726af92b7dec873fa1cdf881eca92a4219787dfad05acb6b10e041fd6"}], "mechanism": "hash-batches", "description": "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.", "source_notes": ["PHJ_BUILD_ELECTING -- initial state PHJ_BUILD_ALLOCATING -- one sets up the batches and table 0 PHJ_BUILD_HASHING_INNER -- all hash the inner rel PHJ_BUILD_HASHING_OUTER -- (multi-batch only) all hash the outer PHJ_BUILD_RUNNING -- building done, probing can begin PHJ_BUILD_DONE -- all work complete, one frees batches", "While in the phase PHJ_BUILD_HASHING_INNER a separate pair of barriers may be used repeatedly as required to coordinate expansions in the number of batches or buckets. Their phases are as follows:", "PHJ_GROW_BATCHES_ELECTING -- initial state PHJ_GROW_BATCHES_ALLOCATING -- one allocates new batches PHJ_GROW_BATCHES_REPARTITIONING -- all repartition PHJ_GROW_BATCHES_FINISHING -- one cleans up, detects skew", "If the planner got the number of batches and buckets right, those won't be necessary, but on the other hand we might finish up needing to expand the buckets or batches multiple times while hashing the inner relation to stay within our memory budget and load factor target. For that reason it's a separate pair of barriers using circular phases."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Hash", "identity": "Hash Join"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=13", "label": "EXPLAIN"}, {"url": "/docs/13/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/13/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_hashjoin/?v=13", "label": "enable_hashjoin"}], "release": {"ref": "PostgreSQL 13.23 source archive", "label": "13.23", "major": "13", "channel": "historical", "revision": "6ec3c82726af92b7dec873fa1cdf881eca92a4219787dfad05acb6b10e041fd6", "source_url": "https://ftp.postgresql.org/pub/source/v13.23/postgresql-13.23.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v13.23/postgresql-13.23.tar.bz2", "line": 1182, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1182", "sha256": "541713e0e7f1c9cc352c2b6028964d440c19d2678a4463000094c24a88c1e730", "archive_sha256": "6ec3c82726af92b7dec873fa1cdf881eca92a4219787dfad05acb6b10e041fd6"}, {"url": "https://ftp.postgresql.org/pub/source/v13.23/postgresql-13.23.tar.bz2", "line": 300, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:300", "sha256": "d085ee99acfa00587e6ade3a1d9f8108a0566beedbbee3f54a50c9fc0cc2e875", "archive_sha256": "6ec3c82726af92b7dec873fa1cdf881eca92a4219787dfad05acb6b10e041fd6"}, {"url": "https://ftp.postgresql.org/pub/source/v13.23/postgresql-13.23.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "2338fb274caff86599ea52166501638cf24f47102ebdb6c02ce7cc3e25bbcf22", "archive_sha256": "6ec3c82726af92b7dec873fa1cdf881eca92a4219787dfad05acb6b10e041fd6"}, {"url": "https://ftp.postgresql.org/pub/source/v13.23/postgresql-13.23.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "dcb296833777b02008c4b6bae8e8f7c6423b7ffba21f36702597c9d596d039ab", "archive_sha256": "6ec3c82726af92b7dec873fa1cdf881eca92a4219787dfad05acb6b10e041fd6"}, {"url": "/docs/13/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 13.23 \u00b7 using-explain", "sha256": "650fd8629382d5dc8f9f8412c50ec5f32442a9ad2f98ca88e5348a7c2bd0ac7a"}, {"url": "/docs/13/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 13.23 \u00b7 using-explain", "sha256": "650fd8629382d5dc8f9f8412c50ec5f32442a9ad2f98ca88e5348a7c2bd0ac7a"}, {"url": "/docs/13/parallel-plans.html#PARALLEL-JOINS", "path": "parallel-plans.html", "label": "PostgreSQL 13.23 \u00b7 parallel-plans", "sha256": "025ad8564a8461b676c9153f9e084429cca0ef86c968090689b082120060f3d0"}], "node_tag": "T_HashJoin", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["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."]}, {"title": "Memory and temporary storage", "paragraphs": ["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.", "PHJ_BUILD_ELECTING -- initial state PHJ_BUILD_ALLOCATING -- one sets up the batches and table 0 PHJ_BUILD_HASHING_INNER -- all hash the inner rel PHJ_BUILD_HASHING_OUTER -- (multi-batch only) all hash the outer PHJ_BUILD_RUNNING -- building done, probing can begin PHJ_BUILD_DONE -- all work complete, one frees batches", "While in the phase PHJ_BUILD_HASHING_INNER a separate pair of barriers may be used repeatedly as required to coordinate expansions in the number of batches or buckets. Their phases are as follows:", "PHJ_GROW_BATCHES_ELECTING -- initial state PHJ_GROW_BATCHES_ALLOCATING -- one allocates new batches PHJ_GROW_BATCHES_REPARTITIONING -- all repartition PHJ_GROW_BATCHES_FINISHING -- one cleans up, detects skew", "If the planner got the number of batches and buckets right, those won't be necessary, but on the other hand we might finish up needing to expand the buckets or batches multiple times while hashing the inner relation to stay within our memory budget and load factor target. For that reason it's a separate pair of barriers using circular phases."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["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."]}, {"title": "Same-version manual discussion", "paragraphs": ["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.", "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.", "In a hash join (without the \"parallel\" prefix), the inner side is executed in full by every cooperating process to build identical copies of the hash table. This may be inefficient if the hash table is large or the plan is expensive. In a parallel hash join , the inner side is a parallel hash that divides the work of building a shared hash table over the cooperating processes."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;\n\n QUERY PLAN\n------------------------------------------------------------------------------------------\n Hash Join (cost=230.47..713.98 rows=101 width=488)\n Hash Cond: (t2.unique2 = t1.unique2)\n -> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244)\n -> Hash (cost=229.20..229.20 rows=101 width=244)\n -> Bitmap Heap Scan on tenk1 t1 (cost=5.07..229.20 rows=101 width=244)\n Recheck Cond: (unique1 < 100)\n -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0)\n Index Cond: (unique1 < 100)", "source": {"url": "/docs/13/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 13.23 \u00b7 using-explain", "sha256": "650fd8629382d5dc8f9f8412c50ec5f32442a9ad2f98ca88e5348a7c2bd0ac7a"}, "paragraphs": ["Example copied from the PostgreSQL 13.23 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:"]}, {"code": "EXPLAIN ANALYZE SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous;\n\n QUERY PLAN\n--------------------------------------------------------------------------------------------------------------------------------------------\n Sort (cost=717.34..717.59 rows=101 width=488) (actual time=7.761..7.774 rows=100 loops=1)\n Sort Key: t1.fivethous\n Sort Method: quicksort Memory: 77kB\n -> Hash Join (cost=230.47..713.98 rows=101 width=488) (actual time=0.711..7.427 rows=100 loops=1)\n Hash Cond: (t2.unique2 = t1.unique2)\n -> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244) (actual time=0.007..2.583 rows=10000 loops=1)\n -> Hash (cost=229.20..229.20 rows=101 width=244) (actual time=0.659..0.659 rows=100 loops=1)\n Buckets: 1024 Batches: 1 Memory Usage: 28kB\n -> Bitmap Heap Scan on tenk1 t1 (cost=5.07..229.20 rows=101 width=244) (actual time=0.080..0.526 rows=100 loops=1)\n Recheck Cond: (unique1 < 100)\n -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0) (actual time=0.049..0.049 rows=100 loops=1)\n Index Cond: (unique1 < 100)\n Planning time: 0.194 ms\n Execution time: 8.008 ms", "source": {"url": "/docs/13/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 13.23 \u00b7 using-explain", "sha256": "650fd8629382d5dc8f9f8412c50ec5f32442a9ad2f98ca88e5348a7c2bd0ac7a"}, "paragraphs": ["Example copied from the PostgreSQL 13.23 manual; it was not executed for this collection.", "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:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["Hash joins can participate in parallel query execution in several ways. A parallel-oblivious hash join is one where the node is unaware that it is part of a parallel plan. In this case, a copy of the inner plan is used to build a copy of the hash table in every backend, and the outer plan could either be built from a partial or complete path, so that the results of the hash join are correspondingly either partial or complete. A parallel-aware hash join is one that behaves differently, coordinating work between backends, and appears as Parallel Hash Join in EXPLAIN output. A Parallel Hash Join always appears with a Parallel Hash node.", "Parallel-aware hash joins use the same per-backend state machine to track progress through the hash join algorithm as parallel-oblivious hash joins. In a parallel-aware hash join, there is also a shared state machine that co-operating backends use to synchronize their local state machines and program counters. The shared state machine is managed with a Barrier IPC primitive. When all attached participants arrive at a barrier, the phase advances and all waiting participants are released.", "When a participant begins working on a parallel hash join, it must first figure out how much progress has already been made, because participants don't wait for each other to begin. For this reason there are switch statements at key points in the code where we have to synchronize our local state machine with the phase, and then jump to the correct part of the algorithm so that we can get started.", "One barrier called build_barrier is used to coordinate the hashing phases. The phase is represented by an integer which begins at zero and increments one by one, but in the code it is referred to by symbolic names as follows:", "PHJ_BUILD_ELECTING -- initial state PHJ_BUILD_ALLOCATING -- one sets up the batches and table 0 PHJ_BUILD_HASHING_INNER -- all hash the inner rel PHJ_BUILD_HASHING_OUTER -- (multi-batch only) all hash the outer PHJ_BUILD_RUNNING -- building done, probing can begin PHJ_BUILD_DONE -- all work complete, one frees batches"]}, {"code": "case T_HashJoin:\n\t\t\tpname = \"Hash\";\t\t/* \"Join\" gets added by jointype switch */\n\t\t\tsname = \"Hash Join\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Probes a hash table built from its inner input while reading its outer input."], "evidence_kind": "source and documentation", "explain_names": ["Hash"], "partial_modes": [], "comparison_data": {"node_tag": "T_HashJoin", "strategies": [], "text_names": ["Hash"], "initializer": "ExecInitHashJoin", "partial_modes": [], "memory_mechanism": "hash-batches", "parallel_callbacks": ["ExecHashJoinEstimate", "ExecHashJoinInitializeDSM", "ExecHashJoinInitializeWorker", "ExecHashJoinReInitializeDSM"]}, "comparison_hash": "f721dd250dc8d1d06a3d3dc814d77a301b3c9b8eb383affa6abbdd7a95c634df", "explain_prefixes": ["Parallel"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeHashjoin.c"}, "parallel_callbacks": ["ExecHashJoinEstimate", "ExecHashJoinInitializeDSM", "ExecHashJoinInitializeWorker", "ExecHashJoinReInitializeDSM"]}, "14": {"facts": [{"label": "Core node tag", "value": "T_HashJoin"}, {"label": "Structured EXPLAIN Node Type", "value": "Hash Join"}, {"label": "Inputs", "value": "Outer child and inner Hash plan"}, {"label": "Output", "value": "Joined tuples according to the selected join type"}, {"label": "Executor initializer", "value": "ExecInitHashJoin"}, {"label": "Memory mechanism", "value": "hash-batches"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "ce9fc0a7a23d310be034575e179cb2797547ee8b391e2beafca10e4b663809cb", "archive_sha256": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897"}, {"url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "ce9fc0a7a23d310be034575e179cb2797547ee8b391e2beafca10e4b663809cb", "archive_sha256": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897"}], "mechanism": "hash-batches", "description": "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.", "source_notes": ["PHJ_BUILD_ELECTING -- initial state PHJ_BUILD_ALLOCATING -- one sets up the batches and table 0 PHJ_BUILD_HASHING_INNER -- all hash the inner rel PHJ_BUILD_HASHING_OUTER -- (multi-batch only) all hash the outer PHJ_BUILD_RUNNING -- building done, probing can begin PHJ_BUILD_DONE -- all work complete, one frees batches", "While in the phase PHJ_BUILD_HASHING_INNER a separate pair of barriers may be used repeatedly as required to coordinate expansions in the number of batches or buckets. Their phases are as follows:", "PHJ_GROW_BATCHES_ELECTING -- initial state PHJ_GROW_BATCHES_ALLOCATING -- one allocates new batches PHJ_GROW_BATCHES_REPARTITIONING -- all repartition PHJ_GROW_BATCHES_FINISHING -- one cleans up, detects skew", "If the planner got the number of batches and buckets right, those won't be necessary, but on the other hand we might finish up needing to expand the buckets or batches multiple times while hashing the inner relation to stay within our memory budget and load factor target. For that reason it's a separate pair of barriers using circular phases."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Hash", "identity": "Hash Join"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=14", "label": "EXPLAIN"}, {"url": "/docs/14/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/14/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_hashjoin/?v=14", "label": "enable_hashjoin"}], "release": {"ref": "PostgreSQL 14.24 source archive", "label": "14.24", "major": "14", "channel": "stable", "revision": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897", "source_url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "line": 1218, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1218", "sha256": "e091be4e2a083b8dea39ccd09beedede22c1716ef974da66c214a44f48be8c41", "archive_sha256": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897"}, {"url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "line": 307, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:307", "sha256": "72da1c5ad457f1d92a39ab73531701794df858419e3b89d6e6cb7079634e68fa", "archive_sha256": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897"}, {"url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "ce9fc0a7a23d310be034575e179cb2797547ee8b391e2beafca10e4b663809cb", "archive_sha256": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897"}, {"url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "302f51a16b570dba7ec4e7bc045f7df5800d21630280354d1a24025f3baec75d", "archive_sha256": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897"}, {"url": "/docs/14/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 14.24 \u00b7 using-explain", "sha256": "7f5ab59cb21a035ada45ea3426c5d1cca3f781273483677f73fdd76753555206"}, {"url": "/docs/14/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 14.24 \u00b7 using-explain", "sha256": "7f5ab59cb21a035ada45ea3426c5d1cca3f781273483677f73fdd76753555206"}, {"url": "/docs/14/parallel-plans.html#PARALLEL-JOINS", "path": "parallel-plans.html", "label": "PostgreSQL 14.24 \u00b7 parallel-plans", "sha256": "71fc3525781b74150364925e123cd8598fdebe0e05326706f2bc2e1ffa0b88a4"}], "node_tag": "T_HashJoin", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["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."]}, {"title": "Memory and temporary storage", "paragraphs": ["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.", "PHJ_BUILD_ELECTING -- initial state PHJ_BUILD_ALLOCATING -- one sets up the batches and table 0 PHJ_BUILD_HASHING_INNER -- all hash the inner rel PHJ_BUILD_HASHING_OUTER -- (multi-batch only) all hash the outer PHJ_BUILD_RUNNING -- building done, probing can begin PHJ_BUILD_DONE -- all work complete, one frees batches", "While in the phase PHJ_BUILD_HASHING_INNER a separate pair of barriers may be used repeatedly as required to coordinate expansions in the number of batches or buckets. Their phases are as follows:", "PHJ_GROW_BATCHES_ELECTING -- initial state PHJ_GROW_BATCHES_ALLOCATING -- one allocates new batches PHJ_GROW_BATCHES_REPARTITIONING -- all repartition PHJ_GROW_BATCHES_FINISHING -- one cleans up, detects skew", "If the planner got the number of batches and buckets right, those won't be necessary, but on the other hand we might finish up needing to expand the buckets or batches multiple times while hashing the inner relation to stay within our memory budget and load factor target. For that reason it's a separate pair of barriers using circular phases."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["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."]}, {"title": "Same-version manual discussion", "paragraphs": ["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.", "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.", "In a hash join (without the \"parallel\" prefix), the inner side is executed in full by every cooperating process to build identical copies of the hash table. This may be inefficient if the hash table is large or the plan is expensive. In a parallel hash join , the inner side is a parallel hash that divides the work of building a shared hash table over the cooperating processes."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;\n\n QUERY PLAN\n------------------------------------------------------------------------------------------\n Hash Join (cost=230.47..713.98 rows=101 width=488)\n Hash Cond: (t2.unique2 = t1.unique2)\n -> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244)\n -> Hash (cost=229.20..229.20 rows=101 width=244)\n -> Bitmap Heap Scan on tenk1 t1 (cost=5.07..229.20 rows=101 width=244)\n Recheck Cond: (unique1 < 100)\n -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0)\n Index Cond: (unique1 < 100)", "source": {"url": "/docs/14/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 14.24 \u00b7 using-explain", "sha256": "7f5ab59cb21a035ada45ea3426c5d1cca3f781273483677f73fdd76753555206"}, "paragraphs": ["Example copied from the PostgreSQL 14.24 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:"]}, {"code": "EXPLAIN ANALYZE SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous;\n\n QUERY PLAN\n--------------------------------------------------------------------------------------------------------------------------------------------\n Sort (cost=717.34..717.59 rows=101 width=488) (actual time=7.761..7.774 rows=100 loops=1)\n Sort Key: t1.fivethous\n Sort Method: quicksort Memory: 77kB\n -> Hash Join (cost=230.47..713.98 rows=101 width=488) (actual time=0.711..7.427 rows=100 loops=1)\n Hash Cond: (t2.unique2 = t1.unique2)\n -> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244) (actual time=0.007..2.583 rows=10000 loops=1)\n -> Hash (cost=229.20..229.20 rows=101 width=244) (actual time=0.659..0.659 rows=100 loops=1)\n Buckets: 1024 Batches: 1 Memory Usage: 28kB\n -> Bitmap Heap Scan on tenk1 t1 (cost=5.07..229.20 rows=101 width=244) (actual time=0.080..0.526 rows=100 loops=1)\n Recheck Cond: (unique1 < 100)\n -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0) (actual time=0.049..0.049 rows=100 loops=1)\n Index Cond: (unique1 < 100)\n Planning time: 0.194 ms\n Execution time: 8.008 ms", "source": {"url": "/docs/14/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 14.24 \u00b7 using-explain", "sha256": "7f5ab59cb21a035ada45ea3426c5d1cca3f781273483677f73fdd76753555206"}, "paragraphs": ["Example copied from the PostgreSQL 14.24 manual; it was not executed for this collection.", "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:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["Hash joins can participate in parallel query execution in several ways. A parallel-oblivious hash join is one where the node is unaware that it is part of a parallel plan. In this case, a copy of the inner plan is used to build a copy of the hash table in every backend, and the outer plan could either be built from a partial or complete path, so that the results of the hash join are correspondingly either partial or complete. A parallel-aware hash join is one that behaves differently, coordinating work between backends, and appears as Parallel Hash Join in EXPLAIN output. A Parallel Hash Join always appears with a Parallel Hash node.", "Parallel-aware hash joins use the same per-backend state machine to track progress through the hash join algorithm as parallel-oblivious hash joins. In a parallel-aware hash join, there is also a shared state machine that co-operating backends use to synchronize their local state machines and program counters. The shared state machine is managed with a Barrier IPC primitive. When all attached participants arrive at a barrier, the phase advances and all waiting participants are released.", "When a participant begins working on a parallel hash join, it must first figure out how much progress has already been made, because participants don't wait for each other to begin. For this reason there are switch statements at key points in the code where we have to synchronize our local state machine with the phase, and then jump to the correct part of the algorithm so that we can get started.", "One barrier called build_barrier is used to coordinate the hashing phases. The phase is represented by an integer which begins at zero and increments one by one, but in the code it is referred to by symbolic names as follows:", "PHJ_BUILD_ELECTING -- initial state PHJ_BUILD_ALLOCATING -- one sets up the batches and table 0 PHJ_BUILD_HASHING_INNER -- all hash the inner rel PHJ_BUILD_HASHING_OUTER -- (multi-batch only) all hash the outer PHJ_BUILD_RUNNING -- building done, probing can begin PHJ_BUILD_DONE -- all work complete, one frees batches"]}, {"code": "case T_HashJoin:\n\t\t\tpname = \"Hash\";\t\t/* \"Join\" gets added by jointype switch */\n\t\t\tsname = \"Hash Join\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Probes a hash table built from its inner input while reading its outer input."], "evidence_kind": "source and documentation", "explain_names": ["Hash"], "partial_modes": [], "comparison_data": {"node_tag": "T_HashJoin", "strategies": [], "text_names": ["Hash"], "initializer": "ExecInitHashJoin", "partial_modes": [], "memory_mechanism": "hash-batches", "parallel_callbacks": ["ExecHashJoinEstimate", "ExecHashJoinInitializeDSM", "ExecHashJoinInitializeWorker", "ExecHashJoinReInitializeDSM"]}, "comparison_hash": "f721dd250dc8d1d06a3d3dc814d77a301b3c9b8eb383affa6abbdd7a95c634df", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeHashjoin.c"}, "parallel_callbacks": ["ExecHashJoinEstimate", "ExecHashJoinInitializeDSM", "ExecHashJoinInitializeWorker", "ExecHashJoinReInitializeDSM"]}, "15": {"facts": [{"label": "Core node tag", "value": "T_HashJoin"}, {"label": "Structured EXPLAIN Node Type", "value": "Hash Join"}, {"label": "Inputs", "value": "Outer child and inner Hash plan"}, {"label": "Output", "value": "Joined tuples according to the selected join type"}, {"label": "Executor initializer", "value": "ExecInitHashJoin"}, {"label": "Memory mechanism", "value": "hash-batches"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "0c60d385e70ea580b2154332da4de3badecb4d1bab28ab13f45eb09f23c16dbb", "archive_sha256": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89"}, {"url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "0c60d385e70ea580b2154332da4de3badecb4d1bab28ab13f45eb09f23c16dbb", "archive_sha256": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89"}], "mechanism": "hash-batches", "description": "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.", "source_notes": ["PHJ_BUILD_ELECTING -- initial state PHJ_BUILD_ALLOCATING -- one sets up the batches and table 0 PHJ_BUILD_HASHING_INNER -- all hash the inner rel PHJ_BUILD_HASHING_OUTER -- (multi-batch only) all hash the outer PHJ_BUILD_RUNNING -- building done, probing can begin PHJ_BUILD_DONE -- all work complete, one frees batches", "While in the phase PHJ_BUILD_HASHING_INNER a separate pair of barriers may be used repeatedly as required to coordinate expansions in the number of batches or buckets. Their phases are as follows:", "PHJ_GROW_BATCHES_ELECTING -- initial state PHJ_GROW_BATCHES_ALLOCATING -- one allocates new batches PHJ_GROW_BATCHES_REPARTITIONING -- all repartition PHJ_GROW_BATCHES_FINISHING -- one cleans up, detects skew", "If the planner got the number of batches and buckets right, those won't be necessary, but on the other hand we might finish up needing to expand the buckets or batches multiple times while hashing the inner relation to stay within our memory budget and load factor target. For that reason it's a separate pair of barriers using circular phases."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Hash", "identity": "Hash Join"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=15", "label": "EXPLAIN"}, {"url": "/docs/15/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/15/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_hashjoin/?v=15", "label": "enable_hashjoin"}], "release": {"ref": "PostgreSQL 15.19 source archive", "label": "15.19", "major": "15", "channel": "stable", "revision": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89", "source_url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "line": 1221, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1221", "sha256": "bb3b442d0f1b098aa8707335250102f027a596cd94117308bd16d1d36b258f5c", "archive_sha256": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89"}, {"url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "line": 307, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:307", "sha256": "19836c50a272741a4eac653541e655437c2e00710a541e5348d6a277d0669d7c", "archive_sha256": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89"}, {"url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "0c60d385e70ea580b2154332da4de3badecb4d1bab28ab13f45eb09f23c16dbb", "archive_sha256": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89"}, {"url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "fb4a4c8165495299131173680bc02a950d88e1ff610231fd97997bc0c9afc1d7", "archive_sha256": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89"}, {"url": "/docs/15/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 15.19 \u00b7 using-explain", "sha256": "d1f509457c647da453d2575c772d91022a0f115dd84f9a3b20f5ff25a243648f"}, {"url": "/docs/15/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 15.19 \u00b7 using-explain", "sha256": "d1f509457c647da453d2575c772d91022a0f115dd84f9a3b20f5ff25a243648f"}, {"url": "/docs/15/parallel-plans.html#PARALLEL-JOINS", "path": "parallel-plans.html", "label": "PostgreSQL 15.19 \u00b7 parallel-plans", "sha256": "ec9345488a15cdc05d3e0b0849763e2bf0b864ad17de786c5f023db5dab1ea96"}], "node_tag": "T_HashJoin", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["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."]}, {"title": "Memory and temporary storage", "paragraphs": ["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.", "PHJ_BUILD_ELECTING -- initial state PHJ_BUILD_ALLOCATING -- one sets up the batches and table 0 PHJ_BUILD_HASHING_INNER -- all hash the inner rel PHJ_BUILD_HASHING_OUTER -- (multi-batch only) all hash the outer PHJ_BUILD_RUNNING -- building done, probing can begin PHJ_BUILD_DONE -- all work complete, one frees batches", "While in the phase PHJ_BUILD_HASHING_INNER a separate pair of barriers may be used repeatedly as required to coordinate expansions in the number of batches or buckets. Their phases are as follows:", "PHJ_GROW_BATCHES_ELECTING -- initial state PHJ_GROW_BATCHES_ALLOCATING -- one allocates new batches PHJ_GROW_BATCHES_REPARTITIONING -- all repartition PHJ_GROW_BATCHES_FINISHING -- one cleans up, detects skew", "If the planner got the number of batches and buckets right, those won't be necessary, but on the other hand we might finish up needing to expand the buckets or batches multiple times while hashing the inner relation to stay within our memory budget and load factor target. For that reason it's a separate pair of barriers using circular phases."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["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."]}, {"title": "Same-version manual discussion", "paragraphs": ["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.", "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.", "In a hash join (without the \"parallel\" prefix), the inner side is executed in full by every cooperating process to build identical copies of the hash table. This may be inefficient if the hash table is large or the plan is expensive. In a parallel hash join , the inner side is a parallel hash that divides the work of building a shared hash table over the cooperating processes."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;\n\n QUERY PLAN\n------------------------------------------------------------------------------------------\n Hash Join (cost=230.47..713.98 rows=101 width=488)\n Hash Cond: (t2.unique2 = t1.unique2)\n -> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244)\n -> Hash (cost=229.20..229.20 rows=101 width=244)\n -> Bitmap Heap Scan on tenk1 t1 (cost=5.07..229.20 rows=101 width=244)\n Recheck Cond: (unique1 < 100)\n -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0)\n Index Cond: (unique1 < 100)", "source": {"url": "/docs/15/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 15.19 \u00b7 using-explain", "sha256": "d1f509457c647da453d2575c772d91022a0f115dd84f9a3b20f5ff25a243648f"}, "paragraphs": ["Example copied from the PostgreSQL 15.19 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:"]}, {"code": "EXPLAIN ANALYZE SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous;\n\n QUERY PLAN\n--------------------------------------------------------------------------------------------------------------------------------------------\n Sort (cost=717.34..717.59 rows=101 width=488) (actual time=7.761..7.774 rows=100 loops=1)\n Sort Key: t1.fivethous\n Sort Method: quicksort Memory: 77kB\n -> Hash Join (cost=230.47..713.98 rows=101 width=488) (actual time=0.711..7.427 rows=100 loops=1)\n Hash Cond: (t2.unique2 = t1.unique2)\n -> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244) (actual time=0.007..2.583 rows=10000 loops=1)\n -> Hash (cost=229.20..229.20 rows=101 width=244) (actual time=0.659..0.659 rows=100 loops=1)\n Buckets: 1024 Batches: 1 Memory Usage: 28kB\n -> Bitmap Heap Scan on tenk1 t1 (cost=5.07..229.20 rows=101 width=244) (actual time=0.080..0.526 rows=100 loops=1)\n Recheck Cond: (unique1 < 100)\n -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0) (actual time=0.049..0.049 rows=100 loops=1)\n Index Cond: (unique1 < 100)\n Planning time: 0.194 ms\n Execution time: 8.008 ms", "source": {"url": "/docs/15/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 15.19 \u00b7 using-explain", "sha256": "d1f509457c647da453d2575c772d91022a0f115dd84f9a3b20f5ff25a243648f"}, "paragraphs": ["Example copied from the PostgreSQL 15.19 manual; it was not executed for this collection.", "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:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["Hash joins can participate in parallel query execution in several ways. A parallel-oblivious hash join is one where the node is unaware that it is part of a parallel plan. In this case, a copy of the inner plan is used to build a copy of the hash table in every backend, and the outer plan could either be built from a partial or complete path, so that the results of the hash join are correspondingly either partial or complete. A parallel-aware hash join is one that behaves differently, coordinating work between backends, and appears as Parallel Hash Join in EXPLAIN output. A Parallel Hash Join always appears with a Parallel Hash node.", "Parallel-aware hash joins use the same per-backend state machine to track progress through the hash join algorithm as parallel-oblivious hash joins. In a parallel-aware hash join, there is also a shared state machine that co-operating backends use to synchronize their local state machines and program counters. The shared state machine is managed with a Barrier IPC primitive. When all attached participants arrive at a barrier, the phase advances and all waiting participants are released.", "When a participant begins working on a parallel hash join, it must first figure out how much progress has already been made, because participants don't wait for each other to begin. For this reason there are switch statements at key points in the code where we have to synchronize our local state machine with the phase, and then jump to the correct part of the algorithm so that we can get started.", "One barrier called build_barrier is used to coordinate the hashing phases. The phase is represented by an integer which begins at zero and increments one by one, but in the code it is referred to by symbolic names as follows:", "PHJ_BUILD_ELECTING -- initial state PHJ_BUILD_ALLOCATING -- one sets up the batches and table 0 PHJ_BUILD_HASHING_INNER -- all hash the inner rel PHJ_BUILD_HASHING_OUTER -- (multi-batch only) all hash the outer PHJ_BUILD_RUNNING -- building done, probing can begin PHJ_BUILD_DONE -- all work complete, one frees batches"]}, {"code": "case T_HashJoin:\n\t\t\tpname = \"Hash\";\t\t/* \"Join\" gets added by jointype switch */\n\t\t\tsname = \"Hash Join\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Probes a hash table built from its inner input while reading its outer input."], "evidence_kind": "source and documentation", "explain_names": ["Hash"], "partial_modes": [], "comparison_data": {"node_tag": "T_HashJoin", "strategies": [], "text_names": ["Hash"], "initializer": "ExecInitHashJoin", "partial_modes": [], "memory_mechanism": "hash-batches", "parallel_callbacks": ["ExecHashJoinEstimate", "ExecHashJoinInitializeDSM", "ExecHashJoinInitializeWorker", "ExecHashJoinReInitializeDSM"]}, "comparison_hash": "f721dd250dc8d1d06a3d3dc814d77a301b3c9b8eb383affa6abbdd7a95c634df", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeHashjoin.c"}, "parallel_callbacks": ["ExecHashJoinEstimate", "ExecHashJoinInitializeDSM", "ExecHashJoinInitializeWorker", "ExecHashJoinReInitializeDSM"]}, "16": {"facts": [{"label": "Core node tag", "value": "T_HashJoin"}, {"label": "Structured EXPLAIN Node Type", "value": "Hash Join"}, {"label": "Inputs", "value": "Outer child and inner Hash plan"}, {"label": "Output", "value": "Joined tuples according to the selected join type"}, {"label": "Executor initializer", "value": "ExecInitHashJoin"}, {"label": "Memory mechanism", "value": "hash-batches"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "af73bcac9e79097ade5166ff3b3e22f338d3aee976a7e8a5bc812aabfcedc7ef", "archive_sha256": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed"}, {"url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "af73bcac9e79097ade5166ff3b3e22f338d3aee976a7e8a5bc812aabfcedc7ef", "archive_sha256": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed"}], "mechanism": "hash-batches", "description": "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.", "source_notes": ["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."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Hash", "identity": "Hash Join"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=16", "label": "EXPLAIN"}, {"url": "/docs/16/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/16/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_hashjoin/?v=16", "label": "enable_hashjoin"}, {"url": "/wiki/guc/work_mem/?v=16", "label": "work_mem"}], "release": {"ref": "PostgreSQL 16.15 source archive", "label": "16.15", "major": "16", "channel": "stable", "revision": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed", "source_url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "line": 1254, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1254", "sha256": "8e017f0116dbea471339b40c37a667cc9f95039e7e0329c783e5e8ce194de7e1", "archive_sha256": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed"}, {"url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "line": 307, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:307", "sha256": "e48c08e555f8cb4e4bb43df516c4b8906ce9bc374b2a745d98a1fc8c22cc5099", "archive_sha256": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed"}, {"url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "af73bcac9e79097ade5166ff3b3e22f338d3aee976a7e8a5bc812aabfcedc7ef", "archive_sha256": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed"}, {"url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "97db47353db76326b874589a5ad0a04501cc74cd72e237e7bd956e7472c41f1f", "archive_sha256": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed"}, {"url": "/docs/16/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 16.15 \u00b7 using-explain", "sha256": "bd8b86e5281cf0e52ad6e0e4bb8b6ff6510dac61982b44f0c1dd07d54012db3c"}, {"url": "/docs/16/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 16.15 \u00b7 using-explain", "sha256": "bd8b86e5281cf0e52ad6e0e4bb8b6ff6510dac61982b44f0c1dd07d54012db3c"}, {"url": "/docs/16/parallel-plans.html#PARALLEL-JOINS", "path": "parallel-plans.html", "label": "PostgreSQL 16.15 \u00b7 parallel-plans", "sha256": "53d83f63f97381fe1e4c0cfe5ceb81d30c1542b03e35a144684afd8fb3b9686c"}], "node_tag": "T_HashJoin", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["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."]}, {"title": "Memory and temporary storage", "paragraphs": ["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."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["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."]}, {"title": "Same-version manual discussion", "paragraphs": ["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.", "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.", "In a hash join (without the \"parallel\" prefix), the inner side is executed in full by every cooperating process to build identical copies of the hash table. This may be inefficient if the hash table is large or the plan is expensive. In a parallel hash join , the inner side is a parallel hash that divides the work of building a shared hash table over the cooperating processes."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;\n\n QUERY PLAN\n------------------------------------------------------------------------------------------\n Hash Join (cost=230.47..713.98 rows=101 width=488)\n Hash Cond: (t2.unique2 = t1.unique2)\n -> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244)\n -> Hash (cost=229.20..229.20 rows=101 width=244)\n -> Bitmap Heap Scan on tenk1 t1 (cost=5.07..229.20 rows=101 width=244)\n Recheck Cond: (unique1 < 100)\n -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0)\n Index Cond: (unique1 < 100)", "source": {"url": "/docs/16/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 16.15 \u00b7 using-explain", "sha256": "bd8b86e5281cf0e52ad6e0e4bb8b6ff6510dac61982b44f0c1dd07d54012db3c"}, "paragraphs": ["Example copied from the PostgreSQL 16.15 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:"]}, {"code": "EXPLAIN ANALYZE SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous;\n\n QUERY PLAN\n--------------------------------------------------------------------------------------------------------------------------------------------\n Sort (cost=717.34..717.59 rows=101 width=488) (actual time=7.761..7.774 rows=100 loops=1)\n Sort Key: t1.fivethous\n Sort Method: quicksort Memory: 77kB\n -> Hash Join (cost=230.47..713.98 rows=101 width=488) (actual time=0.711..7.427 rows=100 loops=1)\n Hash Cond: (t2.unique2 = t1.unique2)\n -> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244) (actual time=0.007..2.583 rows=10000 loops=1)\n -> Hash (cost=229.20..229.20 rows=101 width=244) (actual time=0.659..0.659 rows=100 loops=1)\n Buckets: 1024 Batches: 1 Memory Usage: 28kB\n -> Bitmap Heap Scan on tenk1 t1 (cost=5.07..229.20 rows=101 width=244) (actual time=0.080..0.526 rows=100 loops=1)\n Recheck Cond: (unique1 < 100)\n -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0) (actual time=0.049..0.049 rows=100 loops=1)\n Index Cond: (unique1 < 100)\n Planning time: 0.194 ms\n Execution time: 8.008 ms", "source": {"url": "/docs/16/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 16.15 \u00b7 using-explain", "sha256": "bd8b86e5281cf0e52ad6e0e4bb8b6ff6510dac61982b44f0c1dd07d54012db3c"}, "paragraphs": ["Example copied from the PostgreSQL 16.15 manual; it was not executed for this collection.", "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:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["This is based on the \"hybrid hash join\" algorithm described shortly in the following page", "\"An Adaptive Hash Join Algorithm for Multiuser Environments\" Hansj\u00f6rg Zeller; Jim Gray (1990). Proceedings of the 16th VLDB conference. Brisbane: 186\u2013197.", "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."]}, {"code": "case T_HashJoin:\n\t\t\tpname = \"Hash\";\t\t/* \"Join\" gets added by jointype switch */\n\t\t\tsname = \"Hash Join\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Probes a hash table built from its inner input while reading its outer input."], "evidence_kind": "source and documentation", "explain_names": ["Hash"], "partial_modes": [], "comparison_data": {"node_tag": "T_HashJoin", "strategies": [], "text_names": ["Hash"], "initializer": "ExecInitHashJoin", "partial_modes": [], "memory_mechanism": "hash-batches", "parallel_callbacks": ["ExecHashJoinEstimate", "ExecHashJoinInitializeDSM", "ExecHashJoinInitializeWorker", "ExecHashJoinReInitializeDSM"]}, "comparison_hash": "f721dd250dc8d1d06a3d3dc814d77a301b3c9b8eb383affa6abbdd7a95c634df", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeHashjoin.c"}, "parallel_callbacks": ["ExecHashJoinEstimate", "ExecHashJoinInitializeDSM", "ExecHashJoinInitializeWorker", "ExecHashJoinReInitializeDSM"]}, "17": {"facts": [{"label": "Core node tag", "value": "T_HashJoin"}, {"label": "Structured EXPLAIN Node Type", "value": "Hash Join"}, {"label": "Inputs", "value": "Outer child and inner Hash plan"}, {"label": "Output", "value": "Joined tuples according to the selected join type"}, {"label": "Executor initializer", "value": "ExecInitHashJoin"}, {"label": "Memory mechanism", "value": "hash-batches"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "389cd4a41ab1d9711393154ffa2c5915af52233b114e8dcafd7e7847f00629f7", "archive_sha256": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979"}, {"url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "389cd4a41ab1d9711393154ffa2c5915af52233b114e8dcafd7e7847f00629f7", "archive_sha256": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979"}], "mechanism": "hash-batches", "description": "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.", "source_notes": ["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."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Hash", "identity": "Hash Join"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=17", "label": "EXPLAIN"}, {"url": "/docs/17/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/17/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_hashjoin/?v=17", "label": "enable_hashjoin"}, {"url": "/wiki/guc/work_mem/?v=17", "label": "work_mem"}], "release": {"ref": "PostgreSQL 17.11 source archive", "label": "17.11", "major": "17", "channel": "stable", "revision": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979", "source_url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "line": 1443, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1443", "sha256": "741251b1a3b6d269a52a673d42eb63b02e13a5872db7b359b137086ab21b63c8", "archive_sha256": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979"}, {"url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "line": 307, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:307", "sha256": "a77576e158b94cb01fa8c5174ba133004eabdd727660323f8afc66c8d2e757b8", "archive_sha256": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979"}, {"url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "389cd4a41ab1d9711393154ffa2c5915af52233b114e8dcafd7e7847f00629f7", "archive_sha256": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979"}, {"url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "d390dd69e2d3f5085beb42b33e46ff0676a2959b916a12b82118a7e545f8e562", "archive_sha256": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979"}, {"url": "/docs/17/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 17.11 \u00b7 using-explain", "sha256": "8e3422c77496cc53bfc225ccadda3e82eb23c8c362b8eb95e7fb02cdb315ea78"}, {"url": "/docs/17/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 17.11 \u00b7 using-explain", "sha256": "8e3422c77496cc53bfc225ccadda3e82eb23c8c362b8eb95e7fb02cdb315ea78"}, {"url": "/docs/17/parallel-plans.html#PARALLEL-JOINS", "path": "parallel-plans.html", "label": "PostgreSQL 17.11 \u00b7 parallel-plans", "sha256": "784fe3d3b7ae7d1a34e466aa551bde484a40a3b60dc5d2ada6e1675f40713bbc"}], "node_tag": "T_HashJoin", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["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."]}, {"title": "Memory and temporary storage", "paragraphs": ["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."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["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."]}, {"title": "Same-version manual discussion", "paragraphs": ["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."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;\n\n QUERY PLAN\n------------------------------------------------------------------------------------------\n Hash Join (cost=226.23..709.73 rows=100 width=488)\n Hash Cond: (t2.unique2 = t1.unique2)\n -> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244)\n -> Hash (cost=224.98..224.98 rows=100 width=244)\n -> Bitmap Heap Scan on tenk1 t1 (cost=5.06..224.98 rows=100 width=244)\n Recheck Cond: (unique1 < 100)\n -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0)\n Index Cond: (unique1 < 100)", "source": {"url": "/docs/17/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 17.11 \u00b7 using-explain", "sha256": "8e3422c77496cc53bfc225ccadda3e82eb23c8c362b8eb95e7fb02cdb315ea78"}, "paragraphs": ["Example copied from the PostgreSQL 17.11 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:"]}, {"code": "SET enable_mergejoin = off;\n\nEXPLAIN SELECT *\nFROM tenk1 t1, onek t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;\n\n QUERY PLAN\n------------------------------------------------------------------------------------------\n Hash Join (cost=226.23..344.08 rows=10 width=488)\n Hash Cond: (t2.unique2 = t1.unique2)\n -> Seq Scan on onek t2 (cost=0.00..114.00 rows=1000 width=244)\n -> Hash (cost=224.98..224.98 rows=100 width=244)\n -> Bitmap Heap Scan on tenk1 t1 (cost=5.06..224.98 rows=100 width=244)\n Recheck Cond: (unique1 < 100)\n -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0)\n Index Cond: (unique1 < 100)", "source": {"url": "/docs/17/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 17.11 \u00b7 using-explain", "sha256": "8e3422c77496cc53bfc225ccadda3e82eb23c8c362b8eb95e7fb02cdb315ea78"}, "paragraphs": ["Example copied from the PostgreSQL 17.11 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"]}]}, {"title": "Executor implementation notes", "paragraphs": ["This is based on the \"hybrid hash join\" algorithm described shortly in the following page", "\"An Adaptive Hash Join Algorithm for Multiuser Environments\" Hansj\u00f6rg Zeller; Jim Gray (1990). Proceedings of the 16th VLDB conference. Brisbane: 186\u2013197.", "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."]}, {"code": "case T_HashJoin:\n\t\t\tpname = \"Hash\";\t\t/* \"Join\" gets added by jointype switch */\n\t\t\tsname = \"Hash Join\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Probes a hash table built from its inner input while reading its outer input."], "evidence_kind": "source and documentation", "explain_names": ["Hash"], "partial_modes": [], "comparison_data": {"node_tag": "T_HashJoin", "strategies": [], "text_names": ["Hash"], "initializer": "ExecInitHashJoin", "partial_modes": [], "memory_mechanism": "hash-batches", "parallel_callbacks": ["ExecHashJoinEstimate", "ExecHashJoinInitializeDSM", "ExecHashJoinInitializeWorker", "ExecHashJoinReInitializeDSM"]}, "comparison_hash": "f721dd250dc8d1d06a3d3dc814d77a301b3c9b8eb383affa6abbdd7a95c634df", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeHashjoin.c"}, "parallel_callbacks": ["ExecHashJoinEstimate", "ExecHashJoinInitializeDSM", "ExecHashJoinInitializeWorker", "ExecHashJoinReInitializeDSM"]}, "18": {"facts": [{"label": "Core node tag", "value": "T_HashJoin"}, {"label": "Structured EXPLAIN Node Type", "value": "Hash Join"}, {"label": "Inputs", "value": "Outer child and inner Hash plan"}, {"label": "Output", "value": "Joined tuples according to the selected join type"}, {"label": "Executor initializer", "value": "ExecInitHashJoin"}, {"label": "Memory mechanism", "value": "hash-batches"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "4a72b0db253c1905c415462cfe56a2327ac65507c607b6d699d2717fa624ad56", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "4a72b0db253c1905c415462cfe56a2327ac65507c607b6d699d2717fa624ad56", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}], "mechanism": "hash-batches", "description": "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.", "source_notes": ["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."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Hash", "identity": "Hash Join"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=18", "label": "EXPLAIN"}, {"url": "/docs/18/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/18/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_hashjoin/?v=18", "label": "enable_hashjoin"}, {"url": "/wiki/guc/work_mem/?v=18", "label": "work_mem"}], "release": {"ref": "PostgreSQL 18.6 source archive", "label": "18.6", "major": "18", "channel": "stable", "revision": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f", "source_url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "line": 1428, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1428", "sha256": "34c86d6070224a0e981efef51f79101d6d505e5874f1684ace183034bab14bb4", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "line": 307, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:307", "sha256": "f8a06a3f539077249b20664b2812433db6d7bd12b2c0ca633525db43d06f112a", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "4a72b0db253c1905c415462cfe56a2327ac65507c607b6d699d2717fa624ad56", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "52422b327a8049fbbb20d8b96008a0fc0a6fafa60f7eff3c695d5b2e83830120", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "/docs/18/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 18.6 \u00b7 using-explain", "sha256": "60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed"}, {"url": "/docs/18/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 18.6 \u00b7 using-explain", "sha256": "60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed"}, {"url": "/docs/18/parallel-plans.html#PARALLEL-JOINS", "path": "parallel-plans.html", "label": "PostgreSQL 18.6 \u00b7 parallel-plans", "sha256": "62207d207bead82b01b59dc119c4f95856f08655cc11d4699a05a40867ed2070"}], "node_tag": "T_HashJoin", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["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."]}, {"title": "Memory and temporary storage", "paragraphs": ["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."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["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."]}, {"title": "Same-version manual discussion", "paragraphs": ["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."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;\n\n QUERY PLAN\n------------------------------------------------------------------------------------------\n Hash Join (cost=226.23..709.73 rows=100 width=488)\n Hash Cond: (t2.unique2 = t1.unique2)\n -> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244)\n -> Hash (cost=224.98..224.98 rows=100 width=244)\n -> Bitmap Heap Scan on tenk1 t1 (cost=5.06..224.98 rows=100 width=244)\n Recheck Cond: (unique1 < 100)\n -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0)\n Index Cond: (unique1 < 100)", "source": {"url": "/docs/18/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 18.6 \u00b7 using-explain", "sha256": "60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed"}, "paragraphs": ["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:"]}, {"code": "SET enable_mergejoin = off;\n\nEXPLAIN SELECT *\nFROM tenk1 t1, onek t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;\n\n QUERY PLAN\n------------------------------------------------------------------------------------------\n Hash Join (cost=226.23..344.08 rows=10 width=488)\n Hash Cond: (t2.unique2 = t1.unique2)\n -> Seq Scan on onek t2 (cost=0.00..114.00 rows=1000 width=244)\n -> Hash (cost=224.98..224.98 rows=100 width=244)\n -> Bitmap Heap Scan on tenk1 t1 (cost=5.06..224.98 rows=100 width=244)\n Recheck Cond: (unique1 < 100)\n -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0)\n Index Cond: (unique1 < 100)", "source": {"url": "/docs/18/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 18.6 \u00b7 using-explain", "sha256": "60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed"}, "paragraphs": ["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"]}]}, {"title": "Executor implementation notes", "paragraphs": ["This is based on the \"hybrid hash join\" algorithm described shortly in the following page", "\"An Adaptive Hash Join Algorithm for Multiuser Environments\" Hansj\u00f6rg Zeller; Jim Gray (1990). Proceedings of the 16th VLDB conference. Brisbane: 186\u2013197.", "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."]}, {"code": "case T_HashJoin:\n\t\t\tpname = \"Hash\";\t\t/* \"Join\" gets added by jointype switch */\n\t\t\tsname = \"Hash Join\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Probes a hash table built from its inner input while reading its outer input."], "evidence_kind": "source and documentation", "explain_names": ["Hash"], "partial_modes": [], "comparison_data": {"node_tag": "T_HashJoin", "strategies": [], "text_names": ["Hash"], "initializer": "ExecInitHashJoin", "partial_modes": [], "memory_mechanism": "hash-batches", "parallel_callbacks": ["ExecHashJoinEstimate", "ExecHashJoinInitializeDSM", "ExecHashJoinInitializeWorker", "ExecHashJoinReInitializeDSM"]}, "comparison_hash": "f721dd250dc8d1d06a3d3dc814d77a301b3c9b8eb383affa6abbdd7a95c634df", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeHashjoin.c"}, "parallel_callbacks": ["ExecHashJoinEstimate", "ExecHashJoinInitializeDSM", "ExecHashJoinInitializeWorker", "ExecHashJoinReInitializeDSM"]}, "19": {"facts": [{"label": "Core node tag", "value": "T_HashJoin"}, {"label": "Structured EXPLAIN Node Type", "value": "Hash Join"}, {"label": "Inputs", "value": "Outer child and inner Hash plan"}, {"label": "Output", "value": "Joined tuples according to the selected join type"}, {"label": "Executor initializer", "value": "ExecInitHashJoin"}, {"label": "Memory mechanism", "value": "hash-batches"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "474fc47ff061c8f93520dace692a4d31dde966ae8bc6ed87e2131ba71691f581", "archive_sha256": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86"}, {"url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "474fc47ff061c8f93520dace692a4d31dde966ae8bc6ed87e2131ba71691f581", "archive_sha256": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86"}], "mechanism": "hash-batches", "description": "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.", "source_notes": ["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."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Hash", "identity": "Hash Join"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=19", "label": "EXPLAIN"}, {"url": "/docs/19/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/19/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_hashjoin/?v=19", "label": "enable_hashjoin"}, {"url": "/wiki/guc/work_mem/?v=19", "label": "work_mem"}], "release": {"ref": "PostgreSQL 19beta4 source archive", "label": "19beta4", "major": "19", "channel": "preview", "revision": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86", "source_url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "line": 1440, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1440", "sha256": "8b115b1c194a4b54ae630209a741e293b1df49a9052f10b2de9ca092a48998e3", "archive_sha256": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86"}, {"url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "line": 307, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:307", "sha256": "5e39b2037bed672da55104229ecc32da5abde44c26bcad01479edcfa044d09ed", "archive_sha256": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86"}, {"url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "474fc47ff061c8f93520dace692a4d31dde966ae8bc6ed87e2131ba71691f581", "archive_sha256": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86"}, {"url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "1c65d5d6b6c81c71531685843647869bcae630779d815a5036b06e070c6c06c7", "archive_sha256": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86"}, {"url": "/docs/19/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 19beta4 \u00b7 using-explain", "sha256": "52f111fbd213e2200146319d01a9dcc2ea90001617d2a28edc52f7954c2dd70b"}, {"url": "/docs/19/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 19beta4 \u00b7 using-explain", "sha256": "52f111fbd213e2200146319d01a9dcc2ea90001617d2a28edc52f7954c2dd70b"}, {"url": "/docs/19/parallel-plans.html#PARALLEL-JOINS", "path": "parallel-plans.html", "label": "PostgreSQL 19beta4 \u00b7 parallel-plans", "sha256": "f99fee3456a48a5ce2c399d6b35ab93183c2c9012e8ed60c53c0c253f79c3756"}], "node_tag": "T_HashJoin", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["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."]}, {"title": "Memory and temporary storage", "paragraphs": ["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."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["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."]}, {"title": "Same-version manual discussion", "paragraphs": ["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."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;\n\n QUERY PLAN\n------------------------------------------------------------------------------------------\n Hash Join (cost=226.23..709.73 rows=100 width=488)\n Hash Cond: (t2.unique2 = t1.unique2)\n -> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244)\n -> Hash (cost=224.98..224.98 rows=100 width=244)\n -> Bitmap Heap Scan on tenk1 t1 (cost=5.06..224.98 rows=100 width=244)\n Recheck Cond: (unique1 < 100)\n -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0)\n Index Cond: (unique1 < 100)", "source": {"url": "/docs/19/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 19beta4 \u00b7 using-explain", "sha256": "52f111fbd213e2200146319d01a9dcc2ea90001617d2a28edc52f7954c2dd70b"}, "paragraphs": ["Example copied from the PostgreSQL 19beta4 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:"]}, {"code": "SET enable_mergejoin = off;\n\nEXPLAIN SELECT *\nFROM tenk1 t1, onek t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;\n\n QUERY PLAN\n------------------------------------------------------------------------------------------\n Hash Join (cost=226.23..344.08 rows=10 width=488)\n Hash Cond: (t2.unique2 = t1.unique2)\n -> Seq Scan on onek t2 (cost=0.00..114.00 rows=1000 width=244)\n -> Hash (cost=224.98..224.98 rows=100 width=244)\n -> Bitmap Heap Scan on tenk1 t1 (cost=5.06..224.98 rows=100 width=244)\n Recheck Cond: (unique1 < 100)\n -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0)\n Index Cond: (unique1 < 100)", "source": {"url": "/docs/19/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 19beta4 \u00b7 using-explain", "sha256": "52f111fbd213e2200146319d01a9dcc2ea90001617d2a28edc52f7954c2dd70b"}, "paragraphs": ["Example copied from the PostgreSQL 19beta4 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"]}]}, {"title": "Executor implementation notes", "paragraphs": ["This is based on the \"hybrid hash join\" algorithm described shortly in the following page", "\"An Adaptive Hash Join Algorithm for Multiuser Environments\" Hansj\u00f6rg Zeller; Jim Gray (1990). Proceedings of the 16th VLDB conference. Brisbane: 186\u2013197.", "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."]}, {"code": "case T_HashJoin:\n\t\t\tpname = \"Hash\";\t\t/* \"Join\" gets added by jointype switch */\n\t\t\tsname = \"Hash Join\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Probes a hash table built from its inner input while reading its outer input."], "evidence_kind": "source and documentation", "explain_names": ["Hash"], "partial_modes": [], "comparison_data": {"node_tag": "T_HashJoin", "strategies": [], "text_names": ["Hash"], "initializer": "ExecInitHashJoin", "partial_modes": [], "memory_mechanism": "hash-batches", "parallel_callbacks": ["ExecHashJoinEstimate", "ExecHashJoinInitializeDSM", "ExecHashJoinInitializeWorker", "ExecHashJoinReInitializeDSM"]}, "comparison_hash": "f721dd250dc8d1d06a3d3dc814d77a301b3c9b8eb383affa6abbdd7a95c634df", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeHashjoin.c"}, "parallel_callbacks": ["ExecHashJoinEstimate", "ExecHashJoinInitializeDSM", "ExecHashJoinInitializeWorker", "ExecHashJoinReInitializeDSM"]}, "20": {"facts": [{"label": "Core node tag", "value": "T_HashJoin"}, {"label": "Structured EXPLAIN Node Type", "value": "Hash Join"}, {"label": "Inputs", "value": "Outer child and inner Hash plan"}, {"label": "Output", "value": "Joined tuples according to the selected join type"}, {"label": "Executor initializer", "value": "ExecInitHashJoin"}, {"label": "Memory mechanism", "value": "hash-batches"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "474fc47ff061c8f93520dace692a4d31dde966ae8bc6ed87e2131ba71691f581", "archive_sha256": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41"}, {"url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "474fc47ff061c8f93520dace692a4d31dde966ae8bc6ed87e2131ba71691f581", "archive_sha256": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41"}], "mechanism": "hash-batches", "description": "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.", "source_notes": ["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."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Hash", "identity": "Hash Join"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=20", "label": "EXPLAIN"}, {"url": "/docs/devel/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/devel/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_hashjoin/?v=20", "label": "enable_hashjoin"}, {"url": "/wiki/guc/work_mem/?v=20", "label": "work_mem"}], "release": {"ref": "PostgreSQL 20devel source archive", "label": "20devel", "major": "20", "channel": "devel", "revision": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41", "source_url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "source_snapshot_utc": "26-Sep-2026 20:22"}, "sources": [{"url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "line": 1440, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1440", "sha256": "13402758013520451539427b5993db06d463ca11c4e2d4cc5444e82367688077", "archive_sha256": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41"}, {"url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "line": 307, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:307", "sha256": "5e39b2037bed672da55104229ecc32da5abde44c26bcad01479edcfa044d09ed", "archive_sha256": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41"}, {"url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "474fc47ff061c8f93520dace692a4d31dde966ae8bc6ed87e2131ba71691f581", "archive_sha256": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41"}, {"url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "7a94ed1652f0d74d50c39971d1cd3e8051dbc0d6058f31b6de71a433ca343521", "archive_sha256": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41"}, {"url": "/docs/devel/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 20devel \u00b7 using-explain", "sha256": "99cfea3035876ea63f88b75ba8c964b51a32a70606544e09f769c0b60a234b31"}, {"url": "/docs/devel/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 20devel \u00b7 using-explain", "sha256": "99cfea3035876ea63f88b75ba8c964b51a32a70606544e09f769c0b60a234b31"}, {"url": "/docs/devel/parallel-plans.html#PARALLEL-JOINS", "path": "parallel-plans.html", "label": "PostgreSQL 20devel \u00b7 parallel-plans", "sha256": "6451d4254d26d789b8697ba7207288ac26e75e56f9711c8a5a3f5480171a4724"}], "node_tag": "T_HashJoin", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["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."]}, {"title": "Memory and temporary storage", "paragraphs": ["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."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["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."]}, {"title": "Same-version manual discussion", "paragraphs": ["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."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;\n\n QUERY PLAN\n------------------------------------------------------------------------------------------\n Hash Join (cost=226.23..709.73 rows=100 width=488)\n Hash Cond: (t2.unique2 = t1.unique2)\n -> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244)\n -> Hash (cost=224.98..224.98 rows=100 width=244)\n -> Bitmap Heap Scan on tenk1 t1 (cost=5.06..224.98 rows=100 width=244)\n Recheck Cond: (unique1 < 100)\n -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0)\n Index Cond: (unique1 < 100)", "source": {"url": "/docs/devel/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 20devel \u00b7 using-explain", "sha256": "99cfea3035876ea63f88b75ba8c964b51a32a70606544e09f769c0b60a234b31"}, "paragraphs": ["Example copied from the PostgreSQL 20devel 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:"]}, {"code": "SET enable_mergejoin = off;\n\nEXPLAIN SELECT *\nFROM tenk1 t1, onek t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;\n\n QUERY PLAN\n------------------------------------------------------------------------------------------\n Hash Join (cost=226.23..344.08 rows=10 width=488)\n Hash Cond: (t2.unique2 = t1.unique2)\n -> Seq Scan on onek t2 (cost=0.00..114.00 rows=1000 width=244)\n -> Hash (cost=224.98..224.98 rows=100 width=244)\n -> Bitmap Heap Scan on tenk1 t1 (cost=5.06..224.98 rows=100 width=244)\n Recheck Cond: (unique1 < 100)\n -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0)\n Index Cond: (unique1 < 100)", "source": {"url": "/docs/devel/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 20devel \u00b7 using-explain", "sha256": "99cfea3035876ea63f88b75ba8c964b51a32a70606544e09f769c0b60a234b31"}, "paragraphs": ["Example copied from the PostgreSQL 20devel 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"]}]}, {"title": "Executor implementation notes", "paragraphs": ["This is based on the \"hybrid hash join\" algorithm described shortly in the following page", "\"An Adaptive Hash Join Algorithm for Multiuser Environments\" Hansj\u00f6rg Zeller; Jim Gray (1990). Proceedings of the 16th VLDB conference. Brisbane: 186\u2013197.", "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."]}, {"code": "case T_HashJoin:\n\t\t\tpname = \"Hash\";\t\t/* \"Join\" gets added by jointype switch */\n\t\t\tsname = \"Hash Join\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Probes a hash table built from its inner input while reading its outer input."], "evidence_kind": "source and documentation", "explain_names": ["Hash"], "partial_modes": [], "comparison_data": {"node_tag": "T_HashJoin", "strategies": [], "text_names": ["Hash"], "initializer": "ExecInitHashJoin", "partial_modes": [], "memory_mechanism": "hash-batches", "parallel_callbacks": ["ExecHashJoinEstimate", "ExecHashJoinInitializeDSM", "ExecHashJoinInitializeWorker", "ExecHashJoinReInitializeDSM"]}, "comparison_hash": "f721dd250dc8d1d06a3d3dc814d77a301b3c9b8eb383affa6abbdd7a95c634df", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeHashjoin.c"}, "parallel_callbacks": ["ExecHashJoinEstimate", "ExecHashJoinInitializeDSM", "ExecHashJoinInitializeWorker", "ExecHashJoinReInitializeDSM"]}}}, "snapshot": {"facts": [{"label": "Core node tag", "value": "T_HashJoin"}, {"label": "Structured EXPLAIN Node Type", "value": "Hash Join"}, {"label": "Inputs", "value": "Outer child and inner Hash plan"}, {"label": "Output", "value": "Joined tuples according to the selected join type"}, {"label": "Executor initializer", "value": "ExecInitHashJoin"}, {"label": "Memory mechanism", "value": "hash-batches"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "4a72b0db253c1905c415462cfe56a2327ac65507c607b6d699d2717fa624ad56", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "4a72b0db253c1905c415462cfe56a2327ac65507c607b6d699d2717fa624ad56", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}], "mechanism": "hash-batches", "description": "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.", "source_notes": ["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."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Hash", "identity": "Hash Join"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=18", "label": "EXPLAIN"}, {"url": "/docs/18/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/18/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_hashjoin/?v=18", "label": "enable_hashjoin"}, {"url": "/wiki/guc/work_mem/?v=18", "label": "work_mem"}], "release": {"ref": "PostgreSQL 18.6 source archive", "label": "18.6", "major": "18", "channel": "stable", "revision": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f", "source_url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "line": 1428, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1428", "sha256": "34c86d6070224a0e981efef51f79101d6d505e5874f1684ace183034bab14bb4", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "line": 307, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:307", "sha256": "f8a06a3f539077249b20664b2812433db6d7bd12b2c0ca633525db43d06f112a", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/backend/executor/nodeHashjoin.c", "label": "src/backend/executor/nodeHashjoin.c", "sha256": "4a72b0db253c1905c415462cfe56a2327ac65507c607b6d699d2717fa624ad56", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "52422b327a8049fbbb20d8b96008a0fc0a6fafa60f7eff3c695d5b2e83830120", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "/docs/18/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 18.6 \u00b7 using-explain", "sha256": "60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed"}, {"url": "/docs/18/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 18.6 \u00b7 using-explain", "sha256": "60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed"}, {"url": "/docs/18/parallel-plans.html#PARALLEL-JOINS", "path": "parallel-plans.html", "label": "PostgreSQL 18.6 \u00b7 parallel-plans", "sha256": "62207d207bead82b01b59dc119c4f95856f08655cc11d4699a05a40867ed2070"}], "node_tag": "T_HashJoin", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["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."]}, {"title": "Memory and temporary storage", "paragraphs": ["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."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["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."]}, {"title": "Same-version manual discussion", "paragraphs": ["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."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;\n\n QUERY PLAN\n------------------------------------------------------------------------------------------\n Hash Join (cost=226.23..709.73 rows=100 width=488)\n Hash Cond: (t2.unique2 = t1.unique2)\n -> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244)\n -> Hash (cost=224.98..224.98 rows=100 width=244)\n -> Bitmap Heap Scan on tenk1 t1 (cost=5.06..224.98 rows=100 width=244)\n Recheck Cond: (unique1 < 100)\n -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0)\n Index Cond: (unique1 < 100)", "source": {"url": "/docs/18/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 18.6 \u00b7 using-explain", "sha256": "60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed"}, "paragraphs": ["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:"]}, {"code": "SET enable_mergejoin = off;\n\nEXPLAIN SELECT *\nFROM tenk1 t1, onek t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;\n\n QUERY PLAN\n------------------------------------------------------------------------------------------\n Hash Join (cost=226.23..344.08 rows=10 width=488)\n Hash Cond: (t2.unique2 = t1.unique2)\n -> Seq Scan on onek t2 (cost=0.00..114.00 rows=1000 width=244)\n -> Hash (cost=224.98..224.98 rows=100 width=244)\n -> Bitmap Heap Scan on tenk1 t1 (cost=5.06..224.98 rows=100 width=244)\n Recheck Cond: (unique1 < 100)\n -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0)\n Index Cond: (unique1 < 100)", "source": {"url": "/docs/18/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 18.6 \u00b7 using-explain", "sha256": "60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed"}, "paragraphs": ["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"]}]}, {"title": "Executor implementation notes", "paragraphs": ["This is based on the \"hybrid hash join\" algorithm described shortly in the following page", "\"An Adaptive Hash Join Algorithm for Multiuser Environments\" Hansj\u00f6rg Zeller; Jim Gray (1990). Proceedings of the 16th VLDB conference. Brisbane: 186\u2013197.", "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."]}, {"code": "case T_HashJoin:\n\t\t\tpname = \"Hash\";\t\t/* \"Join\" gets added by jointype switch */\n\t\t\tsname = \"Hash Join\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Probes a hash table built from its inner input while reading its outer input."], "evidence_kind": "source and documentation", "explain_names": ["Hash"], "partial_modes": [], "comparison_data": {"node_tag": "T_HashJoin", "strategies": [], "text_names": ["Hash"], "initializer": "ExecInitHashJoin", "partial_modes": [], "memory_mechanism": "hash-batches", "parallel_callbacks": ["ExecHashJoinEstimate", "ExecHashJoinInitializeDSM", "ExecHashJoinInitializeWorker", "ExecHashJoinReInitializeDSM"]}, "comparison_hash": "f721dd250dc8d1d06a3d3dc814d77a301b3c9b8eb383affa6abbdd7a95c634df", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeHashjoin.c"}, "parallel_callbacks": ["ExecHashJoinEstimate", "ExecHashJoinInitializeDSM", "ExecHashJoinInitializeWorker", "ExecHashJoinReInitializeDSM"]}, "comparison": {"left": "17", "right": "18", "status": "unchanged", "diff": ""}}