{"Entry":{"collection":"plan","key":"hash-join","name":"Hash Join","aliases":["Hash","HashJoin","T_HashJoin"],"metadata":{"aliases":["Hash","HashJoin","T_HashJoin"],"category":"Join","content_hash":"7e608f95eb480ff225df08f08d773ba6dc855f0565b1bb5f6f3c457668a23b51","derived_content_hash":"da3039db0d2e9ac954ce42374da5b0a941d71cb07d617a6fac01362ab0c04113","imported_at":"2026-09-30T00:40:44.128065+08:00","name":"Hash Join","name_zh":"HashJoin","slug":"hash-join","summary":"Probes a hash table built from its inner input while reading its outer input."}},"Definition":{"Collection":"plan","Key":"hash-join","SourceDatabase":"center","Version":"18","SourceTable":"plan_node","SourceKey":"hash-join","SourceRevision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","Facts":{"comparison_data":{"initializer":"ExecInitHashJoin","memory_mechanism":"hash-batches","node_tag":"T_HashJoin","parallel_callbacks":["ExecHashJoinEstimate","ExecHashJoinInitializeDSM","ExecHashJoinInitializeWorker","ExecHashJoinReInitializeDSM"],"partial_modes":[],"strategies":[],"text_names":["Hash"]},"comparison_hash":"f721dd250dc8d1d06a3d3dc814d77a301b3c9b8eb383affa6abbdd7a95c634df","description":["Probes a hash table built from its inner input while reading its outer input."],"description_zh":"读取外侧输入，并探测由内侧输入构建的哈希表。","description_zh_html":"\u003cp\u003e读取外侧输入，并探测由内侧输入构建的哈希表。\u003c/p\u003e\u003cp\u003e核心节点标识：\u003ccode\u003eT_HashJoin\u003c/code\u003e；EXPLAIN 名称：\u003ccode\u003eHash\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e本版本源码中的并行执行或观测回调：\u003ccode\u003eExecHashJoinEstimate, ExecHashJoinInitializeDSM, ExecHashJoinInitializeWorker, ExecHashJoinReInitializeDSM\u003c/code\u003e。回调的存在不表示该节点必然支持共享并行扫描；在并行工作进程中执行也不等于具有并行感知能力。\u003c/p\u003e\u003ch3\u003e同版本中文手册节选\u003c/h3\u003e\u003cp\u003e\n    查询计划的结构是一棵由\u003cem class=\"firstterm\"\u003e计划节点\u003c/em\u003e组成的树。树的最底层是扫描节点，它们从表中返回原始行。不同的表访问方法对应不同类型的扫描节点，例如顺序扫描、索引扫描和位图索引扫描。也有一些并非表的行来源，例如 \u003ccode class=\"literal\"\u003eVALUES\u003c/code\u003e 子句和 \u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e 中的返回集合函数，它们也各自有对应的扫描节点类型。如果查询需要对原始行执行连接、聚合、排序或其他操作，那么扫描节点之上还会出现额外的节点来完成这些操作。同样，这些操作通常不止一种实现方式，因此这里也会出现不同的节点类型。\u003ccode class=\"command\"\u003eEXPLAIN\u003c/code\u003e 会为计划树中的每个节点输出一行，显示基本节点类型以及规划器对该计划节点执行代价的估计值。还可能出现相对节点摘要行缩进的附加行，用来显示该节点的更多属性。第一行，也就是最顶层节点的摘要行，给出了整个计划的估计总执行代价；规划器力图最小化的正是这个数字。\n   \u003c/p\u003e\u003cp\u003e\n    下面是一个简单的示例，仅用于展示输出的样子：\n\n\u003c/p\u003e\u003cp\u003e\n    由于这个查询没有 \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e 子句，它必须扫描表中的所有行，因此规划器选择了一个简单的顺序扫描计划。圆括号中的数字从左到右依次表示：\n\n    \u003c/p\u003e\u003cp\u003e\n    这些代价使用由规划器代价参数决定的任意单位来衡量（见\u003ca class=\"xref\" href=\"/zh/docs/18/runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS\"\u003e第 19.7.2 节\u003c/a\u003e）。传统上通常以磁盘页读取作为代价单位；也就是说，惯例上将 \u003ca class=\"xref\" href=\"/zh/docs/18/runtime-config-query.html#GUC-SEQ-PAGE-COST\"\u003eseq_page_cost\u003c/a\u003e 设为 \u003ccode class=\"literal\"\u003e1.0\u003c/code\u003e，其他代价参数都相对它来设定。本节中的示例都使用默认代价参数。\n   \u003c/p\u003e\u003cp\u003e\u003ca href=\"/zh/docs/18/using-explain.html#USING-EXPLAIN-BASICS\"\u003e查看完整中文手册与上下文\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e本页开头的中文说明为 PG.CENTER 对编辑说明的翻译；手册节选保留对应版本中文文档原文。源码信息及手册示例不代表重新进行过运行测试。\u003c/p\u003e","evidence_kind":"source and documentation","explain_names":["Hash"],"explain_prefixes":["Parallel","Async"],"facts":[{"label":"Core node tag","value":"T_HashJoin"},{"label":"Structured EXPLAIN Node Type","value":"Hash Join"},{"label":"Inputs","value":"Outer child and inner Hash plan"},{"label":"Output","value":"Joined tuples according to the selected join type"},{"label":"Executor initializer","value":"ExecInitHashJoin"},{"label":"Memory mechanism","value":"hash-batches"}],"facts_zh":[{"label":"核心节点标签","value":"T_HashJoin"},{"label":"结构化 EXPLAIN 节点类型","value":"Hash Join"},{"label":"输入","value":"外侧子计划与内侧 Hash 计划"},{"label":"输出","value":"按所选连接类型生成的连接元组"},{"label":"执行器初始化函数","value":"ExecInitHashJoin"},{"label":"内存机制","value":"hash-batches"}],"memory":{"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.","evidence":[{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/backend/executor/nodeHashjoin.c","path":"src/backend/executor/nodeHashjoin.c","sha256":"4a72b0db253c1905c415462cfe56a2327ac65507c607b6d699d2717fa624ad56","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/backend/executor/nodeHashjoin.c","path":"src/backend/executor/nodeHashjoin.c","sha256":"4a72b0db253c1905c415462cfe56a2327ac65507c607b6d699d2717fa624ad56","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"}],"mechanism":"hash-batches","source_notes":["If the inner side tuples of a hash join do not fit in memory, the hash join can be executed in multiple batches.","If the statistics on the inner side relation are accurate, planner chooses a multi-batch strategy and estimates the number of batches.","The query executor measures the real size of the hashtable and increases the number of batches if the hashtable grows too large.","The number of batches is always a power of two, so an increase in the number of batches doubles it."]},"node_tag":"T_HashJoin","parallel_callbacks":["ExecHashJoinEstimate","ExecHashJoinInitializeDSM","ExecHashJoinInitializeWorker","ExecHashJoinReInitializeDSM"],"partial_modes":[],"related":[{"label":"EXPLAIN","url":"/wiki/sql/explain/?v=18"},{"label":"Using EXPLAIN","url":"/docs/18/using-explain.html"},{"label":"Parallel plans","url":"/docs/18/parallel-plans.html"},{"label":"enable_hashjoin","url":"/wiki/guc/enable_hashjoin/?v=18"},{"label":"work_mem","url":"/wiki/guc/work_mem/?v=18"}],"release":{"channel":"stable","label":"18.6","major":"18","ref":"PostgreSQL 18.6 source archive","revision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","source_snapshot_utc":"","source_url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},"runtime_verified":false,"sections":[{"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":"EXPLAIN names and attributes"},{"paragraphs":["Hash-join execution can partition work into batches backed by temporary files. This describes hash-join batching, not the spill policy of aggregate or Memoize nodes.","If the inner side tuples of a hash join do not fit in memory, the hash join can be executed in multiple batches.","If the statistics on the inner side relation are accurate, planner chooses a multi-batch strategy and estimates the number of batches.","The query executor measures the real size of the hashtable and increases the number of batches if the hashtable grows too large.","The number of batches is always a power of two, so an increase in the number of batches doubles it."],"title":"Memory and temporary storage"},{"paragraphs":["The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.","Callbacks in this build: ExecHashJoinEstimate, ExecHashJoinInitializeDSM, ExecHashJoinInitializeWorker, ExecHashJoinReInitializeDSM."],"title":"Parallel execution and instrumentation"},{"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":"Same-version manual discussion"},{"blocks":[{"code":"EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 \u003c 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   -\u003e  Seq Scan on tenk2 t2  (cost=0.00..445.00 rows=10000 width=244)\n   -\u003e  Hash  (cost=224.98..224.98 rows=100 width=244)\n         -\u003e  Bitmap Heap Scan on tenk1 t1  (cost=5.06..224.98 rows=100 width=244)\n               Recheck Cond: (unique1 \u003c 100)\n               -\u003e  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)\n                     Index Cond: (unique1 \u003c 100)","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:"],"source":{"label":"PostgreSQL 18.6 · using-explain","path":"using-explain.html","sha256":"60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed","url":"/docs/18/using-explain.html#USING-EXPLAIN-BASICS"}},{"code":"SET enable_mergejoin = off;\n\nEXPLAIN SELECT *\nFROM tenk1 t1, onek t2\nWHERE t1.unique1 \u003c 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   -\u003e  Seq Scan on onek t2  (cost=0.00..114.00 rows=1000 width=244)\n   -\u003e  Hash  (cost=224.98..224.98 rows=100 width=244)\n         -\u003e  Bitmap Heap Scan on tenk1 t1  (cost=5.06..224.98 rows=100 width=244)\n               Recheck Cond: (unique1 \u003c 100)\n               -\u003e  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)\n                     Index Cond: (unique1 \u003c 100)","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"],"source":{"label":"PostgreSQL 18.6 · using-explain","path":"using-explain.html","sha256":"60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed","url":"/docs/18/using-explain.html#USING-EXPLAIN-BASICS"}}],"title":"Examples from this manual build"},{"paragraphs":["This is based on the \"hybrid hash join\" algorithm described shortly in the following page","\"An Adaptive Hash Join Algorithm for Multiuser Environments\" Hansjörg Zeller; Jim Gray (1990). Proceedings of the 16th VLDB conference. Brisbane: 186–197.","If the inner side tuples of a hash join do not fit in memory, the hash join can be executed in multiple batches.","If the statistics on the inner side relation are accurate, planner chooses a multi-batch strategy and estimates the number of batches.","The query executor measures the real size of the hashtable and increases the number of batches if the hashtable grows too large."],"title":"Executor implementation notes"},{"code":"case T_HashJoin:\n\t\t\tpname = \"Hash\";\t\t/* \"Join\" gets added by jointype switch */\n\t\t\tsname = \"Hash Join\";\n\t\t\tbreak;","title":"EXPLAIN identity in core source"}],"sections_zh":[{"paragraphs":["结构化格式使用上述 Node Type。文本格式名称还可能包含操作、策略、连接类型、扫描方向或聚合阶段属性。","此源码记录的文本名称：Hash.","并行感知与并行安全是不同的计划属性。在并行工作进程内运行的节点不一定是并行感知节点。"],"title":"EXPLAIN 名称与属性"},{"paragraphs":["哈希连接可将工作划分为由临时文件承载的批次。这描述的是哈希连接的分批处理，不是聚合或 Memoize 节点的落盘策略。","哈希连接内侧的元组无法全部放入内存时，可以分多个批次执行哈希连接。","如果内侧关系的统计信息准确，规划器会选择多批次策略并估计批次数量。","查询执行器测量哈希表的实际大小，并在哈希表过大时增加批次数。","批次数始终是 2 的幂，因此每次增加都会翻倍。"],"title":"内存与临时存储"},{"paragraphs":["以下源码回调可以协调执行或收集工作进程的测量数据。回调存在不代表该节点普遍支持共享并行扫描或共享状态。","此构建的回调：ExecHashJoinEstimate, ExecHashJoinInitializeDSM, ExecHashJoinInitializeWorker, ExecHashJoinReInitializeDSM."],"title":"并行执行与运行信息采集"},{"paragraphs":["这里，规划器选择了哈希连接：先将一个表的行放入内存中的哈希表，再扫描另一个表，并为其中的每一行查找哈希表中的匹配项。注意缩进如何反映计划结构：tenk1 上的位图扫描是 Hash 节点的输入，Hash 节点据此构建哈希表，再将其交给 Hash Join 节点。后者从外侧子计划读取行，并为每一行搜索哈希表。","这表明，规划器认为在这个场景下，哈希连接的代价几乎比归并连接高出 50%。当然，接下来的问题就是它是否判断正确。我们可以像 下文 所述，使用 EXPLAIN ANALYZE 来研究。","这里，子计划只执行一次，其输出被装入内存中的 hash 表，随后由外层的 ANY 操作符来探测这个 hash 表。这要求子 SELECT 不能引用外层查询的任何变量，并且 ANY 比较所用的操作符必须适合进行 hash 运算。","在某些情况下，EXPLAIN ANALYZE 除了计划节点的执行时间和行数之外，还会显示额外的执行统计信息。例如，Sort 和 Hash 节点会提供更多信息：","Sort 节点显示排序方法（尤其是内存排序还是磁盘排序）及所需内存或磁盘空间。Hash 节点显示哈希桶数、批次数及哈希表内存使用峰值。（批次数超过一时也会使用磁盘空间，但此处不显示。）","与非并行计划一样，驱动表可以通过嵌套循环、哈希连接或归并连接与一个或多个其他表连接。连接的内侧可以是规划器支持的任何类型的非并行计划，只要它能够安全地在并行工作进程中运行。根据连接类型，内侧也可以是并行计划。"],"title":"同版本手册说明"},{"blocks":[{"code":"EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 \u003c 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   -\u003e  Seq Scan on tenk2 t2  (cost=0.00..445.00 rows=10000 width=244)\n   -\u003e  Hash  (cost=224.98..224.98 rows=100 width=244)\n         -\u003e  Bitmap Heap Scan on tenk1 t1  (cost=5.06..224.98 rows=100 width=244)\n               Recheck Cond: (unique1 \u003c 100)\n               -\u003e  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)\n                     Index Cond: (unique1 \u003c 100)","paragraphs":["示例摘自 PostgreSQL 18.6 手册；本百科未实际执行此示例。","稍微改变查询的选择率，就可能得到截然不同的连接计划："],"source":{"label":"PostgreSQL 18.6 · using-explain","path":"using-explain.html","sha256":"60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed","url":"/docs/18/using-explain.html#USING-EXPLAIN-BASICS"}},{"code":"SET enable_mergejoin = off;\n\nEXPLAIN SELECT *\nFROM tenk1 t1, onek t2\nWHERE t1.unique1 \u003c 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   -\u003e  Seq Scan on onek t2  (cost=0.00..114.00 rows=1000 width=244)\n   -\u003e  Hash  (cost=224.98..224.98 rows=100 width=244)\n         -\u003e  Bitmap Heap Scan on tenk1 t1  (cost=5.06..224.98 rows=100 width=244)\n               Recheck Cond: (unique1 \u003c 100)\n               -\u003e  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)\n                     Index Cond: (unique1 \u003c 100)","paragraphs":["示例摘自 PostgreSQL 18.6 手册；本百科未实际执行此示例。","观察备选计划的一种方法，是利用 第 19.7.1 节 中描述的启用/禁用标志，强迫规划器忽略它认为最便宜的策略。（这是个粗糙但有用的工具。另见 第 14.3 节 。）例如，如果我们并不确信前一个示例中归并连接真的是最佳连接类型，可以试试："],"source":{"label":"PostgreSQL 18.6 · using-explain","path":"using-explain.html","sha256":"60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed","url":"/docs/18/using-explain.html#USING-EXPLAIN-BASICS"}}],"title":"本版手册中的示例"},{"paragraphs":["此实现基于下页简要介绍的“混合哈希连接”算法。","\"An Adaptive Hash Join Algorithm for Multiuser Environments\" Hansjörg Zeller; Jim Gray (1990). Proceedings of the 16th VLDB conference. Brisbane: 186–197.","哈希连接内侧的元组无法全部放入内存时，可以分多个批次执行哈希连接。","如果内侧关系的统计信息准确，规划器会选择多批次策略并估计批次数量。","查询执行器测量哈希表的实际大小，并在哈希表过大时增加批次数。"],"title":"执行器实现说明"},{"code":"case T_HashJoin:\n\t\t\tpname = \"Hash\";\t\t/* \"Join\" gets added by jointype switch */\n\t\t\tsname = \"Hash Join\";\n\t\t\tbreak;","title":"核心源码中的 EXPLAIN 标识"}],"source_inventory":{"executor":"src/backend/executor/execProcnode.c","explain":"src/backend/commands/explain.c","implementation":"src/backend/executor/nodeHashjoin.c"},"sources":[{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/backend/commands/explain.c:1428","line":1428,"path":"src/backend/commands/explain.c","sha256":"34c86d6070224a0e981efef51f79101d6d505e5874f1684ace183034bab14bb4","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/backend/executor/execProcnode.c:307","line":307,"path":"src/backend/executor/execProcnode.c","sha256":"f8a06a3f539077249b20664b2812433db6d7bd12b2c0ca633525db43d06f112a","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/backend/executor/nodeHashjoin.c","path":"src/backend/executor/nodeHashjoin.c","sha256":"4a72b0db253c1905c415462cfe56a2327ac65507c607b6d699d2717fa624ad56","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/include/nodes/plannodes.h","path":"src/include/nodes/plannodes.h","sha256":"52422b327a8049fbbb20d8b96008a0fc0a6fafa60f7eff3c695d5b2e83830120","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"label":"PostgreSQL 18.6 · using-explain","path":"using-explain.html","sha256":"60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed","url":"/docs/18/using-explain.html#USING-EXPLAIN-BASICS"},{"label":"PostgreSQL 18.6 · using-explain","path":"using-explain.html","sha256":"60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed","url":"/docs/18/using-explain.html#USING-EXPLAIN-ANALYZE"},{"label":"PostgreSQL 18.6 · parallel-plans","path":"parallel-plans.html","sha256":"62207d207bead82b01b59dc119c4f95856f08655cc11d4699a05a40867ed2070","url":"/docs/18/parallel-plans.html#PARALLEL-JOINS"}],"strategies":[],"tables":[{"columns":[{"key":"label","label":"Text-format label"},{"key":"identity","label":"Structured node identity"}],"key":"explain-labels","rows":[{"identity":"Hash Join","label":"Hash"}],"title":"EXPLAIN labels in this source build"}],"tables_zh":[{"columns":[{"key":"label","label":"文本格式标签"},{"key":"identity","label":"结构化节点标识"}],"key":"explain-labels","rows":[{"identity":"Hash Join","label":"Hash"}],"title":"本构建中的 EXPLAIN 标签"}],"zh_from":{"authored_translation":"authored-translation-of-reviewed-guidance","body_sha256":"c693480e225a8b7e77b76574216bc3508e3d54dc057e9255392a76f4cb3619b7","kind":"same-version-chinese-manual-with-authored-translation-of-reviewed-guidance","manual":{"anchor":"USING-EXPLAIN-BASICS","build_id":107,"extraction":"concept-introduction","fragment_sha256":"bc79b6f9c70f2b56968e08ea26d2c9ec3d395e425aca0594e227203daa0ad29b","locale":"zh-Hans","page_key":"using-explain.html","source_hash":"d2f749744b3ac5aa2eb0154f295e8163903cd20fcc130c74c6172cdf2ae3a9df","source_revision":"unknown:rendered-translation-baseline","translated_from_revision":null,"translation_revision":"git:b1b500221dbe5437cfca004d6b04d3f6e422765d;html-sha256:08e987a66af27b98444a8057c256314eafa13611a3fb9e888214fc436824244b","url":"/zh/docs/18/using-explain.html#USING-EXPLAIN-BASICS","version":"18"},"runtime_rerun":false,"source_facts_revision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}},"ManualEvidence":{"release":{"channel":"stable","label":"18.6","major":"18","ref":"PostgreSQL 18.6 source archive","revision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","source_snapshot_utc":"","source_url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},"sources":[{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/backend/commands/explain.c:1428","line":1428,"path":"src/backend/commands/explain.c","sha256":"34c86d6070224a0e981efef51f79101d6d505e5874f1684ace183034bab14bb4","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/backend/executor/execProcnode.c:307","line":307,"path":"src/backend/executor/execProcnode.c","sha256":"f8a06a3f539077249b20664b2812433db6d7bd12b2c0ca633525db43d06f112a","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/backend/executor/nodeHashjoin.c","path":"src/backend/executor/nodeHashjoin.c","sha256":"4a72b0db253c1905c415462cfe56a2327ac65507c607b6d699d2717fa624ad56","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/include/nodes/plannodes.h","path":"src/include/nodes/plannodes.h","sha256":"52422b327a8049fbbb20d8b96008a0fc0a6fafa60f7eff3c695d5b2e83830120","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"label":"PostgreSQL 18.6 · using-explain","path":"using-explain.html","sha256":"60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed","url":"/docs/18/using-explain.html#USING-EXPLAIN-BASICS"},{"label":"PostgreSQL 18.6 · using-explain","path":"using-explain.html","sha256":"60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed","url":"/docs/18/using-explain.html#USING-EXPLAIN-ANALYZE"},{"label":"PostgreSQL 18.6 · parallel-plans","path":"parallel-plans.html","sha256":"62207d207bead82b01b59dc119c4f95856f08655cc11d4699a05a40867ed2070","url":"/docs/18/parallel-plans.html#PARALLEL-JOINS"}]},"MeasuredEvidence":{"runtime_verified":false}},"Text":{"Collection":"plan","Key":"hash-join","SourceDatabase":"center","Version":"18","Locale":"en","Title":"Hash Join","Summary":"Probes a hash table built from its inner input while reading its outer input.","BodyHTML":"\u003cp\u003eProbes a hash table built from its inner input while reading its outer input.\u003c/p\u003e","SourceRevision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","ContentHash":"a0d88750d2b599e5d680eaf7cdfb29d9b9a24a5394e53d63d51b9b83b391e1a4","Payload":{"description":["Probes a hash table built from its inner input while reading its outer input."],"related":[{"label":"EXPLAIN","url":"/wiki/sql/explain/?v=18"},{"label":"Using EXPLAIN","url":"/docs/18/using-explain.html"},{"label":"Parallel plans","url":"/docs/18/parallel-plans.html"},{"label":"enable_hashjoin","url":"/wiki/guc/enable_hashjoin/?v=18"},{"label":"work_mem","url":"/wiki/guc/work_mem/?v=18"}],"sections":[{"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":"EXPLAIN names and attributes"},{"paragraphs":["Hash-join execution can partition work into batches backed by temporary files. This describes hash-join batching, not the spill policy of aggregate or Memoize nodes.","If the inner side tuples of a hash join do not fit in memory, the hash join can be executed in multiple batches.","If the statistics on the inner side relation are accurate, planner chooses a multi-batch strategy and estimates the number of batches.","The query executor measures the real size of the hashtable and increases the number of batches if the hashtable grows too large.","The number of batches is always a power of two, so an increase in the number of batches doubles it."],"title":"Memory and temporary storage"},{"paragraphs":["The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.","Callbacks in this build: ExecHashJoinEstimate, ExecHashJoinInitializeDSM, ExecHashJoinInitializeWorker, ExecHashJoinReInitializeDSM."],"title":"Parallel execution and instrumentation"},{"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":"Same-version manual discussion"},{"blocks":[{"code":"EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 \u003c 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   -\u003e  Seq Scan on tenk2 t2  (cost=0.00..445.00 rows=10000 width=244)\n   -\u003e  Hash  (cost=224.98..224.98 rows=100 width=244)\n         -\u003e  Bitmap Heap Scan on tenk1 t1  (cost=5.06..224.98 rows=100 width=244)\n               Recheck Cond: (unique1 \u003c 100)\n               -\u003e  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)\n                     Index Cond: (unique1 \u003c 100)","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:"],"source":{"label":"PostgreSQL 18.6 · using-explain","path":"using-explain.html","sha256":"60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed","url":"/docs/18/using-explain.html#USING-EXPLAIN-BASICS"}},{"code":"SET enable_mergejoin = off;\n\nEXPLAIN SELECT *\nFROM tenk1 t1, onek t2\nWHERE t1.unique1 \u003c 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   -\u003e  Seq Scan on onek t2  (cost=0.00..114.00 rows=1000 width=244)\n   -\u003e  Hash  (cost=224.98..224.98 rows=100 width=244)\n         -\u003e  Bitmap Heap Scan on tenk1 t1  (cost=5.06..224.98 rows=100 width=244)\n               Recheck Cond: (unique1 \u003c 100)\n               -\u003e  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)\n                     Index Cond: (unique1 \u003c 100)","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"],"source":{"label":"PostgreSQL 18.6 · using-explain","path":"using-explain.html","sha256":"60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed","url":"/docs/18/using-explain.html#USING-EXPLAIN-BASICS"}}],"title":"Examples from this manual build"},{"paragraphs":["This is based on the \"hybrid hash join\" algorithm described shortly in the following page","\"An Adaptive Hash Join Algorithm for Multiuser Environments\" Hansjörg Zeller; Jim Gray (1990). Proceedings of the 16th VLDB conference. Brisbane: 186–197.","If the inner side tuples of a hash join do not fit in memory, the hash join can be executed in multiple batches.","If the statistics on the inner side relation are accurate, planner chooses a multi-batch strategy and estimates the number of batches.","The query executor measures the real size of the hashtable and increases the number of batches if the hashtable grows too large."],"title":"Executor implementation notes"},{"code":"case T_HashJoin:\n\t\t\tpname = \"Hash\";\t\t/* \"Join\" gets added by jointype switch */\n\t\t\tsname = \"Hash Join\";\n\t\t\tbreak;","title":"EXPLAIN identity in core source"}],"tables":[{"columns":[{"key":"label","label":"Text-format label"},{"key":"identity","label":"Structured node identity"}],"key":"explain-labels","rows":[{"identity":"Hash Join","label":"Hash"}],"title":"EXPLAIN labels in this source build"}]}},"RequestedLocale":"en","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}