京公网安备11010502063487号“PostgreSQL 的 Nested Loop、Hash Join、Merge Join 什么时候用哪个、NOT IN 为什么那么慢” 是读执行计划时绕不开的问题。这篇在本机自己起一个 PostgreSQL 16.15,用同一个连接分别强制三种算法比较耗时,再看 work_mem 对 Hash Join 的影响和 NOT IN 的陷阱。
N 计划器的选择 计划器 ms 只用 Nested Loop 只用 Hash Join 只用 Merge Join
10 Nested Loop 0.04 0.03 66.16 0.03
1000 Hash Join 72.69 2.85 76.01 2.35
100000 Hash Join 160.67 550.43 144.82 359.68小外表加内表索引用 Nested Loop;大批量用 Hash Join;两边有序且只要前一部分时 Merge Join 占便宜。
work_mem = 64MB:Buckets: 1048576 Batches: 1 Memory Usage: 43349kB;耗时 321.00 ms work_mem = 4MB:Buckets: 262144 Batches: 8 Memory Usage: 6446kB;耗时 337.76 ms work_mem = 1MB:Buckets: 65536 Batches: 32 Memory Usage: 1615kB;耗时 340.85 ms
哈希表放不进 work_mem 就分批写临时文件,内存用量随 work_mem 变化。
limit 100000:计划器选 Merge Join,47.30 ms 强制 Hash Join:Hash Join,357.60 ms(要先把整个结果排序) 不带 limit(取全部 100 万行):计划器选 Merge Join,449.50 ms;强制 Hash Join 355.35 ms;强制 Merge Join 438.14 ms
ORDER BY 连接键加 limit 时,Merge Join 不必先排序,远快于 Hash Join。
有索引 b_a_id:Nested Loop,0.23 ms 删掉 b_a_id 之后:Hash Join,64.97 ms
删掉外键列上的索引,Nested Loop 变成 Hash Join,慢两个数量级。
work_mem = 4MB NOT IN :结果 8002 行,12.06 ms;计划里:Filter: (NOT (hashed SubPlan 1)) work_mem = 4MB NOT EXISTS :结果 8002 行,10.82 ms;计划里:-> Hash Right Anti Join work_mem = 4MB LEFT JOIN … IS NULL :结果 8002 行,10.89 ms;计划里:-> Hash Right Anti Join work_mem = 64MB NOT IN :结果 8002 行,11.33 ms;计划里:Filter: (NOT (hashed SubPlan 1)) work_mem = 64MB NOT EXISTS :结果 8002 行,10.96 ms;计划里:-> Hash Right Anti Join work_mem = 64MB LEFT JOIN … IS NULL :结果 8002 行,10.96 ms;计划里:-> Hash Right Anti Join 子查询结果更大(b6 有 100 万行)、外表 a6 有 2000 行,每条语句限时 10 秒: work_mem = 4MB NOT IN :10 s 后被 statement_timeout 取消(canceling statement due to statement timeout) work_mem = 4MB NOT EXISTS :结果 800 行,94.28 ms;计划里:-> Hash Right Anti Join work_mem = 64MB NOT IN :结果 800 行,99.00 ms;计划里:Filter: (NOT (hashed SubPlan 1)) work_mem = 64MB NOT EXISTS :结果 800 行,85.98 ms;计划里:-> Hash Right Anti Join 往 b5 里插入一个 a_id = NULL 之后:NOT IN 查到 0 行,NOT EXISTS 查到 8002 行
子查询大到放不进哈希就退化得很慢,并且含 NULL 时一行都查不到;用 NOT EXISTS。