PostgreSQL中NOT IN查询未用索引且性能逊于IN的原因分析

一、性能差异的核心原因 #

对比两个查询的执行计划，能看到几个关键差异：

1. 连接类型与逻辑复杂度

   • IN 查询用的是Parallel Hash Join：只要找到table1行在table2中的任意匹配项，就保留该行，逻辑简单直接，匹配到就停止检查。
   • NOT IN 查询用的是Parallel Hash Anti Join：需要确认table1的行在table2中完全没有匹配项才保留。这意味着对于table1中每一行，即使找到多个cola相同但colb不同的行，也得全部检查完，才能确定是否符合条件，计算开销更大。

2. 连接条件的效率差异

   • IN 查询的Hash Cond直接匹配了完整的联合键：((table1.cola = table2.cola) AND ((table1.colb)::text = (table2.colb)::text))，一步到位完成匹配，没有额外过滤开销。
   • NOT IN 查询的Hash Cond只匹配了cola，剩下的colb和 NULL 处理交给了Join Filter，这导致执行过程中1.4亿行被过滤，额外的比较操作大幅增加了耗时。

3. 结果集的间接影响
   虽然NOT IN返回的结果集只有30万行，远小于IN的3000万行，但 Hash Anti Join 的逻辑本身需要对每一行做“无匹配”的确认，这种反向验证的成本远高于正向匹配。

二、为什么没有使用索引 #

PostgreSQL 优化器选择顺序扫描而非索引扫描，主要基于以下判断：

• 数据量规模：当需要扫描表的大部分数据时（比如IN查询要返回99%的行，NOT IN虽然返回少，但需要全表扫描来排除匹配项），顺序扫描的效率更高。因为索引扫描需要先读索引页，再回表读数据页，带来额外的随机IO开销；而顺序扫描是连续读取数据页，IO效率更高。
• 哈希连接的成本优势：对于大表之间的连接，哈希连接的成本通常低于嵌套循环（依赖索引查找的连接方式），优化器认为哈希连接的整体开销更小。

三、优化建议 #

1. 用 LEFT JOIN + IS NULL 重写 NOT IN 查询
   这种写法有时会让优化器生成更高效的执行计划，同时避免 NOT IN 对 NULL 值的敏感问题：

   SELECT t1.cola, t1.colb
   FROM table1 t1
   LEFT JOIN table2 t2 ON t1.cola = t2.cola AND t1.colb = t2.colb
   WHERE t2.id IS NULL;
   

2. 用 NOT EXISTS 替代 NOT IN
   NOT EXISTS 的逻辑更清晰，优化器对其的处理通常更友好，同时避免 NULL 值导致的意外结果：

   SELECT cola, colb
   FROM table1 t1
   WHERE NOT EXISTS (
       SELECT 1
       FROM table2 t2
       WHERE t1.cola = t2.cola AND t1.colb = t2.colb
   );
   

3. 更新统计信息
   确保表的统计信息是最新的，让优化器能更准确地估计行数和选择最优计划：

   ANALYZE table1;
   ANALYZE table2;
   

4. 测试索引扫描（谨慎使用）
   可以临时关闭顺序扫描，强制优化器使用索引，对比性能（但通常优化器的选择更合理）：

   SET enable_seqscan = off;
   -- 执行你的 NOT IN 查询
   SET enable_seqscan = on;
   

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