PostgreSQL中Hash Join与Nested Loop对比及哈希索引应用疑问

测试环境与准备 #

使用PostgreSQL 16.2，创建测试表并插入1000万条数据：

create table join_test (
    pk varchar(20), 
    fk varchar(20));

insert into join_test(pk, fk) 
select s::varchar(20), 
       (10000001 - s)::varchar(20) 
from generate_series(1, 10000000) as s;

执行自连接查询：

explain analyze select * 
from join_test t1 
join join_test t2 on t1.pk = t2.fk;

无索引时的执行计划 #

Hash Join  (cost=327879.85..878413.95 rows=9999860 width=28) (actual time=5181.056..18337.596 rows=10000000 loops=1)
  Hash Cond: ((t1.pk)::text = (t2.fk)::text)
  ->  Seq Scan on join_test t1  (cost=0.00..154053.60 rows=9999860 width=14) (actual time=0.070..1643.618 rows=10000000 loops=1)
  ->  Hash  (cost=154053.60..154053.60 rows=9999860 width=14) (actual time=5147.801..5147.803 rows=10000000 loops=1)
        Buckets: 262144  Batches: 128  Memory Usage: 5691kB
        ->  Seq Scan on join_test t2  (cost=0.00..154053.60 rows=9999860 width=14) (actual time=0.024..2163.714 rows=10000000 loops=1)
Planning Time: 0.172 ms
Execution Time: 18718.586 ms

无索引时优化器选择Hash Join，动态构建哈希表完成批量匹配，结果符合预期。

创建哈希索引后的执行计划 #

在pk列创建哈希索引后，再次执行相同查询，得到执行计划：

Nested Loop  (cost=0.00..776349.75 rows=9999860 width=28) (actual time=0.107..85991.520 rows=10000000 loops=1)
  ->  Seq Scan on join_test t2  (cost=0.00..154053.60 rows=9999860 width=14) (actual time=0.062..1399.400 rows=10000000 loops=1)
  ->  Index Scan using join_test_pk_idx on join_test t1  (cost=0.00..0.05 rows=1 width=14) (actual time=0.008..0.008 rows=1 loops=10000000)
        Index Cond: ((pk)::text = (t2.fk)::text)
        Rows Removed by Index Recheck: 0
Planning Time: 0.195 ms
Execution Time: 86490.687 ms

优化器选择Nested Loop通过哈希索引查找匹配行，但性能反而大幅下降。

技术疑问 #

• 理论上带索引的查询应更高效，为何实际性能反而大幅下降？
• 使用哈希索引实现连接是否具备实际意义，还是应始终选用B-tree索引？

解答 #

问题1：性能下降的核心原因 #

这里的Nested Loop是循环嵌套单次查找，和Hash Join的批量处理逻辑有本质区别：

1. 批量vs单次开销：Hash Join一次性将t2数据构建成哈希表（内存+磁盘分批），然后用t1数据批量匹配，CPU缓存命中率高，整体IO和计算是批量模式；而Nested Loop需要遍历t2的1000万行，每一行都单独发起一次哈希索引查找，1000万次的索引定位、函数调用开销累积后，远大于Hash Join的批量处理成本。
2. 哈希索引的回表开销：PostgreSQL哈希索引不支持仅索引扫描（Index-Only Scan），每次查找都需要回表获取完整行数据，进一步增加了IO开销；而Hash Join在构建哈希表时直接存储查询所需列，无需回表。

问题2：哈希索引用于连接的实际意义 #

哈希索引并非完全不适合连接，但适用场景非常有限：

• 小表驱动的连接：如果驱动表（如上例的t2）数据量极小（比如几百行），那么Nested Loop+哈希索引的开销会远低于Hash Join的哈希表构建成本，此时哈希索引能发挥作用。
• 纯等值查询场景：哈希索引的优势是等值查找效率接近B-tree，但仅支持等值匹配；B-tree索引则支持范围查询、排序、仅索引扫描等更多功能，优化器对B-tree的支持也更成熟，在连接场景中还能配合Merge Join（数据有序时）使用，适用范围远大于哈希索引。
• 绝大多数场景下，B-tree索引是更优选择：无论是连接、查询还是排序，B-tree的通用性和性能表现都更稳定，哈希索引仅适合特定的小表等值连接场景。

内容的提问来源于stack exchange，提问作者Anton Ivanov