PostgreSQL中对比两个可空值的最快实现方法咨询

两种现有写法的效率对比 #

结论：where ((a = b) or (a is null and b is null)) 执行效率远高于coalesce写法，原因如下：

• 无额外函数开销：第一种是原生逻辑判断，不需要调用函数处理字段值，CPU计算成本更低
• 索引友好：如果a或b字段建有普通索引，PostgreSQL的查询优化器可以直接识别该逻辑，走索引扫描；而coalesce写法属于函数计算，除非提前针对coalesce(a, 'SENTINEL_VALUE')、coalesce(b, 'SENTINEL_VALUE')建立专用函数索引，否则完全无法命中普通索引，数据量大时只能走全表扫描，性能差距非常明显
• 无逻辑隐患：coalesce写法需要保证选择的哨兵值不会出现在a、b字段的实际取值中，否则会出现误判，第一种写法则没有该问题

性能更优的原生实现方案 #

PostgreSQL提供了原生的相等判断运算符IS NOT DISTINCT FROM，专门用于处理包含NULL值的等值对比场景，语法为：

where a IS NOT DISTINCT FROM b

该运算符的逻辑和第一种手动拼接的判断逻辑完全一致，但优势非常明显：

• 语法简洁，不需要写复杂的or嵌套条件
• 优化器支持更好，同等条件下执行效率比手动拼接的条件更高
• 完美支持普通字段索引，不需要额外建函数索引，也没有哨兵值误判的风险

额外性能优化建议 #

如果该对比逻辑是高频查询条件，可以针对a、b字段建立联合索引，查询时可以直接命中索引，进一步降低查询耗时。

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