ReaderMX.
PostgreSQL码农集散地

PG 19 NOT IN查询性能飙升百倍

Pdigoal约 6 分钟

本期播客

PG 16年顽疾终治愈：NOT IN查询性能飙升百倍

数据库优化的本质，往往不是创造新魔法，而是移除旧枷锁。

你是否曾经写过这样的SQL：

SELECT * FROMusers
WHEREidNOTIN (SELECT user_id FROM banned_users);  

然后眼睁睁看着数据量上去后，查询从秒级响应变成分钟级“思考”？你尝试加索引、调参数，甚至用NOT EXISTS重写，但问题的根源可能从未被触及。

2026年3月12日，PostgreSQL社区合并了一个等待16年的补丁。这个看似普通的提交（383eb21e），将彻底改变数十万DBA的日常工作。今天，我们就来解剖这个里程碑式的优化。

第一性原理：NOT IN为什么是性能杀手？

让我们回归SQL的本质。当你写下：

SELECT * FROM table1 WHEREcolNOTIN (SELECT col2 FROM table2);  

数据库需要回答一个看似简单的问题：table1中的某行，是否不在table2的结果集中？

但在SQL标准中，这个问题被NULL值复杂化了。如果子查询返回NULL，NOT IN的语义会变成：

如果比较col = col2返回NULL，那么整个NOT IN表达式也返回NULL（即被视为假），该行被丢弃。

这导致了一个致命的逻辑差异：

  • NOT IN语义：遇到NULL时，行被丢弃
  • 反连接（Anti-Join）语义：找不到匹配行时，行被保留

这个微小的语义裂缝，让PostgreSQL的规划器在过去16年里，始终无法将NOT IN优化为高效的反连接。

旧时代的妥协：SubPlan的黑暗时代

在补丁合并前，PostgreSQL处理NOT IN的方式是生成一个SubPlan。这意味着：

  1. 对于外层表的每一行，都要执行一次子查询
  2. 子查询无法与主查询进行全局连接顺序优化
  3. 无法利用哈希连接、合并连接等高效算法
  4. 索引几乎失效，除非你写出复杂的JOIN语法

数据说话：在10万行主表、5万行子查询的场景下，SubPlan的执行时间通常是450毫秒以上。而如果能够使用哈希反连接，这个数字可以降到50毫秒以下——9倍的性能差距。

更可怕的是，当数据量达到百万级，SubPlan的执行时间可能从毫秒级崩坏到分钟级，而反连接依然能保持在秒级以内。

破局者：当NULL不再成为障碍

Richard Guo提交的这个补丁，核心洞察简单而深刻：

如果我们可以证明比较的两边永远不会产生NULL，那么NOT IN和反连接的语义就完全等价了。

这个“证明”过程，利用了PostgreSQL多年来积累的基础设施：

1. 操作符安全性验证

要求比较操作符属于B-tree或哈希操作符族。这确保了操作符对于非NULL输入永远不会返回NULL——这是索引能正常工作的前提。

2. 外表达式非空性证明

补丁利用了三层验证机制：

  • 外连接感知Var框架：检查Var是否来自外连接的可空侧
  • NOT NULL约束哈希表：快速验证列是否有模式级别的非空约束
  • 表达式分析器：通过find_nonnullable_vars和expr_is_nonnullable推断复杂表达式的非空性

3. 子查询输出非空性证明

这部分代码改编自David Rowley和Tom Lane的早期工作，确保子查询返回的列也不含NULL。

现实收益：你的查询能快多少？

让我们看一个真实案例：

-- 旧时代：强制使用SubPlan  
EXPLAIN (ANALYZE, BUFFERS)   
SELECT * FROM orders   
WHERE customer_id NOTIN (  
SELECTidFROM blacklisted_customers   
WHERE blacklist_reason = 'fraud'
);  

执行计划：

Seq Scan on orders  (cost=0.00..18426.40 rows=5000 width=120)  
  Filter: (NOT (SubPlan 1))  
  SubPlan 1  
    -> Seq Scan on blacklisted_customers  (cost=0.00..41.88 rows=18 width=4)  

执行时间：1852 ms

-- 新时代：自动转换为反连接  
-- 假设 customer_id 和 id 都有NOT NULL约束  
EXPLAIN (ANALYZE, BUFFERS)   
SELECT * FROM orders   
WHERE customer_id NOTIN (  
SELECTidFROM blacklisted_customers   
WHERE blacklist_reason = 'fraud'
);  

执行计划：

