SQL查询中NOT IN为何远慢于IN？

最近我发现了一个（至少对我而言）挺令人惊讶的现象：在PostgreSQL数据库中，IN和NOT IN的性能差异居然特别显著。

我针对下面这条DELETE查询做了EXPLAIN分析：

EXPLAIN DELETE FROM AuditTaskImpl l 
WHERE l.processInstanceId in (
    select spl.processInstanceId 
    FROM ProcessInstanceLog spl 
    WHERE spl.status not in ( 2, 3)
)

得到的执行计划片段如下：

Delete on audittaskimpl l (cost=2794.48..6373.52 rows=50859 width=12)
-> Hash Semi Join (cost=2794.48..6373.52 rows=50859 width=12)
Hash Cond: (l.processinstanceid = spl.processinstanceid)
-> S...

这里值得注意的是，PostgreSQL对IN子查询通常会优化为高效的Hash Semi Join；但如果换成NOT IN的话，往往会生成效率更低的Nested Loop Anti Join，尤其是当子查询返回的数据集较大时，性能差距会被进一步放大。如果你的业务场景中遇到NOT IN性能不佳的情况，不妨尝试用NOT EXISTS来替代，通常能得到更优的执行计划和性能表现。

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