八月 22, 2025
摘要：在本教程中，你将了解在执行计划中可能会看到的各种连接 “节点” 或操作。
目录
Hash Join / Hash
这个操作有两个子操作。其中一个总是 “Hash”，另一个则是其他操作。
顾名思义，哈希连接用于连接两个记录集。例如：
explain analyze select * from pg_class c
join pg_namespace n on c.relnamespace = n.oid;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Hash Join (cost=1.14..16.07 rows=292 width=316) (actual time=0.036..0.343 rows=295 loops=1)
Hash Cond: (c.relnamespace = n.oid)
-> Seq Scan on pg_class c (cost=0.00..10.92 rows=292 width=203) (actual time=0.007..0.044 rows=295 loops=1)
-> Hash (cost=1.06..1.06 rows=6 width=117) (actual time=0.012..0.012 rows=6 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on pg_namespace n (cost=0.00..1.06 rows=6 width=117) (actual time=0.004..0.005 rows=6 loops=1)
Total runtime: 0.462 ms
(7 rows)
它的工作原理是：首先，哈希连接调用 “Hash” 操作，Hash 操作再调用其他操作（在我们的例子中是对 pg_namespace 的顺序扫描）。然后，哈希操作根据用于连接数据的键（在我们的例子中是 pg_namespace 中的 OID 列），为来自数据源的行在内存（或磁盘，取决于大小）中创建一个哈希表或关联数组或字典。
当然，对于给定的连接键，可能有多行。在这个例子中不是这样，因为我们使用了主键进行连接，但一般来说，单个哈希键对应多行是完全可能的。
所以，使用 Perl 的表示法，哈希操作的输出类似于：
{
'123' => [ { data for row with OID = 123 }, ],
'256' => [ { data for row with OID = 256 }, ],
...
}
然后，哈希连接运行第二个子操作（在我们的例子中是对 pg_class 的顺序扫描），对于从中获取的每一行，它会：
- 检查连接键（在我们的例子中是 pg_class.relnamespace）是否存在于哈希操作返回的哈希表中。
- 如果不存在，子操作返回的行将被忽略，不会被返回。
- 如果存在，哈希连接从哈希表中获取行，并根据一侧的行和哈希表中的所有行生成输出行。
需要注意的是，两侧的操作都只运行一次（在我们的例子中，都是顺序扫描），但第一个操作（由哈希调用的那个）必须返回所有行，这些行必须存储在哈希表中，而另一个操作则逐行处理，如果在另一侧的哈希表中不存在对应的行，有些行就会被跳过（希望这句话是清晰的，这里有很多 “哈希”）。
当然，由于两个子扫描可以是任何类型的操作，它们可以进行过滤、索引扫描或任何你能想到的操作。
关于哈希连接 / 哈希操作的最后一点是，哈希操作和排序、哈希聚合一样，最多会使用 work_mem 大小的内存。
Nested Loop
既然我们谈到了连接，就必须讨论嵌套循环。示例：
explain analyze select a.* from pg_class c
join pg_attribute a on c.oid = a.attrelid
where c.relname in ( 'pg_class', 'pg_namespace' );
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.28..52.32 rows=16 width=203) (actual time=0.057..0.134 rows=46 loops=1)
-> Seq Scan on pg_class c (cost=0.00..11.65 rows=2 width=4) (actual time=0.043..0.080 rows=2 loops=1)
Filter: (relname = ANY ('{pg_class,pg_namespace}'::name[]))
Rows Removed by Filter: 291
-> Index Scan using pg_attribute_relid_attnum_index on pg_attribute a (cost=0.28..20.25 rows=8 width=203) (actual time=0.007..0.015 rows=23 loops=2)
Index Cond: (attrelid = c.oid)
Total runtime: 0.182 ms
这是一个非常有趣的计划，因为它可以多次运行给定的操作。
和哈希连接一样，嵌套循环有两个 “子节点”。一般来说，首先运行第一个子节点，在我们的例子中，它运行 “Seq Scan”，然后，对于第一个子节点返回的每一行（在我们的例子中是 2 行），它运行第二个操作（在我们的例子中是对 pg_attribute 的索引扫描）。
你可能注意到索引扫描的实际运行信息中有 “loops=2”。这意味着这个操作被运行了两次，其他值（行数、时间）是所有运行的平均值。
让我们看看下面的这个计划。请注意，categories 索引扫描的实际时间是 0.002 到 0.003 毫秒。但这个节点的总时间是 78.852 毫秒，因为这个索引扫描被运行了超过 26000 次。
Nested Loop (cost=0.00..10715.90 rows=26284 width=4449) (actual time=0.054..291.131 rows=26284 loops=1)
-> Index Scan using books_index_title on books (cost=0.00..3306.28 rows=26284 width=3357) (actual time=0.033..50.773 rows=26284 loops=1)
-> Index Scan using categories_pkey on categories (cost=0.00..0.27 rows=1 width=1092) (actual time=0.002..0.003 rows=1 loops=26284)
Index Cond: (categories.id = books.category_id)
Total runtime: 312.212 ms
所以，其处理过程如下：
- 嵌套循环运行连接一侧的操作一次。我们称之为 “A”。
- 对于 “A” 中的每一行，它运行连接另一侧的操作（我们称之为 “B”）。
- 如果 “B” 没有返回任何行，“A” 中的数据会被忽略。
- 如果 “B” 返回了行，对于它返回的每一行，嵌套循环会基于 A 中的当前行和 B 中的当前行返回一个新行。
Merge Join
另一种连接数据的方法叫做合并连接。如果要连接的数据集已经按连接键排序（或者可以低成本地排序），就会使用这种方法。
在这里，我们通过使用在连接之前对数据进行排序的子查询来强制使用它：
explain analyze select * from
(select oid, * from pg_class order by oid) as c
join
(select * from pg_attribute a order by attrelid) as a
on c.oid = a.attrelid;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=23.16..268.01 rows=2273 width=410) (actual time=0.658..3.779 rows=2274 loops=1)
Merge Cond: (pg_class.oid = a.attrelid)
-> Sort (cost=22.88..23.61 rows=292 width=207) (actual time=0.624..0.655 rows=293 loops=1)
Sort Key: pg_class.oid
Sort Method: quicksort Memory: 102kB
-> Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=207) (actual time=0.011..0.211 rows=293 loops=1)
-> Materialize (cost=0.28..212.34 rows=2273 width=203) (actual time=0.028..1.264 rows=2274 loops=1)
-> Index Scan using pg_attribute_relid_attnum_index on pg_attribute a (cost=0.28..183.92 rows=2273 width=203) (actual time=0.015..0.752 rows=2274 loops=1)
Total runtime: 4.009 ms
(9 rows)
和其他连接方式一样，合并连接会运行两个子操作（在本例中是排序和物化）。由于这两个子操作返回的数据都是经过排序的，且排序顺序与连接操作所需的顺序一致，因此 PostgreSQL 可以同时扫描这两个子操作返回的结果集，并直接检查标识符是否匹配。
其处理流程如下：
- 如果右侧的连接列与左侧的连接列相同：
- 基于右侧和左侧当前行生成新的连接行并返回
- 从右侧获取下一行（如果右侧没有更多行，则从左侧获取）
- 回到步骤 1
- 如果右侧的连接列 “小于” 左侧的连接列：
- 从右侧获取下一行（如果没有更多行，则结束处理）
- 回到步骤 1
- 如果右侧的连接列 “大于” 左侧的连接列：
- 从左侧获取下一行（如果没有更多行，则结束处理）
- 回到步骤 1
这是一种非常巧妙的数据集连接方式，但它仅适用于已排序的数据源。
Hash Join / Nested Loop / Merge Join 的变体
在上面的所有示例中，展示的连接操作都只有在从连接的两侧都获取到行时才会返回结果行。
但情况并非总是如此。我们还可以使用左外连接（LEFT JOIN）、右外连接（RIGHT JOIN）、全外连接（FULL JOIN），以及所谓的反连接（Anti Join）。
对于左外连接或右外连接，操作名称会相应变为：
- 哈希左连接（Hash Left Join）
- 哈希右连接（Hash Right Join）
- 合并左连接（Merge Left Join）
- 合并右连接（Merge Right Join）
- 嵌套循环左连接（Nested Loop Left Join）
不存在嵌套循环右连接（Nested Loop Right Join），因为嵌套循环总是以左侧作为循环的基础。因此，对于使用右连接且本应采用嵌套循环的情况，PostgreSQL 会在内部将其转换为左连接，以便嵌套循环能够正常工作。
在所有这些情况下，逻辑都很简单：连接有左右两侧，当连接名称中提及某一侧时，即使另一侧没有匹配的行，连接操作也会返回新的结果行。
这种情况出现在类似下面的查询中：
select * from a left join b on ...
或者右连接的情况。
哈希连接、合并连接或嵌套循环的其他所有特性都保持不变，只是在何时生成输出行的逻辑上略有不同。
还有一种全连接（Full Join），其操作名称为：
- 哈希全连接（Hash Full Join）
- 合并全连接（Merge Full Join）
在这种情况下，只要其中一侧有数据，无论另一侧是否有匹配数据，连接操作都会生成新的输出行。这种情况出现在类似下面的查询中：
select * from a full join b ...
当然，其所有处理过程与前面所述一致。
还有所谓的反连接（Anti Joins），其操作名称如下：
- 哈希反连接（Hash Anti Join）
- 合并反连接（Merge Anti Join）
- 嵌套循环反连接（Nested Loop Anti Join）
在这些情况下，只有当右侧没有找到任何匹配的行时，连接操作才会输出左侧的行。这在执行 WHERE not exists () 或 left join … where right_table.column is null 之类的操作时非常有用。
比如下面的例子：
explain analyze select * from pg_class c
where not exists (select * from pg_attribute a
where a.attrelid = c.oid and a.attnum = 10);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Anti Join (cost=62.27..78.66 rows=250 width=203) (actual time=0.145..0.448 rows=251 loops=1)
Hash Cond: (c.oid = a.attrelid)
-> Seq Scan on pg_class c (cost=0.00..10.92 rows=292 width=207) (actual time=0.009..0.195 rows=293 loops=1)
-> Hash (cost=61.75..61.75 rows=42 width=4) (actual time=0.123..0.123 rows=42 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 2kB
-> Index Only Scan using pg_attribute_relid_attnum_index on pg_attribute a (cost=0.28..61.75 rows=42 width=4) (actual time=0.021..0.109 rows=42 loops=1)
Index Cond: (attnum = 10)
Heap Fetches: 0
Total runtime: 0.521 ms
(9 rows)
在这里，PostgreSQL 运行了右侧操作（对 pg_attribute 的索引扫描），对其进行哈希处理，然后运行左侧操作（对 pg_class 的顺序扫描），只返回在哈希表中没有对应 pg_class.oid 项的行。