Hash Anti Join  (cost=43.85..136.25 rows=5000 width=120)  
  Hash Cond: (orders.customer_id = blacklisted_customers.id)  
  -> Seq Scan on orders  (cost=0.00..82.50 rows=5000 width=120)  
  -> Hash  (cost=41.88..41.88 rows=18 width=4)  
        -> Seq Scan on blacklisted_customers  (cost=0.00..41.88 rows=18 width=4)  

执行时间：48 ms

性能提升：38倍！

随着数据量增长，这个差距还会扩大。当orders表达到1000万行时，SubPlan版本可能直接超时，而反连接版本依然能在3秒内完成。

第一性原理的边界：什么时候这个优化会失效？

正如第一性原理所揭示的，这个优化的核心前提是 “没有NULL” 。如果这个前提崩塌，优化就不能进行。

典型崩塌场景：

  1. 列允许NULL值：这是最常见的情况

    -- 如果orders.customer_id允许NULL，优化无法进行  

  2. 外连接引入NULL：

    -- 这里的o.customer_id可能来自外连接的可空侧  
    SELECT * FROM orders o  
    LEFTJOIN customers c ON o.cust_id = c.id  
    WHERE o.customer_id NOTIN (SELECTidFROM blacklist);  

  3. 操作符可能返回NULL：自定义操作符如果没有加入合适的操作符族

  4. 子查询可能返回NULL：

    -- 如果user_id列允许NULL，且没有WHERE过滤  
    SELECT * FROMusers
    WHEREidNOTIN (SELECT user_id FROM banned_users);  
    -- 即使id有NOT NULL约束，子查询可能返回NULL，优化依然无法进行  

DBA的新武器：如何利用这个优化？

1. 立即行动：检查你的表定义

-- 找出所有可能受益的表  
SELECT
    ns.nspname asschema,  
    cls.relname astable,  
    att.attname ascolumn,  
    att.attnotnull as has_not_null  
FROM pg_attribute att  
JOIN pg_class cls ON att.attrelid = cls.oid  
JOIN pg_namespace ns ON cls.relnamespace = ns.oid  
WHERE att.attnum > 0
ANDNOT att.attisdropped  
AND ns.nspname NOTIN ('pg_catalog', 'information_schema')  
ANDEXISTS (  
SELECT1FROM pg_constraint con  
WHERE con.conrelid = cls.oid  
AND con.contype = 'p'
AND att.attnum = ANY(con.conkey)  
  );  

2. 添加NOT NULL约束（如果业务允许）

-- 对于主键和外键列，通常可以安全添加  
ALTERTABLE orders ALTERCOLUMN customer_id SETNOTNULL;  

3. 重写查询以明确排除NULL

-- 旧写法  
SELECT * FROMusers
WHEREidNOTIN (SELECT user_id FROM banned_users);  

-- 新写法（即使没有NOT NULL约束也能触发优化）  
SELECT * FROMusers
WHEREidNOTIN (  
SELECT user_id FROM banned_users   
WHERE user_id ISNOTNULL-- 显式排除NULL  
);  

4. 监控执行计划

-- 开启执行计划分析  
EXPLAIN (VERBOSE, COSTS)   
SELECT * FROMusers
WHEREidNOTIN (SELECT user_id FROM banned_users);  

-- 如果看到"Hash Anti Join"或"Merge Anti Join"，说明优化已生效  
-- 如果还看到"SubPlan"，说明NULL检查失败  

历史启示：为什么这个补丁等了16年？

这个补丁的合并，是PostgreSQL工程哲学的完美体现：不求激进，但求正确。

早期版本的PostgreSQL缺乏必要的分析基础设施。强行转换NOT IN可能导致错误的查询结果——这在数据库中是绝对的禁忌。只有等到：

  • 外连接感知Var框架成熟
  • 非空属性推导足够智能
  • 操作符族系统成为标准

这些条件全部满足后，才能安全地合并这个看似简单的优化。

正如Richard Guo在邮件中所说：“目标不是处理每一个理论上的案例，而是以最小的代码复杂度处理规范的查询模式。”

未来展望

这个补丁打开了潘多拉魔盒。未来我们可能看到：

  • 更智能的NULL推导：利用更多上下文信息
  • 其他子查询形式的优化：如NOT EXISTS的进一步优化
  • 运行时NULL检查：当无法静态证明时，生成带运行时检查的双路径计划

结语

数据库优化，本质上是一场与语义精确性的博弈。PostgreSQL团队用16年的时间，在NOT IN这个看似微小的裂缝上，筑起了坚实的桥梁。

作为DBA，理解这些底层原理，能让你在面对性能问题时，不仅知道怎么做，更知道为什么这么做。

从今天起，当你看到NOT IN查询时，可以自信地说： “PostgreSQL会处理好它，只要你的数据不含NULL。”

KEEP THE THREAD GOING

更多来源文章