PostgreSQL中NOT EXISTS查询返回子查询表存在的id是什么原因？

核心原因 #

PostgreSQL的ncbi.affi_known1表主键索引出现了逻辑损坏，问题根因来自数据生成阶段的代码非正常退出：

• 主键索引是B树结构，写入过程中程序异常中断会导致索引页的变更没有完成提交，出现索引条目缺失，但表的堆存储（实际行数据）是完整的
• 执行NOT EXISTS关联查询时，PostgreSQL优化器默认会走主键索引做匹配，因为损坏的主键索引中没有id=4683763的条目，所以判定该id不存在，返回了不符合预期的结果
• 单独执行select * from ncbi.affi_known1 where id = 4683763时，优化器如果选择了全表扫描路径，就会从堆存储中直接读到正确的行数据，和索引状态无关
• 手动新建id字段索引后，新索引是基于完整的堆存储数据构建的，没有损坏，优化器切换到新索引执行关联查询，结果恢复正常；删除新索引后重新走损坏的主键索引，问题复现

验证方法 #

1. 执行以下语句查看NOT EXISTS查询的执行计划，确认关联阶段是否走了主键索引affi_known_pkey：

EXPLAIN ANALYZE
select a.id, a.affiliation 
FROM public.affiliation AS a
WHERE NOT EXISTS (
  SELECT *
  FROM ncbi.affi_known1 AS b 
  WHERE  a.id = b.id
)
limit 5000;

2. 重建ncbi.affi_known1表的主键索引后删除额外新增的id索引，测试问题是否消失：

REINDEX INDEX ncbi.affi_known_pkey;
-- 或者重建整表所有索引
REINDEX TABLE ncbi.affi_known1;

后续规避方案 #

• 调整数据写入逻辑，将单条提交改为批量提交，减少频繁提交带来的索引页变更风险，同时降低异常中断时的索引损坏概率
• 程序异常中断后，首次访问生成的表时优先执行索引重建操作，避免逻辑错误
• 初始化PostgreSQL集群时开启数据页校验和，可及时发现存储层面的页损坏问题

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