PostgreSQL IN子句关联空表查询变慢原因及优化方案问询

问题原因 #

从两个执行计划的差异可以直接定位根因：
你用的嵌套循环半连接逻辑依赖LIMIT 15的提前终止机制：当filtered_users有数据时，数据库按rank升序扫描ai_algo的有序索引，每扫到一行就和filtered_users做匹配，只要凑够15条匹配结果就会直接终止查询，实际只需要扫描几百行ai_algo就结束，所以速度很快。
当filtered_users为空时，永远不会有匹配的结果，数据库只能把所有符合rank_timeframe = '7d'且_at_timestamp = 1626955200的ai_algo行全部扫描完，才能确定没有符合条件的结果。慢查询的执行计划里实际扫描了超过800万行ai_algo数据，这就是4秒耗时的核心来源。
优化器没有提前规避这个问题是因为它的估算结果认为filtered_users有13行，所以默认选择了「扫有序索引凑够15条就停」的低开销执行计划，无法预知filtered_users实际是空集。

优化方案 #

方案1（最易实现，性能最优） #

应用层拆分查询：

1. 先单独执行filtered_users的查询，判断返回行数是否为0
2. 如果行数为0直接返回空结果，不需要执行后续的ai_algo相关查询
   这个方案可以让空结果场景的耗时降到和filtered_users查询本身一致（<10ms）。

方案2（SQL层面修改，无需改动应用逻辑） #

强制优化器先计算filtered_users的结果集，再执行后续关联：
给filtered_users的子查询加OFFSET 0提示，强制PostgreSQL先执行完CTE的计算，获知结果集为空后会直接返回空，不会扫描ai_algo表：

valid_users as (
    SELECT aa.username, aa.rank, aa.points, aa.version
    FROM "users-results".ai_algo aa
    WHERE aa._at_timestamp = 1626955200
          AND aa.rank_timeframe = '7d'
          AND aa.username IN (SELECT * FROM filtered_users OFFSET 0)
    ORDER BY aa.rank ASC
    LIMIT 15
    OFFSET 0
)

方案3（索引优化，兼顾所有场景） #

给ai_algo建覆盖查询条件的联合有序索引，把过滤条件塞进索引里，减少扫描时的IO开销：

CREATE INDEX CONCURRENTLY idx_ai_algo_timeframe_ts_rank ON "users-results".ai_algo (rank_timeframe, _at_timestamp, rank);

这个索引可以让扫描ai_algo时不需要额外做_at_timestamp的过滤，就算真的需要全扫符合条件的行，速度也会提升数倍。

方案4（调整连接策略，避免嵌套循环的扫描问题） #

强制使用哈希连接替代嵌套循环连接，哈希连接会先计算两边的结果集再做关联，如果filtered_users为空，会直接返回空，不需要扫描全量ai_algo：
可以在查询前加会话级参数：

-- 临时关闭嵌套循环，强制用哈希连接
SET enable_nestloop = off;
-- 执行你的查询
SELECT * FROM valid_users;
-- 恢复默认配置
SET enable_nestloop = on;

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