PostgreSQL中WHERE NOT IN查询过慢，如何获取全列查询结果？

我之前也踩过类似的坑——NOT IN在处理多列子查询时，数据库优化器经常会生成低效的执行计划，尤其是当子查询结果集较大时，速度会慢到离谱。不过既然你已经发现EXCEPT的写法速度正常，那我们可以基于这个高效的逻辑来扩展，拿到你需要的4列数据。下面是几个亲测有效的方案：

方案1：用EXCEPT筛选ID对，再关联location表取名称 #

既然EXCEPT能快速得到不在distance里的(source_id, destination_id)对，那我们可以把这个结果作为子查询，再和location表做两次关联，获取对应的名称字段。这样既保留了EXCEPT的高效，又能拿到完整的4列数据：

SELECT 
    source.name AS source, 
    pair.source_id, 
    dest.name AS destination, 
    pair.destination_id
FROM (
    -- 先用EXCEPT快速筛选出需要的ID对
    SELECT source.id AS source_id, dest.id AS destination_id 
    FROM location AS source, location AS dest 
    EXCEPT 
    SELECT source_id, destination_id FROM distance
) AS pair
-- 关联location表获取source的名称
JOIN location AS source ON pair.source_id = source.id
-- 关联location表获取dest的名称
JOIN location AS dest ON pair.destination_id = dest.id

这个写法的核心是让EXCEPT只处理ID（通常是主键，查询和对比都极快），再通过主键关联拿名称，整个过程的执行计划会非常高效。

方案2：用LEFT JOIN + IS NULL替代NOT IN #

NOT IN的执行逻辑有时候不如LEFT JOIN优化得好，尤其是当子查询存在NULL值时（哪怕你的场景里没有，也可以试试这个写法）。我们可以用CROSS JOIN生成所有可能的source-dest组合，再通过LEFT JOIN匹配distance表，最后筛选出没有匹配到的记录：

SELECT 
    source.name AS source, 
    source.id AS source_id, 
    dest.name AS destination, 
    dest.id AS destination_id
FROM location AS source
-- 生成所有source和dest的组合
CROSS JOIN location AS dest
-- 左关联distance表，匹配对应的ID对
LEFT JOIN distance d 
    ON d.source_id = source.id 
    AND d.destination_id = dest.id
-- 筛选出在distance里没有匹配的记录
WHERE d.source_id IS NULL

这个写法的优势是优化器更容易生成哈希连接或者嵌套循环的高效执行计划，比NOT IN的性能提升明显。

方案3：给distance表添加联合索引 #

不管用上面哪种方案，给distance表的source_id和destination_id加个联合索引，都能进一步加速匹配过程：

CREATE INDEX idx_distance_source_dest ON distance(source_id, destination_id);

这个索引能让数据库快速定位到对应的ID对，不管是EXCEPT的对比还是LEFT JOIN的匹配，都会更快完成。

总结 #

优先试试方案1，因为你已经验证了EXCEPT的速度，在此基础上扩展最稳妥。如果还想优化，可以加上方案3的索引。方案2作为备选，适合某些优化器对NOT IN支持不好的场景。

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