PostgreSQL中如何借助索引高效查找无外键引用的行

1 先修正现有查询的逻辑错误 #

你原来的LEFT JOIN写法存在逻辑问题：将analysis_version = 1放在WHERE条件中时，LEFT JOIN无匹配的行对应的analysis_version为NULL，会被条件过滤，永远查不到符合要求的数据。正确的LEFT JOIN写法需要把版本条件放到JOIN的ON子句中：

SELECT *
FROM oranges
LEFT JOIN orange_analysis ON oranges.orange_id = orange_analysis.orange_id 
  AND orange_analysis.analysis_version = 1
WHERE orange_analysis.orange_id IS NULL
ORDER BY oranges.created_at DESC
LIMIT 500;

2 最优实现方案（无需预插空记录） #

你担心的NOT EXISTS无法利用索引的问题不存在，只要配置合适的索引，这类查询完全可以避免全表扫描，效率远高于预插空记录的方案。

2.1 推荐查询语句 #

优先选择NOT EXISTS写法，PostgreSQL对这类查询的优化和LEFT JOIN基本一致，语义更清晰：

SELECT *
FROM oranges
WHERE NOT EXISTS (
    SELECT 1 FROM orange_analysis 
    WHERE orange_analysis.orange_id = oranges.orange_id 
      AND analysis_version = 1
)
ORDER BY created_at DESC
LIMIT 500;

2.2 配套索引配置 #

• orange_analysis表保留原有的(orange_id, analysis_version)联合主键即可满足需求，如果要极致优化，可以额外建一个适配子查询逻辑的索引：

  CREATE INDEX idx_analysis_version_orange ON orange_analysis(analysis_version, orange_id);
  

• oranges表已有的created_at btree索引不需要改动。

2.3 执行效率说明 #

PostgreSQL优化器会自动选择最优执行路径：先反向扫描oranges的created_at索引（匹配你倒序取最新数据的需求），每取出一条orange_id就去orange_analysis的索引里校验是否存在对应版本的分析记录，凑够500条符合要求的数据就停止执行。绝大多数场景下待分析的都是最新创建的橙子，整个过程只需要扫描几百条索引，完全不会触发全表扫描，查询延迟在毫秒级。

3 预插空记录方案的弊端 #

你提到的预插空记录方案存在明显缺陷，不推荐使用：

• 新增分析版本时需要全量插入所有历史橙子的空记录，超大表下该操作会占用大量IO和存储空间，甚至导致业务阻塞
• 多版本场景下会成倍放大存储占用，N个版本就需要N倍的orange_analysis存储空间，性价比极低
• 额外增加了分析流程的写入开销，每次分析完成还要执行更新操作

4 额外优化建议 #

如果你的分析程序只会处理最近一段时间的橙子，可以在查询条件中增加created_at的范围过滤，进一步缩小扫描范围：

SELECT *
FROM oranges
WHERE created_at >= NOW() - INTERVAL '30 days'
  AND NOT EXISTS (
    SELECT 1 FROM orange_analysis 
    WHERE orange_analysis.orange_id = oranges.orange_id 
      AND analysis_version = 1
)
ORDER BY created_at DESC
LIMIT 500;

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