含空结果子查询的SQL语句执行缓慢原因咨询

首先，我们来拆解你的问题核心：单独执行子查询速度极快且返回空，替换子查询为NULL后主查询也瞬间完成，但原查询却走了全表扫描，耗时21ms。这背后是PostgreSQL查询优化器对OR条件结合子查询的处理逻辑导致的。

执行计划对比关键差异 #

先看原查询的执行计划：

Aggregate (cost=6583.49..6583.50 rows=1 width=32) (actual time=21.702..21.702 rows=1 loops=1)
  -> Seq Scan on adverts (cost=6.31..6533.88 rows=19842 width=4) (actual time=0.462..21.684 rows=44 loops=1)
        Filter: ((discarded_at IS NULL) AND visible AND ((type)::text = 'Businesses::Restaurant'::text) AND ((city_location_id = 56) OR (hashed SubPlan 1)))
        Rows Removed by Filter: 46217

这里优化器选择了全表扫描（Seq Scan），扫描了4万多行数据，只筛选出44行，效率极低。

再看替换子查询为NULL后的执行计划：

Aggregate (cost=162.66..162.67 rows=1 width=32) (actual time=0.309..0.310 rows=1 loops=1)
  -> Bitmap Heap Scan on adverts (cost=4.72..162.55 rows=42 width=4) (actual time=0.082..0.278 rows=44 loops=1)
        Recheck Cond: (city_location_id = 56)
        Filter: ((discarded_at IS NULL) AND visible AND ((type)::text = 'Businesses::Restaurant'::text))
        Heap Blocks: exact=42
        -> Bitmap Index Scan on index_adverts_on_city_location_id_and_visible (cost=0.00..4.71 rows=42 width=0) (actual time=0.043..0.044 rows=44 loops=1)
              Index Cond: ((city_location_id = 56) AND (visible = true))

这里优化器识别到city_location_id IN (NULL)永远为false，所以条件简化为city_location_id=56，直接使用了index_adverts_on_city_location_id_and_visible索引，效率大幅提升。

为什么会出现这种差异？ #

问题出在PostgreSQL优化器的规划阶段评估逻辑：

• 当你使用OR条件结合子查询时，优化器默认不会提前执行子查询来判断结果是否为空（即使子查询是不相关的）。它会假设子查询可能返回数据，因此认为OR条件需要匹配更多行，最终选择了全表扫描而非索引扫描。
• 而当你直接写IN (NULL)时，优化器能立刻识别这个条件恒为假，从而简化整个WHERE子句，自然会选择最优的索引路径。

解决方法 #

你可以通过以下几种方式帮助优化器做出正确的选择：

1. 用CTE提前执行子查询 #

CTE会强制优化器先执行子查询，获取空结果后，主查询的OR条件会被自动简化：

WITH subquery_result AS (
    SELECT "city_locations"."id" FROM "city_locations" 
    WHERE "city_locations"."type" IN ('Arrondissement') 
    AND "city_locations"."arrondissement_city_id" = 56
)
SELECT AVG("adverts"."price") 
FROM "adverts" 
WHERE "adverts"."type" IN ('Businesses::Restaurant') 
AND "adverts"."discarded_at" IS NULL 
AND "adverts"."visible" = true 
AND (
    "adverts"."city_location_id" = 56 
    OR "adverts"."city_location_id" IN (SELECT id FROM subquery_result)
);

2. 用UNION ALL拆分OR条件 #

将OR拆分为两个独立的查询，让每个部分都能利用索引扫描，再合并结果：

SELECT AVG(price) FROM (
    SELECT "adverts"."price" 
    FROM "adverts" 
    WHERE "adverts"."type" IN ('Businesses::Restaurant') 
    AND "adverts"."discarded_at" IS NULL 
    AND "adverts"."visible" = true 
    AND "adverts"."city_location_id" = 56
    UNION ALL
    SELECT "adverts"."price" 
    FROM "adverts" 
    WHERE "adverts"."type" IN ('Businesses::Restaurant') 
    AND "adverts"."discarded_at" IS NULL 
    AND "adverts"."visible" = true 
    AND "adverts"."city_location_id" IN (
        SELECT "city_locations"."id" FROM "city_locations" 
        WHERE "city_locations"."type" IN ('Arrondissement') 
        AND "city_locations"."arrondissement_city_id" = 56
    )
) AS combined;

3. 用物化子查询强制优化器评估结果 #

通过内层子查询强制优化器先获取子查询的空结果，再优化主查询：

SELECT AVG("adverts"."price") 
FROM "adverts" 
WHERE "adverts"."type" IN ('Businesses::Restaurant') 
AND "adverts"."discarded_at" IS NULL 
AND "adverts"."visible" = true 
AND (
    "adverts"."city_location_id" = 56 
    OR "adverts"."city_location_id" IN (
        SELECT id FROM (
            SELECT "city_locations"."id" FROM "city_locations" 
            WHERE "city_locations"."type" IN ('Arrondissement') 
            AND "city_locations"."arrondissement_city_id" = 56
        ) AS mat_subquery
    )
);

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