{"kind": "plan", "major": "18", "item": {"slug": "hash", "name": "Hash", "name_zh": "Hash", "category": "Join", "summary": "Builds the hash table consumed by a hash join.", "aliases": ["Hash", "T_Hash"], "content_hash": "6f465636f139b620089b2f2ec1bae20c244768d30189a0dd1b76b251e9ca4410", "versions": {"10": {"facts": [{"label": "Core node tag", "value": "T_Hash"}, {"label": "Structured EXPLAIN Node Type", "value": "Hash"}, {"label": "Inputs", "value": "The hash join inner child plan"}, {"label": "Output", "value": "Hash table, not a normal tuple stream"}, {"label": "Executor initializer", "value": "ExecInitHash"}, {"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/nodeHash.c", "label": "src/backend/executor/nodeHash.c", "sha256": "b1da97b777f8d177ac8a2c28c9fcc9e27f90813b069179ba7009c97018bd2a41", "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": ["Set nbuckets to achieve an average bucket load of NTUP_PER_BUCKET when memory is filled, assuming a single batch; but limit the value so that the pointer arrays we'll try to allocate do not exceed work_mem nor MaxAllocSize.", "If there's not enough space to store the projected number of tuples and the required bucket headers, we will need multiple batches.", "Estimate the number of buckets we'll want to have when work_mem is entirely full. Each bucket will contain a bucket pointer plus NTUP_PER_BUCKET tuples, whose projected size already includes overhead for the hash code, pointer to the next tuple, etc.", "Buckets are simple pointers to hashjoin tuples, while tupsize includes the pointer, hash code, and MinimalTupleData. So buckets should never really exceed 25% of work_mem (even for NTUP_PER_BUCKET=1); except maybe for work_mem values that are not 2^N bytes, where we might get more because of doubling. So let's look for 50% here."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Hash", "identity": "Hash"}], "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"}, {"url": "/wiki/guc/work_mem/?v=10", "label": "work_mem"}], "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": 1095, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1095", "sha256": "a785298532047cfeda969e78c3597a343dc1c56d61ba85830b0f16a02a14b5a1", "archive_sha256": "94a4b2528372458e5662c18d406629266667c437198160a18cdfd2c4a4d6eee9"}, {"url": "https://ftp.postgresql.org/pub/source/v10.23/postgresql-10.23.tar.bz2", "line": 348, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:348", "sha256": "cea76648bb38ae55f18f989768bee1a4ee025691ceea0f86bccb29dcdc166acc", "archive_sha256": "94a4b2528372458e5662c18d406629266667c437198160a18cdfd2c4a4d6eee9"}, {"url": "https://ftp.postgresql.org/pub/source/v10.23/postgresql-10.23.tar.bz2", "path": "src/backend/executor/nodeHash.c", "label": "src/backend/executor/nodeHash.c", "sha256": "b1da97b777f8d177ac8a2c28c9fcc9e27f90813b069179ba7009c97018bd2a41", "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": "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": "/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_Hash", "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.", "Set nbuckets to achieve an average bucket load of NTUP_PER_BUCKET when memory is filled, assuming a single batch; but limit the value so that the pointer arrays we'll try to allocate do not exceed work_mem nor MaxAllocSize.", "If there's not enough space to store the projected number of tuples and the required bucket headers, we will need multiple batches.", "Estimate the number of buckets we'll want to have when work_mem is entirely full. Each bucket will contain a bucket pointer plus NTUP_PER_BUCKET tuples, whose projected size already includes overhead for the hash code, pointer to the next tuple, etc.", "Buckets are simple pointers to hashjoin tuples, while tupsize includes the pointer, hash code, and MinimalTupleData. So buckets should never really exceed 25% of work_mem (even for NTUP_PER_BUCKET=1); except maybe for work_mem values that are not 2^N bytes, where we might get more because of doubling. So let's look for 50% here."]}, {"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": ["build hash table for hashjoin, doing partitioning if more than one batch is required.", "get all inner tuples and insert into the hash table (or temp files)", "Account for the buckets in spaceUsed (reported in EXPLAIN ANALYZE)", "We do not return the hash table directly because it's not a subtype of Node, and so would violate the MultiExecProcNode API. Instead, our parent Hashjoin node is expected to know how to fish it out of our node state. Ugly but not really worth cleaning up, since Hashjoin knows quite a bit more about Hash besides that.", "initialize tuple type. no need to initialize projection info because this node doesn't do projections"]}, {"code": "case T_Hash:\n\t\t\tpname = sname = \"Hash\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Builds the hash table consumed by a hash join."], "evidence_kind": "source and documentation", "explain_names": ["Hash"], "partial_modes": [], "comparison_data": {"node_tag": "T_Hash", "strategies": [], "text_names": ["Hash"], "initializer": "ExecInitHash", "partial_modes": [], "memory_mechanism": "hash-batches", "parallel_callbacks": []}, "comparison_hash": "f2bd6432fff5ff0e5b0364cd2695c35ec4d211083db694a822764b1c330fbdbf", "explain_prefixes": ["Parallel"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeHash.c"}, "parallel_callbacks": []}, "11": {"facts": [{"label": "Core node tag", "value": "T_Hash"}, {"label": "Structured EXPLAIN Node Type", "value": "Hash"}, {"label": "Inputs", "value": "The hash join inner child plan"}, {"label": "Output", "value": "Hash table, not a normal tuple stream"}, {"label": "Executor initializer", "value": "ExecInitHash"}, {"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/nodeHash.c", "label": "src/backend/executor/nodeHash.c", "sha256": "584e47f972f0aaaf6d3613e0aa95e99ef147ba538ebe7a3c2351f14a993fcdfa", "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": ["It's time to begin hashing, or if we just arrived here then hashing is already underway, so join in that effort. While hashing we have to be prepared to help increase the number of batches or buckets at any time, and if we arrived here when that was already underway we'll have to help complete that work immediately so that it's safe to access batches and buckets below.", "Make sure that any tuples we wrote to disk are visible to others before anyone tries to load them.", "Elect one backend to disable any further growth. Batches are now fixed. While building them we made sure they'd fit in our memory budget when we load them back in later (or we tried to do that and gave up because we detected extreme skew).", "Parallel Hash tries to use the combined work_mem of all workers to avoid the need to batch. If that won't work, it falls back to work_mem per worker and tries to process batches in parallel."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Hash", "identity": "Hash"}], "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": 1220, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1220", "sha256": "9df8400c1a4377179572ceb916d6020fca4e2760f74bf416d77ed97476523bbd", "archive_sha256": "2cb7c97d7a0d7278851bbc9c61f467b69c094c72b81740b751108e7892ebe1f0"}, {"url": "https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.bz2", "line": 348, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:348", "sha256": "95ef4d4a5df4c29f14af9763fae2c530449bdacdf3853d9ff297adf1fed6153b", "archive_sha256": "2cb7c97d7a0d7278851bbc9c61f467b69c094c72b81740b751108e7892ebe1f0"}, {"url": "https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.bz2", "path": "src/backend/executor/nodeHash.c", "label": "src/backend/executor/nodeHash.c", "sha256": "584e47f972f0aaaf6d3613e0aa95e99ef147ba538ebe7a3c2351f14a993fcdfa", "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": "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": "/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_Hash", "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.", "It's time to begin hashing, or if we just arrived here then hashing is already underway, so join in that effort. While hashing we have to be prepared to help increase the number of batches or buckets at any time, and if we arrived here when that was already underway we'll have to help complete that work immediately so that it's safe to access batches and buckets below.", "Make sure that any tuples we wrote to disk are visible to others before anyone tries to load them.", "Elect one backend to disable any further growth. Batches are now fixed. While building them we made sure they'd fit in our memory budget when we load them back in later (or we tried to do that and gave up because we detected extreme skew).", "Parallel Hash tries to use the combined work_mem of all workers to avoid the need to batch. If that won't work, it falls back to work_mem per worker and tries to process batches in parallel."]}, {"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: ExecHashEstimate, ExecHashInitializeDSM, ExecHashInitializeWorker, ExecHashRetrieveInstrumentation."]}, {"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": ["build hash table for hashjoin, doing partitioning if more than one batch is required.", "We do not return the hash table directly because it's not a subtype of Node, and so would violate the MultiExecProcNode API. Instead, our parent Hashjoin node is expected to know how to fish it out of our node state. Ugly but not really worth cleaning up, since Hashjoin knows quite a bit more about Hash besides that.", "parallel-oblivious version, building a backend-private hash table and (if necessary) batch files.", "get all inner tuples and insert into the hash table (or temp files)", "Account for the buckets in spaceUsed (reported in EXPLAIN ANALYZE)"]}, {"code": "case T_Hash:\n\t\t\tpname = sname = \"Hash\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Builds the hash table consumed by a hash join."], "evidence_kind": "source and documentation", "explain_names": ["Hash"], "partial_modes": [], "comparison_data": {"node_tag": "T_Hash", "strategies": [], "text_names": ["Hash"], "initializer": "ExecInitHash", "partial_modes": [], "memory_mechanism": "hash-batches", "parallel_callbacks": ["ExecHashEstimate", "ExecHashInitializeDSM", "ExecHashInitializeWorker", "ExecHashRetrieveInstrumentation"]}, "comparison_hash": "2d9def2bb1ea7366b3f68169fa01faf4e00149c2373e67133c3ac0d69137032f", "explain_prefixes": ["Parallel"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeHash.c"}, "parallel_callbacks": ["ExecHashEstimate", "ExecHashInitializeDSM", "ExecHashInitializeWorker", "ExecHashRetrieveInstrumentation"]}, "12": {"facts": [{"label": "Core node tag", "value": "T_Hash"}, {"label": "Structured EXPLAIN Node Type", "value": "Hash"}, {"label": "Inputs", "value": "The hash join inner child plan"}, {"label": "Output", "value": "Hash table, not a normal tuple stream"}, {"label": "Executor initializer", "value": "ExecInitHash"}, {"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/nodeHash.c", "label": "src/backend/executor/nodeHash.c", "sha256": "6ad5cef1ad0412d67ecfacc4a8ad353e0dfffffd23934e27ba0e1e3f8fc60951", "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": ["It's time to begin hashing, or if we just arrived here then hashing is already underway, so join in that effort. While hashing we have to be prepared to help increase the number of batches or buckets at any time, and if we arrived here when that was already underway we'll have to help complete that work immediately so that it's safe to access batches and buckets below.", "Make sure that any tuples we wrote to disk are visible to others before anyone tries to load them.", "Elect one backend to disable any further growth. Batches are now fixed. While building them we made sure they'd fit in our memory budget when we load them back in later (or we tried to do that and gave up because we detected extreme skew).", "Parallel Hash tries to use the combined work_mem of all workers to avoid the need to batch. If that won't work, it falls back to work_mem per worker and tries to process batches in parallel."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Hash", "identity": "Hash"}], "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": 1291, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1291", "sha256": "d02ea84fdaa201de5d9360645a9f24bfbd2c31f7d45a639e09560ac0e6b6471d", "archive_sha256": "8df3c0474782589d3c6f374b5133b1bd14d168086edbc13c6e72e67dd4527a3b"}, {"url": "https://ftp.postgresql.org/pub/source/v12.22/postgresql-12.22.tar.bz2", "line": 348, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:348", "sha256": "311b17379fe54e3f342fe5ad41c43afbdfa1b844978db2bb2eb22b82520d3256", "archive_sha256": "8df3c0474782589d3c6f374b5133b1bd14d168086edbc13c6e72e67dd4527a3b"}, {"url": "https://ftp.postgresql.org/pub/source/v12.22/postgresql-12.22.tar.bz2", "path": "src/backend/executor/nodeHash.c", "label": "src/backend/executor/nodeHash.c", "sha256": "6ad5cef1ad0412d67ecfacc4a8ad353e0dfffffd23934e27ba0e1e3f8fc60951", "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": "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": "/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_Hash", "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.", "It's time to begin hashing, or if we just arrived here then hashing is already underway, so join in that effort. While hashing we have to be prepared to help increase the number of batches or buckets at any time, and if we arrived here when that was already underway we'll have to help complete that work immediately so that it's safe to access batches and buckets below.", "Make sure that any tuples we wrote to disk are visible to others before anyone tries to load them.", "Elect one backend to disable any further growth. Batches are now fixed. While building them we made sure they'd fit in our memory budget when we load them back in later (or we tried to do that and gave up because we detected extreme skew).", "Parallel Hash tries to use the combined work_mem of all workers to avoid the need to batch. If that won't work, it falls back to work_mem per worker and tries to process batches in parallel."]}, {"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: ExecHashEstimate, ExecHashInitializeDSM, ExecHashInitializeWorker, ExecHashRetrieveInstrumentation."]}, {"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": ["build hash table for hashjoin, doing partitioning if more than one batch is required.", "We do not return the hash table directly because it's not a subtype of Node, and so would violate the MultiExecProcNode API. Instead, our parent Hashjoin node is expected to know how to fish it out of our node state. Ugly but not really worth cleaning up, since Hashjoin knows quite a bit more about Hash besides that.", "parallel-oblivious version, building a backend-private hash table and (if necessary) batch files.", "Get all tuples from the node below the Hash node and insert into the hash table (or temp files).", "Account for the buckets in spaceUsed (reported in EXPLAIN ANALYZE)"]}, {"code": "case T_Hash:\n\t\t\tpname = sname = \"Hash\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Builds the hash table consumed by a hash join."], "evidence_kind": "source and documentation", "explain_names": ["Hash"], "partial_modes": [], "comparison_data": {"node_tag": "T_Hash", "strategies": [], "text_names": ["Hash"], "initializer": "ExecInitHash", "partial_modes": [], "memory_mechanism": "hash-batches", "parallel_callbacks": ["ExecHashEstimate", "ExecHashInitializeDSM", "ExecHashInitializeWorker", "ExecHashRetrieveInstrumentation"]}, "comparison_hash": "2d9def2bb1ea7366b3f68169fa01faf4e00149c2373e67133c3ac0d69137032f", "explain_prefixes": ["Parallel"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeHash.c"}, "parallel_callbacks": ["ExecHashEstimate", "ExecHashInitializeDSM", "ExecHashInitializeWorker", "ExecHashRetrieveInstrumentation"]}, "13": {"facts": [{"label": "Core node tag", "value": "T_Hash"}, {"label": "Structured EXPLAIN Node Type", "value": "Hash"}, {"label": "Inputs", "value": "The hash join inner child plan"}, {"label": "Output", "value": "Hash table, not a normal tuple stream"}, {"label": "Executor initializer", "value": "ExecInitHash"}, {"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/nodeHash.c", "label": "src/backend/executor/nodeHash.c", "sha256": "ca59ef331aeceaeced6d83459a4ca7b5d2f88e0f701d880f11ac2356dc474806", "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": ["It's time to begin hashing, or if we just arrived here then hashing is already underway, so join in that effort. While hashing we have to be prepared to help increase the number of batches or buckets at any time, and if we arrived here when that was already underway we'll have to help complete that work immediately so that it's safe to access batches and buckets below.", "Make sure that any tuples we wrote to disk are visible to others before anyone tries to load them.", "Elect one backend to disable any further growth. Batches are now fixed. While building them we made sure they'd fit in our memory budget when we load them back in later (or we tried to do that and gave up because we detected extreme skew).", "Parallel Hash tries to use the combined hash_mem of all workers to avoid the need to batch. If that won't work, it falls back to hash_mem per worker and tries to process batches in parallel."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Hash", "identity": "Hash"}], "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"}, {"url": "/wiki/guc/work_mem/?v=13", "label": "work_mem"}, {"url": "/wiki/guc/hash_mem_multiplier/?v=13", "label": "hash_mem_multiplier"}], "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": 1352, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1352", "sha256": "541713e0e7f1c9cc352c2b6028964d440c19d2678a4463000094c24a88c1e730", "archive_sha256": "6ec3c82726af92b7dec873fa1cdf881eca92a4219787dfad05acb6b10e041fd6"}, {"url": "https://ftp.postgresql.org/pub/source/v13.23/postgresql-13.23.tar.bz2", "line": 353, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:353", "sha256": "d085ee99acfa00587e6ade3a1d9f8108a0566beedbbee3f54a50c9fc0cc2e875", "archive_sha256": "6ec3c82726af92b7dec873fa1cdf881eca92a4219787dfad05acb6b10e041fd6"}, {"url": "https://ftp.postgresql.org/pub/source/v13.23/postgresql-13.23.tar.bz2", "path": "src/backend/executor/nodeHash.c", "label": "src/backend/executor/nodeHash.c", "sha256": "ca59ef331aeceaeced6d83459a4ca7b5d2f88e0f701d880f11ac2356dc474806", "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": "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": "/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_Hash", "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.", "It's time to begin hashing, or if we just arrived here then hashing is already underway, so join in that effort. While hashing we have to be prepared to help increase the number of batches or buckets at any time, and if we arrived here when that was already underway we'll have to help complete that work immediately so that it's safe to access batches and buckets below.", "Make sure that any tuples we wrote to disk are visible to others before anyone tries to load them.", "Elect one backend to disable any further growth. Batches are now fixed. While building them we made sure they'd fit in our memory budget when we load them back in later (or we tried to do that and gave up because we detected extreme skew).", "Parallel Hash tries to use the combined hash_mem of all workers to avoid the need to batch. If that won't work, it falls back to hash_mem per worker and tries to process batches in parallel."]}, {"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: ExecHashEstimate, ExecHashInitializeDSM, ExecHashInitializeWorker, ExecHashRetrieveInstrumentation."]}, {"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": ["build hash table for hashjoin, doing partitioning if more than one batch is required.", "We do not return the hash table directly because it's not a subtype of Node, and so would violate the MultiExecProcNode API. Instead, our parent Hashjoin node is expected to know how to fish it out of our node state. Ugly but not really worth cleaning up, since Hashjoin knows quite a bit more about Hash besides that.", "parallel-oblivious version, building a backend-private hash table and (if necessary) batch files.", "Get all tuples from the node below the Hash node and insert into the hash table (or temp files).", "Account for the buckets in spaceUsed (reported in EXPLAIN ANALYZE)"]}, {"code": "case T_Hash:\n\t\t\tpname = sname = \"Hash\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Builds the hash table consumed by a hash join."], "evidence_kind": "source and documentation", "explain_names": ["Hash"], "partial_modes": [], "comparison_data": {"node_tag": "T_Hash", "strategies": [], "text_names": ["Hash"], "initializer": "ExecInitHash", "partial_modes": [], "memory_mechanism": "hash-batches", "parallel_callbacks": ["ExecHashEstimate", "ExecHashInitializeDSM", "ExecHashInitializeWorker", "ExecHashRetrieveInstrumentation"]}, "comparison_hash": "2d9def2bb1ea7366b3f68169fa01faf4e00149c2373e67133c3ac0d69137032f", "explain_prefixes": ["Parallel"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeHash.c"}, "parallel_callbacks": ["ExecHashEstimate", "ExecHashInitializeDSM", "ExecHashInitializeWorker", "ExecHashRetrieveInstrumentation"]}, "14": {"facts": [{"label": "Core node tag", "value": "T_Hash"}, {"label": "Structured EXPLAIN Node Type", "value": "Hash"}, {"label": "Inputs", "value": "The hash join inner child plan"}, {"label": "Output", "value": "Hash table, not a normal tuple stream"}, {"label": "Executor initializer", "value": "ExecInitHash"}, {"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/nodeHash.c", "label": "src/backend/executor/nodeHash.c", "sha256": "0e0309fc36eeb90b76df82b43548160611bca5823b7b0527c0dbac4083a39bc6", "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": ["It's time to begin hashing, or if we just arrived here then hashing is already underway, so join in that effort. While hashing we have to be prepared to help increase the number of batches or buckets at any time, and if we arrived here when that was already underway we'll have to help complete that work immediately so that it's safe to access batches and buckets below.", "Make sure that any tuples we wrote to disk are visible to others before anyone tries to load them.", "Elect one backend to disable any further growth. Batches are now fixed. While building them we made sure they'd fit in our memory budget when we load them back in later (or we tried to do that and gave up because we detected extreme skew).", "Parallel Hash tries to use the combined hash_mem of all workers to avoid the need to batch. If that won't work, it falls back to hash_mem per worker and tries to process batches in parallel."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Hash", "identity": "Hash"}], "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"}, {"url": "/wiki/guc/work_mem/?v=14", "label": "work_mem"}, {"url": "/wiki/guc/hash_mem_multiplier/?v=14", "label": "hash_mem_multiplier"}], "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": 1394, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1394", "sha256": "e091be4e2a083b8dea39ccd09beedede22c1716ef974da66c214a44f48be8c41", "archive_sha256": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897"}, {"url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "line": 365, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:365", "sha256": "72da1c5ad457f1d92a39ab73531701794df858419e3b89d6e6cb7079634e68fa", "archive_sha256": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897"}, {"url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "path": "src/backend/executor/nodeHash.c", "label": "src/backend/executor/nodeHash.c", "sha256": "0e0309fc36eeb90b76df82b43548160611bca5823b7b0527c0dbac4083a39bc6", "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": "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": "/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_Hash", "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.", "It's time to begin hashing, or if we just arrived here then hashing is already underway, so join in that effort. While hashing we have to be prepared to help increase the number of batches or buckets at any time, and if we arrived here when that was already underway we'll have to help complete that work immediately so that it's safe to access batches and buckets below.", "Make sure that any tuples we wrote to disk are visible to others before anyone tries to load them.", "Elect one backend to disable any further growth. Batches are now fixed. While building them we made sure they'd fit in our memory budget when we load them back in later (or we tried to do that and gave up because we detected extreme skew).", "Parallel Hash tries to use the combined hash_mem of all workers to avoid the need to batch. If that won't work, it falls back to hash_mem per worker and tries to process batches in parallel."]}, {"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: ExecHashEstimate, ExecHashInitializeDSM, ExecHashInitializeWorker, ExecHashRetrieveInstrumentation."]}, {"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": ["build hash table for hashjoin, doing partitioning if more than one batch is required.", "We do not return the hash table directly because it's not a subtype of Node, and so would violate the MultiExecProcNode API. Instead, our parent Hashjoin node is expected to know how to fish it out of our node state. Ugly but not really worth cleaning up, since Hashjoin knows quite a bit more about Hash besides that.", "parallel-oblivious version, building a backend-private hash table and (if necessary) batch files.", "Get all tuples from the node below the Hash node and insert into the hash table (or temp files).", "Account for the buckets in spaceUsed (reported in EXPLAIN ANALYZE)"]}, {"code": "case T_Hash:\n\t\t\tpname = sname = \"Hash\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Builds the hash table consumed by a hash join."], "evidence_kind": "source and documentation", "explain_names": ["Hash"], "partial_modes": [], "comparison_data": {"node_tag": "T_Hash", "strategies": [], "text_names": ["Hash"], "initializer": "ExecInitHash", "partial_modes": [], "memory_mechanism": "hash-batches", "parallel_callbacks": ["ExecHashEstimate", "ExecHashInitializeDSM", "ExecHashInitializeWorker", "ExecHashRetrieveInstrumentation"]}, "comparison_hash": "2d9def2bb1ea7366b3f68169fa01faf4e00149c2373e67133c3ac0d69137032f", "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/nodeHash.c"}, "parallel_callbacks": ["ExecHashEstimate", "ExecHashInitializeDSM", "ExecHashInitializeWorker", "ExecHashRetrieveInstrumentation"]}, "15": {"facts": [{"label": "Core node tag", "value": "T_Hash"}, {"label": "Structured EXPLAIN Node Type", "value": "Hash"}, {"label": "Inputs", "value": "The hash join inner child plan"}, {"label": "Output", "value": "Hash table, not a normal tuple stream"}, {"label": "Executor initializer", "value": "ExecInitHash"}, {"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/nodeHash.c", "label": "src/backend/executor/nodeHash.c", "sha256": "1ee3a4d7e1c9aa7a960ec04b3f7d65ba9495341b2240173f06407f2ad9453904", "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": ["It's time to begin hashing, or if we just arrived here then hashing is already underway, so join in that effort. While hashing we have to be prepared to help increase the number of batches or buckets at any time, and if we arrived here when that was already underway we'll have to help complete that work immediately so that it's safe to access batches and buckets below.", "Make sure that any tuples we wrote to disk are visible to others before anyone tries to load them.", "Elect one backend to disable any further growth. Batches are now fixed. While building them we made sure they'd fit in our memory budget when we load them back in later (or we tried to do that and gave up because we detected extreme skew).", "Parallel Hash tries to use the combined hash_mem of all workers to avoid the need to batch. If that won't work, it falls back to hash_mem per worker and tries to process batches in parallel."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Hash", "identity": "Hash"}], "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"}, {"url": "/wiki/guc/work_mem/?v=15", "label": "work_mem"}, {"url": "/wiki/guc/hash_mem_multiplier/?v=15", "label": "hash_mem_multiplier"}], "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": 1397, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1397", "sha256": "bb3b442d0f1b098aa8707335250102f027a596cd94117308bd16d1d36b258f5c", "archive_sha256": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89"}, {"url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "line": 365, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:365", "sha256": "19836c50a272741a4eac653541e655437c2e00710a541e5348d6a277d0669d7c", "archive_sha256": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89"}, {"url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "path": "src/backend/executor/nodeHash.c", "label": "src/backend/executor/nodeHash.c", "sha256": "1ee3a4d7e1c9aa7a960ec04b3f7d65ba9495341b2240173f06407f2ad9453904", "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": "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": "/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_Hash", "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.", "It's time to begin hashing, or if we just arrived here then hashing is already underway, so join in that effort. While hashing we have to be prepared to help increase the number of batches or buckets at any time, and if we arrived here when that was already underway we'll have to help complete that work immediately so that it's safe to access batches and buckets below.", "Make sure that any tuples we wrote to disk are visible to others before anyone tries to load them.", "Elect one backend to disable any further growth. Batches are now fixed. While building them we made sure they'd fit in our memory budget when we load them back in later (or we tried to do that and gave up because we detected extreme skew).", "Parallel Hash tries to use the combined hash_mem of all workers to avoid the need to batch. If that won't work, it falls back to hash_mem per worker and tries to process batches in parallel."]}, {"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: ExecHashEstimate, ExecHashInitializeDSM, ExecHashInitializeWorker, ExecHashRetrieveInstrumentation."]}, {"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": ["build hash table for hashjoin, doing partitioning if more than one batch is required.", "We do not return the hash table directly because it's not a subtype of Node, and so would violate the MultiExecProcNode API. Instead, our parent Hashjoin node is expected to know how to fish it out of our node state. Ugly but not really worth cleaning up, since Hashjoin knows quite a bit more about Hash besides that.", "parallel-oblivious version, building a backend-private hash table and (if necessary) batch files.", "Get all tuples from the node below the Hash node and insert into the hash table (or temp files).", "Account for the buckets in spaceUsed (reported in EXPLAIN ANALYZE)"]}, {"code": "case T_Hash:\n\t\t\tpname = sname = \"Hash\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Builds the hash table consumed by a hash join."], "evidence_kind": "source and documentation", "explain_names": ["Hash"], "partial_modes": [], "comparison_data": {"node_tag": "T_Hash", "strategies": [], "text_names": ["Hash"], "initializer": "ExecInitHash", "partial_modes": [], "memory_mechanism": "hash-batches", "parallel_callbacks": ["ExecHashEstimate", "ExecHashInitializeDSM", "ExecHashInitializeWorker", "ExecHashRetrieveInstrumentation"]}, "comparison_hash": "2d9def2bb1ea7366b3f68169fa01faf4e00149c2373e67133c3ac0d69137032f", "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/nodeHash.c"}, "parallel_callbacks": ["ExecHashEstimate", "ExecHashInitializeDSM", "ExecHashInitializeWorker", "ExecHashRetrieveInstrumentation"]}, "16": {"facts": [{"label": "Core node tag", "value": "T_Hash"}, {"label": "Structured EXPLAIN Node Type", "value": "Hash"}, {"label": "Inputs", "value": "The hash join inner child plan"}, {"label": "Output", "value": "Hash table, not a normal tuple stream"}, {"label": "Executor initializer", "value": "ExecInitHash"}, {"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/nodeHash.c", "label": "src/backend/executor/nodeHash.c", "sha256": "11e46dabda4ba96ac9d0ed51abcb08785e3e7a39d1296ff57c66193fbaa7c0bb", "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": ["It's time to begin hashing, or if we just arrived here then hashing is already underway, so join in that effort. While hashing we have to be prepared to help increase the number of batches or buckets at any time, and if we arrived here when that was already underway we'll have to help complete that work immediately so that it's safe to access batches and buckets below.", "Make sure that any tuples we wrote to disk are visible to others before anyone tries to load them.", "Elect one backend to disable any further growth. Batches are now fixed. While building them we made sure they'd fit in our memory budget when we load them back in later (or we tried to do that and gave up because we detected extreme skew).", "Parallel Hash tries to use the combined hash_mem of all workers to avoid the need to batch. If that won't work, it falls back to hash_mem per worker and tries to process batches in parallel."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Hash", "identity": "Hash"}], "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"}, {"url": "/wiki/guc/hash_mem_multiplier/?v=16", "label": "hash_mem_multiplier"}], "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": 1430, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1430", "sha256": "8e017f0116dbea471339b40c37a667cc9f95039e7e0329c783e5e8ce194de7e1", "archive_sha256": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed"}, {"url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "line": 365, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:365", "sha256": "e48c08e555f8cb4e4bb43df516c4b8906ce9bc374b2a745d98a1fc8c22cc5099", "archive_sha256": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed"}, {"url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "path": "src/backend/executor/nodeHash.c", "label": "src/backend/executor/nodeHash.c", "sha256": "11e46dabda4ba96ac9d0ed51abcb08785e3e7a39d1296ff57c66193fbaa7c0bb", "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": "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": "/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_Hash", "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.", "It's time to begin hashing, or if we just arrived here then hashing is already underway, so join in that effort. While hashing we have to be prepared to help increase the number of batches or buckets at any time, and if we arrived here when that was already underway we'll have to help complete that work immediately so that it's safe to access batches and buckets below.", "Make sure that any tuples we wrote to disk are visible to others before anyone tries to load them.", "Elect one backend to disable any further growth. Batches are now fixed. While building them we made sure they'd fit in our memory budget when we load them back in later (or we tried to do that and gave up because we detected extreme skew).", "Parallel Hash tries to use the combined hash_mem of all workers to avoid the need to batch. If that won't work, it falls back to hash_mem per worker and tries to process batches in parallel."]}, {"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: ExecHashEstimate, ExecHashInitializeDSM, ExecHashInitializeWorker, ExecHashRetrieveInstrumentation."]}, {"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": ["build hash table for hashjoin, doing partitioning if more than one batch is required.", "We do not return the hash table directly because it's not a subtype of Node, and so would violate the MultiExecProcNode API. Instead, our parent Hashjoin node is expected to know how to fish it out of our node state. Ugly but not really worth cleaning up, since Hashjoin knows quite a bit more about Hash besides that.", "parallel-oblivious version, building a backend-private hash table and (if necessary) batch files.", "Get all tuples from the node below the Hash node and insert into the hash table (or temp files).", "Account for the buckets in spaceUsed (reported in EXPLAIN ANALYZE)"]}, {"code": "case T_Hash:\n\t\t\tpname = sname = \"Hash\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Builds the hash table consumed by a hash join."], "evidence_kind": "source and documentation", "explain_names": ["Hash"], "partial_modes": [], "comparison_data": {"node_tag": "T_Hash", "strategies": [], "text_names": ["Hash"], "initializer": "ExecInitHash", "partial_modes": [], "memory_mechanism": "hash-batches", "parallel_callbacks": ["ExecHashEstimate", "ExecHashInitializeDSM", "ExecHashInitializeWorker", "ExecHashRetrieveInstrumentation"]}, "comparison_hash": "2d9def2bb1ea7366b3f68169fa01faf4e00149c2373e67133c3ac0d69137032f", "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/nodeHash.c"}, "parallel_callbacks": ["ExecHashEstimate", "ExecHashInitializeDSM", "ExecHashInitializeWorker", "ExecHashRetrieveInstrumentation"]}, "17": {"facts": [{"label": "Core node tag", "value": "T_Hash"}, {"label": "Structured EXPLAIN Node Type", "value": "Hash"}, {"label": "Inputs", "value": "The hash join inner child plan"}, {"label": "Output", "value": "Hash table, not a normal tuple stream"}, {"label": "Executor initializer", "value": "ExecInitHash"}, {"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/nodeHash.c", "label": "src/backend/executor/nodeHash.c", "sha256": "fc8c8d41bc969dc7874fae77f4c09d6e92758b0aba943d8ae46e722fec0609a4", "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": ["It's time to begin hashing, or if we just arrived here then hashing is already underway, so join in that effort. While hashing we have to be prepared to help increase the number of batches or buckets at any time, and if we arrived here when that was already underway we'll have to help complete that work immediately so that it's safe to access batches and buckets below.", "Make sure that any tuples we wrote to disk are visible to others before anyone tries to load them.", "Elect one backend to disable any further growth. Batches are now fixed. While building them we made sure they'd fit in our memory budget when we load them back in later (or we tried to do that and gave up because we detected extreme skew).", "Parallel Hash tries to use the combined hash_mem of all workers to avoid the need to batch. If that won't work, it falls back to hash_mem per worker and tries to process batches in parallel."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Hash", "identity": "Hash"}], "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"}, {"url": "/wiki/guc/hash_mem_multiplier/?v=17", "label": "hash_mem_multiplier"}], "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": 1619, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1619", "sha256": "741251b1a3b6d269a52a673d42eb63b02e13a5872db7b359b137086ab21b63c8", "archive_sha256": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979"}, {"url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "line": 365, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:365", "sha256": "a77576e158b94cb01fa8c5174ba133004eabdd727660323f8afc66c8d2e757b8", "archive_sha256": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979"}, {"url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "path": "src/backend/executor/nodeHash.c", "label": "src/backend/executor/nodeHash.c", "sha256": "fc8c8d41bc969dc7874fae77f4c09d6e92758b0aba943d8ae46e722fec0609a4", "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": "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": "/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_Hash", "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.", "It's time to begin hashing, or if we just arrived here then hashing is already underway, so join in that effort. While hashing we have to be prepared to help increase the number of batches or buckets at any time, and if we arrived here when that was already underway we'll have to help complete that work immediately so that it's safe to access batches and buckets below.", "Make sure that any tuples we wrote to disk are visible to others before anyone tries to load them.", "Elect one backend to disable any further growth. Batches are now fixed. While building them we made sure they'd fit in our memory budget when we load them back in later (or we tried to do that and gave up because we detected extreme skew).", "Parallel Hash tries to use the combined hash_mem of all workers to avoid the need to batch. If that won't work, it falls back to hash_mem per worker and tries to process batches in parallel."]}, {"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: ExecHashEstimate, ExecHashInitializeDSM, ExecHashInitializeWorker, ExecHashRetrieveInstrumentation."]}, {"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": ["build hash table for hashjoin, doing partitioning if more than one batch is required.", "We do not return the hash table directly because it's not a subtype of Node, and so would violate the MultiExecProcNode API. Instead, our parent Hashjoin node is expected to know how to fish it out of our node state. Ugly but not really worth cleaning up, since Hashjoin knows quite a bit more about Hash besides that.", "parallel-oblivious version, building a backend-private hash table and (if necessary) batch files.", "Get all tuples from the node below the Hash node and insert into the hash table (or temp files).", "Account for the buckets in spaceUsed (reported in EXPLAIN ANALYZE)"]}, {"code": "case T_Hash:\n\t\t\tpname = sname = \"Hash\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Builds the hash table consumed by a hash join."], "evidence_kind": "source and documentation", "explain_names": ["Hash"], "partial_modes": [], "comparison_data": {"node_tag": "T_Hash", "strategies": [], "text_names": ["Hash"], "initializer": "ExecInitHash", "partial_modes": [], "memory_mechanism": "hash-batches", "parallel_callbacks": ["ExecHashEstimate", "ExecHashInitializeDSM", "ExecHashInitializeWorker", "ExecHashRetrieveInstrumentation"]}, "comparison_hash": "2d9def2bb1ea7366b3f68169fa01faf4e00149c2373e67133c3ac0d69137032f", "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/nodeHash.c"}, "parallel_callbacks": ["ExecHashEstimate", "ExecHashInitializeDSM", "ExecHashInitializeWorker", "ExecHashRetrieveInstrumentation"]}, "18": {"facts": [{"label": "Core node tag", "value": "T_Hash"}, {"label": "Structured EXPLAIN Node Type", "value": "Hash"}, {"label": "Inputs", "value": "The hash join inner child plan"}, {"label": "Output", "value": "Hash table, not a normal tuple stream"}, {"label": "Executor initializer", "value": "ExecInitHash"}, {"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/nodeHash.c", "label": "src/backend/executor/nodeHash.c", "sha256": "f83e9b10129450f2dc52ac7ea5147b7c51c12648ae1b3b3195bb3f0b4895fafc", "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": ["It's time to begin hashing, or if we just arrived here then hashing is already underway, so join in that effort. While hashing we have to be prepared to help increase the number of batches or buckets at any time, and if we arrived here when that was already underway we'll have to help complete that work immediately so that it's safe to access batches and buckets below.", "Make sure that any tuples we wrote to disk are visible to others before anyone tries to load them.", "Elect one backend to disable any further growth. Batches are now fixed. While building them we made sure they'd fit in our memory budget when we load them back in later (or we tried to do that and gave up because we detected extreme skew).", "Parallel Hash tries to use the combined hash_mem of all workers to avoid the need to batch. If that won't work, it falls back to hash_mem per worker and tries to process batches in parallel."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Hash", "identity": "Hash"}], "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"}, {"url": "/wiki/guc/hash_mem_multiplier/?v=18", "label": "hash_mem_multiplier"}], "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": 1604, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1604", "sha256": "34c86d6070224a0e981efef51f79101d6d505e5874f1684ace183034bab14bb4", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "line": 365, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:365", "sha256": "f8a06a3f539077249b20664b2812433db6d7bd12b2c0ca633525db43d06f112a", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/backend/executor/nodeHash.c", "label": "src/backend/executor/nodeHash.c", "sha256": "f83e9b10129450f2dc52ac7ea5147b7c51c12648ae1b3b3195bb3f0b4895fafc", "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": "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": "/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_Hash", "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.", "It's time to begin hashing, or if we just arrived here then hashing is already underway, so join in that effort. While hashing we have to be prepared to help increase the number of batches or buckets at any time, and if we arrived here when that was already underway we'll have to help complete that work immediately so that it's safe to access batches and buckets below.", "Make sure that any tuples we wrote to disk are visible to others before anyone tries to load them.", "Elect one backend to disable any further growth. Batches are now fixed. While building them we made sure they'd fit in our memory budget when we load them back in later (or we tried to do that and gave up because we detected extreme skew).", "Parallel Hash tries to use the combined hash_mem of all workers to avoid the need to batch. If that won't work, it falls back to hash_mem per worker and tries to process batches in parallel."]}, {"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: ExecHashEstimate, ExecHashInitializeDSM, ExecHashInitializeWorker, ExecHashRetrieveInstrumentation."]}, {"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": ["build hash table for hashjoin, doing partitioning if more than one batch is required.", "We do not return the hash table directly because it's not a subtype of Node, and so would violate the MultiExecProcNode API. Instead, our parent Hashjoin node is expected to know how to fish it out of our node state. Ugly but not really worth cleaning up, since Hashjoin knows quite a bit more about Hash besides that.", "parallel-oblivious version, building a backend-private hash table and (if necessary) batch files.", "Get all tuples from the node below the Hash node and insert into the hash table (or temp files).", "Account for the buckets in spaceUsed (reported in EXPLAIN ANALYZE)"]}, {"code": "case T_Hash:\n\t\t\tpname = sname = \"Hash\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Builds the hash table consumed by a hash join."], "evidence_kind": "source and documentation", "explain_names": ["Hash"], "partial_modes": [], "comparison_data": {"node_tag": "T_Hash", "strategies": [], "text_names": ["Hash"], "initializer": "ExecInitHash", "partial_modes": [], "memory_mechanism": "hash-batches", "parallel_callbacks": ["ExecHashEstimate", "ExecHashInitializeDSM", "ExecHashInitializeWorker", "ExecHashRetrieveInstrumentation"]}, "comparison_hash": "2d9def2bb1ea7366b3f68169fa01faf4e00149c2373e67133c3ac0d69137032f", "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/nodeHash.c"}, "parallel_callbacks": ["ExecHashEstimate", "ExecHashInitializeDSM", "ExecHashInitializeWorker", "ExecHashRetrieveInstrumentation"]}, "19": {"facts": [{"label": "Core node tag", "value": "T_Hash"}, {"label": "Structured EXPLAIN Node Type", "value": "Hash"}, {"label": "Inputs", "value": "The hash join inner child plan"}, {"label": "Output", "value": "Hash table, not a normal tuple stream"}, {"label": "Executor initializer", "value": "ExecInitHash"}, {"label": "Memory mechanism", "value": "hash-batches"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "path": "src/backend/executor/nodeHash.c", "label": "src/backend/executor/nodeHash.c", "sha256": "80d8ce8589d4d38464d8c7752e5b9a90e02735bdeba9497e7502a423f34ba6dc", "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": ["It's time to begin hashing, or if we just arrived here then hashing is already underway, so join in that effort. While hashing we have to be prepared to help increase the number of batches or buckets at any time, and if we arrived here when that was already underway we'll have to help complete that work immediately so that it's safe to access batches and buckets below.", "Make sure that any tuples we wrote to disk are visible to others before anyone tries to load them.", "Elect one backend to disable any further growth. Batches are now fixed. While building them we made sure they'd fit in our memory budget when we load them back in later (or we tried to do that and gave up because we detected extreme skew).", "Parallel Hash tries to use the combined hash_mem of all workers to avoid the need to batch. If that won't work, it falls back to hash_mem per worker and tries to process batches in parallel."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Hash", "identity": "Hash"}], "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"}, {"url": "/wiki/guc/hash_mem_multiplier/?v=19", "label": "hash_mem_multiplier"}], "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": 1616, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1616", "sha256": "8b115b1c194a4b54ae630209a741e293b1df49a9052f10b2de9ca092a48998e3", "archive_sha256": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86"}, {"url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "line": 365, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:365", "sha256": "5e39b2037bed672da55104229ecc32da5abde44c26bcad01479edcfa044d09ed", "archive_sha256": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86"}, {"url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "path": "src/backend/executor/nodeHash.c", "label": "src/backend/executor/nodeHash.c", "sha256": "80d8ce8589d4d38464d8c7752e5b9a90e02735bdeba9497e7502a423f34ba6dc", "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": "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": "/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_Hash", "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.", "It's time to begin hashing, or if we just arrived here then hashing is already underway, so join in that effort. While hashing we have to be prepared to help increase the number of batches or buckets at any time, and if we arrived here when that was already underway we'll have to help complete that work immediately so that it's safe to access batches and buckets below.", "Make sure that any tuples we wrote to disk are visible to others before anyone tries to load them.", "Elect one backend to disable any further growth. Batches are now fixed. While building them we made sure they'd fit in our memory budget when we load them back in later (or we tried to do that and gave up because we detected extreme skew).", "Parallel Hash tries to use the combined hash_mem of all workers to avoid the need to batch. If that won't work, it falls back to hash_mem per worker and tries to process batches in parallel."]}, {"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: ExecHashEstimate, ExecHashInitializeDSM, ExecHashInitializeWorker, ExecHashRetrieveInstrumentation."]}, {"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": ["build hash table for hashjoin, doing partitioning if more than one batch is required.", "We do not return the hash table directly because it's not a subtype of Node, and so would violate the MultiExecProcNode API. Instead, our parent Hashjoin node is expected to know how to fish it out of our node state. Ugly but not really worth cleaning up, since Hashjoin knows quite a bit more about Hash besides that.", "parallel-oblivious version, building a backend-private hash table and (if necessary) batch files.", "Get all tuples from the node below the Hash node and insert the potentially-matchable ones into the hash table (or temp files). Tuples that can't possibly match because they have null join keys are dumped into a separate tuplestore, or just summarily discarded if we don't need to emit them with null-extension.", "Account for the buckets in spaceUsed (reported in EXPLAIN ANALYZE)"]}, {"code": "case T_Hash:\n\t\t\tpname = sname = \"Hash\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Builds the hash table consumed by a hash join."], "evidence_kind": "source and documentation", "explain_names": ["Hash"], "partial_modes": [], "comparison_data": {"node_tag": "T_Hash", "strategies": [], "text_names": ["Hash"], "initializer": "ExecInitHash", "partial_modes": [], "memory_mechanism": "hash-batches", "parallel_callbacks": ["ExecHashEstimate", "ExecHashInitializeDSM", "ExecHashInitializeWorker", "ExecHashRetrieveInstrumentation"]}, "comparison_hash": "2d9def2bb1ea7366b3f68169fa01faf4e00149c2373e67133c3ac0d69137032f", "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/nodeHash.c"}, "parallel_callbacks": ["ExecHashEstimate", "ExecHashInitializeDSM", "ExecHashInitializeWorker", "ExecHashRetrieveInstrumentation"]}, "20": {"facts": [{"label": "Core node tag", "value": "T_Hash"}, {"label": "Structured EXPLAIN Node Type", "value": "Hash"}, {"label": "Inputs", "value": "The hash join inner child plan"}, {"label": "Output", "value": "Hash table, not a normal tuple stream"}, {"label": "Executor initializer", "value": "ExecInitHash"}, {"label": "Memory mechanism", "value": "hash-batches"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "path": "src/backend/executor/nodeHash.c", "label": "src/backend/executor/nodeHash.c", "sha256": "80d8ce8589d4d38464d8c7752e5b9a90e02735bdeba9497e7502a423f34ba6dc", "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": ["It's time to begin hashing, or if we just arrived here then hashing is already underway, so join in that effort. While hashing we have to be prepared to help increase the number of batches or buckets at any time, and if we arrived here when that was already underway we'll have to help complete that work immediately so that it's safe to access batches and buckets below.", "Make sure that any tuples we wrote to disk are visible to others before anyone tries to load them.", "Elect one backend to disable any further growth. Batches are now fixed. While building them we made sure they'd fit in our memory budget when we load them back in later (or we tried to do that and gave up because we detected extreme skew).", "Parallel Hash tries to use the combined hash_mem of all workers to avoid the need to batch. If that won't work, it falls back to hash_mem per worker and tries to process batches in parallel."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Hash", "identity": "Hash"}], "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"}, {"url": "/wiki/guc/hash_mem_multiplier/?v=20", "label": "hash_mem_multiplier"}], "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": 1616, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1616", "sha256": "13402758013520451539427b5993db06d463ca11c4e2d4cc5444e82367688077", "archive_sha256": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41"}, {"url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "line": 365, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:365", "sha256": "5e39b2037bed672da55104229ecc32da5abde44c26bcad01479edcfa044d09ed", "archive_sha256": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41"}, {"url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "path": "src/backend/executor/nodeHash.c", "label": "src/backend/executor/nodeHash.c", "sha256": "80d8ce8589d4d38464d8c7752e5b9a90e02735bdeba9497e7502a423f34ba6dc", "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": "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": "/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_Hash", "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.", "It's time to begin hashing, or if we just arrived here then hashing is already underway, so join in that effort. While hashing we have to be prepared to help increase the number of batches or buckets at any time, and if we arrived here when that was already underway we'll have to help complete that work immediately so that it's safe to access batches and buckets below.", "Make sure that any tuples we wrote to disk are visible to others before anyone tries to load them.", "Elect one backend to disable any further growth. Batches are now fixed. While building them we made sure they'd fit in our memory budget when we load them back in later (or we tried to do that and gave up because we detected extreme skew).", "Parallel Hash tries to use the combined hash_mem of all workers to avoid the need to batch. If that won't work, it falls back to hash_mem per worker and tries to process batches in parallel."]}, {"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: ExecHashEstimate, ExecHashInitializeDSM, ExecHashInitializeWorker, ExecHashRetrieveInstrumentation."]}, {"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": ["build hash table for hashjoin, doing partitioning if more than one batch is required.", "We do not return the hash table directly because it's not a subtype of Node, and so would violate the MultiExecProcNode API. Instead, our parent Hashjoin node is expected to know how to fish it out of our node state. Ugly but not really worth cleaning up, since Hashjoin knows quite a bit more about Hash besides that.", "parallel-oblivious version, building a backend-private hash table and (if necessary) batch files.", "Get all tuples from the node below the Hash node and insert the potentially-matchable ones into the hash table (or temp files). Tuples that can't possibly match because they have null join keys are dumped into a separate tuplestore, or just summarily discarded if we don't need to emit them with null-extension.", "Account for the buckets in spaceUsed (reported in EXPLAIN ANALYZE)"]}, {"code": "case T_Hash:\n\t\t\tpname = sname = \"Hash\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Builds the hash table consumed by a hash join."], "evidence_kind": "source and documentation", "explain_names": ["Hash"], "partial_modes": [], "comparison_data": {"node_tag": "T_Hash", "strategies": [], "text_names": ["Hash"], "initializer": "ExecInitHash", "partial_modes": [], "memory_mechanism": "hash-batches", "parallel_callbacks": ["ExecHashEstimate", "ExecHashInitializeDSM", "ExecHashInitializeWorker", "ExecHashRetrieveInstrumentation"]}, "comparison_hash": "2d9def2bb1ea7366b3f68169fa01faf4e00149c2373e67133c3ac0d69137032f", "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/nodeHash.c"}, "parallel_callbacks": ["ExecHashEstimate", "ExecHashInitializeDSM", "ExecHashInitializeWorker", "ExecHashRetrieveInstrumentation"]}}}, "snapshot": {"facts": [{"label": "Core node tag", "value": "T_Hash"}, {"label": "Structured EXPLAIN Node Type", "value": "Hash"}, {"label": "Inputs", "value": "The hash join inner child plan"}, {"label": "Output", "value": "Hash table, not a normal tuple stream"}, {"label": "Executor initializer", "value": "ExecInitHash"}, {"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/nodeHash.c", "label": "src/backend/executor/nodeHash.c", "sha256": "f83e9b10129450f2dc52ac7ea5147b7c51c12648ae1b3b3195bb3f0b4895fafc", "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": ["It's time to begin hashing, or if we just arrived here then hashing is already underway, so join in that effort. While hashing we have to be prepared to help increase the number of batches or buckets at any time, and if we arrived here when that was already underway we'll have to help complete that work immediately so that it's safe to access batches and buckets below.", "Make sure that any tuples we wrote to disk are visible to others before anyone tries to load them.", "Elect one backend to disable any further growth. Batches are now fixed. While building them we made sure they'd fit in our memory budget when we load them back in later (or we tried to do that and gave up because we detected extreme skew).", "Parallel Hash tries to use the combined hash_mem of all workers to avoid the need to batch. If that won't work, it falls back to hash_mem per worker and tries to process batches in parallel."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Hash", "identity": "Hash"}], "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"}, {"url": "/wiki/guc/hash_mem_multiplier/?v=18", "label": "hash_mem_multiplier"}], "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": 1604, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1604", "sha256": "34c86d6070224a0e981efef51f79101d6d505e5874f1684ace183034bab14bb4", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "line": 365, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:365", "sha256": "f8a06a3f539077249b20664b2812433db6d7bd12b2c0ca633525db43d06f112a", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/backend/executor/nodeHash.c", "label": "src/backend/executor/nodeHash.c", "sha256": "f83e9b10129450f2dc52ac7ea5147b7c51c12648ae1b3b3195bb3f0b4895fafc", "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": "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": "/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_Hash", "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.", "It's time to begin hashing, or if we just arrived here then hashing is already underway, so join in that effort. While hashing we have to be prepared to help increase the number of batches or buckets at any time, and if we arrived here when that was already underway we'll have to help complete that work immediately so that it's safe to access batches and buckets below.", "Make sure that any tuples we wrote to disk are visible to others before anyone tries to load them.", "Elect one backend to disable any further growth. Batches are now fixed. While building them we made sure they'd fit in our memory budget when we load them back in later (or we tried to do that and gave up because we detected extreme skew).", "Parallel Hash tries to use the combined hash_mem of all workers to avoid the need to batch. If that won't work, it falls back to hash_mem per worker and tries to process batches in parallel."]}, {"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: ExecHashEstimate, ExecHashInitializeDSM, ExecHashInitializeWorker, ExecHashRetrieveInstrumentation."]}, {"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": ["build hash table for hashjoin, doing partitioning if more than one batch is required.", "We do not return the hash table directly because it's not a subtype of Node, and so would violate the MultiExecProcNode API. Instead, our parent Hashjoin node is expected to know how to fish it out of our node state. Ugly but not really worth cleaning up, since Hashjoin knows quite a bit more about Hash besides that.", "parallel-oblivious version, building a backend-private hash table and (if necessary) batch files.", "Get all tuples from the node below the Hash node and insert into the hash table (or temp files).", "Account for the buckets in spaceUsed (reported in EXPLAIN ANALYZE)"]}, {"code": "case T_Hash:\n\t\t\tpname = sname = \"Hash\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Builds the hash table consumed by a hash join."], "evidence_kind": "source and documentation", "explain_names": ["Hash"], "partial_modes": [], "comparison_data": {"node_tag": "T_Hash", "strategies": [], "text_names": ["Hash"], "initializer": "ExecInitHash", "partial_modes": [], "memory_mechanism": "hash-batches", "parallel_callbacks": ["ExecHashEstimate", "ExecHashInitializeDSM", "ExecHashInitializeWorker", "ExecHashRetrieveInstrumentation"]}, "comparison_hash": "2d9def2bb1ea7366b3f68169fa01faf4e00149c2373e67133c3ac0d69137032f", "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/nodeHash.c"}, "parallel_callbacks": ["ExecHashEstimate", "ExecHashInitializeDSM", "ExecHashInitializeWorker", "ExecHashRetrieveInstrumentation"]}, "comparison": {"left": "17", "right": "18", "status": "unchanged", "diff": ""}}