为何创建索引后Nested Loop替代Hash Join？SQL执行计划疑问

算力阿峰

2026-5-15

为什么创建索引后查询优化器选择Nested Loop而非Hash Join？

问题背景

我是SQL新手，执行了以下SQL代码（包含表创建、数据插入、索引创建前后的EXPLAIN ANALYZE）：

DROP TABLE if exists students; DROP TABLE if exists grades; 
CREATE TABLE students( s_id integer NOT NULL PRIMARY KEY, s_name text, s_last_name text, curr_year integer ); 
CREATE TABLE grades( s_id integer NOT NULL PRIMARY KEY, course text, c_year integer, grade integer, FOREIGN KEY (s_id) REFERENCES students ); 
INSERT INTO students (s_id, s_name, s_last_name, curr_year) VALUES (1, 'A', 'S', 3); 
INSERT INTO students (s_id, s_name, s_last_name, curr_year) VALUES (2, 'A', 'A', 2); 
INSERT INTO students (s_id, s_name, s_last_name, curr_year) VALUES (3, 'V', 'B', 1); 
INSERT INTO students (s_id, s_name, s_last_name, curr_year) VALUES (4, 'K', 'N', 2); 
INSERT INTO grades (s_id, course, c_year, grade) VALUES (1, 'DB', 2, 98); 
INSERT INTO grades (s_id, course, c_year, grade) VALUES (2, 'OS', 3, 90); 
INSERT INTO grades (s_id, course, c_year, grade) VALUES (3, 'DB', 2, 94); 

-- 创建索引前的执行计划
EXPLAIN ANALYZE SELECT * FROM students s JOIN grades gr ON s.s_id = gr.s_id WHERE curr_year > 0; 

CREATE INDEX student_details ON students(s_id, s_name, s_last_name); 
CREATE INDEX student_courses ON grades(s_id, course); 

-- 创建索引后的执行计划
EXPLAIN ANALYZE SELECT * FROM students s JOIN grades gr ON s.s_id = gr.s_id WHERE curr_year > 0; 

DROP INDEX student_details; DROP INDEX student_courses; 
DROP TABLE students CASCADE; DROP TABLE grades CASCADE;

创建索引前的执行计划（Hash Join）：

Hash Join (cost=23.50..51.74 rows=270 width=116) (actual time=0.039..0.050 rows=3 loops=1)
Hash Cond: (gr.s_id = s.s_id)
-> Seq Scan on grades gr (cost=0.00..21.30 rows=1130 width=44) (actual time=0.005..0.008 rows=3 loops=1)
-> Hash (cost=20.12..20.12 rows=270 width=72) (actual time=0.021..0.021 rows=4 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on students s (cost=0.00..20.12 rows=270 width=72) (actual time=0.006..0.011 rows=4 loops=1)
Filter: (curr_year > 0)
Planning time: 0.147 ms
Execution time: 0.089 ms (9 rows)

创建索引后的执行计划（Nested Loop）：

Nested Loop (cost=0.00..2.12 rows=1 width=116) (actual time=0.031..0.116 rows=3 loops=1)
Join Filter: (s.s_id = gr.s_id)
Rows Removed by Join Filter: 9
-> Seq Scan on students s (cost=0.00..1.05 rows=1 width=72) (actual time=0.012..0.018 rows=4 loops=1)
Filter: (curr_year > 0)
-> Seq Scan on grades gr (cost=0.00..1.03 rows=3 width=44) (actual time=0.003..0.009 rows=3 loops=4)
Planning time: 0.396 ms
Execution time: 0.170 ms

我无法理解其中原因，为何创建索引后查询优化器会选择Nested Loop而非Hash Join？

专业解释

1. 先划重点：你创建的索引其实没被用到

仔细看创建索引后的执行计划，依然是Seq Scan（全表扫描）操作，完全没有提到你创建的student_details和student_courses索引。这是因为对于只有4行的students表和3行的grades表来说，全表扫描的成本远低于走索引的成本。

索引本身是额外的磁盘数据结构，使用索引需要先查找索引条目，再回表读取对应的数据行——这个过程的IO和计算开销，比直接一次性扫完整个小表要大得多。查询优化器会基于成本估算做出选择，它显然算得清这笔账。

2. 数据量极小是连接方式切换的核心原因

查询优化器选择连接方式的核心依据是执行成本估算，而数据量大小是影响成本的关键因素：

Hash Join：适合大表之间的连接。它需要先把其中一个表的数据构建成哈希表，这个构建过程有固定的开销。当数据量很大时，这个固定开销摊到每一行上的成本会被稀释，整体效率更高。但对于极小的表，构建哈希表的开销反而显得得不偿失。
Nested Loop：适合小表之间的连接。它的逻辑非常简单：拿驱动表（这里是students，仅4行）的每一行，去遍历另一个表（grades，仅3行）匹配符合条件的行。总共只需要做4×3=12次比较，这个工作量比构建哈希表再匹配要小得多，优化器估算出Nested Loop的总成本更低，自然就选择了它。

3. 创建索引为什么会触发这个变化？

其实不是索引本身被使用了，而是创建索引后，PostgreSQL的查询优化器会重新收集表的统计信息，并重新评估所有可能的执行路径的成本。

在你未创建索引时，优化器可能估算Hash Join的成本略低（虽然实际执行时间差异不大）；创建索引后，优化器重新计算了各种路径的成本，发现对于这种极小表的连接场景，Nested Loop的成本比Hash Join更低，因此切换了执行计划。

4. 验证：数据量变大后结果会不同

如果把你的表数据量放大（比如给students插入几千行，grades插入几万行），你会看到两个明显变化：

优化器会开始使用你创建的索引，比如用索引快速筛选出符合curr_year > 0的学生行，而不是全表扫描；
连接方式会根据数据量重新选择：如果驱动表依然很小，可能还是Nested Loop；如果两个表都很大，优化器可能又会换回Hash Join（甚至Merge Join）。

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