sql - 为什么这个查询慢，where id in (sub select that returns null)

Question

我很难理解为什么这个查询花费的时间超过 1 毫秒。

EXPLAIN ANALYZE 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 "city_locations"."id" 
                                         FROM "city_locations" 
                                         WHERE "city_locations"."type" IN ('Arrondissement') 
                                        AND "city_locations"."arrondissement_city_id" = 56));

QUERY PLAN

 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
         SubPlan 1
           ->  Index Scan using index_city_locations_on_arrondissement_city_id on city_locations  (cost=0.29..6.31 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=1)
                 Index Cond: (arrondissement_city_id = 56)
                 Filter: ((type)::text = 'Arrondissement'::text)
 Planning Time: 0.173 ms
 Execution Time: 21.739 ms

执行时间为21ms

如果我执行我得到的子请求:

EXPLAIN ANALYZE SELECT "city_locations"."id" FROM "city_locations" WHERE "city_locations"."type" IN ('Arrondissement') AND "city_locations"."arrondissement_city_id" = 56;
 id 
----
(0 rows)

QUERY PLAN

 Index Scan using index_city_locations_on_arrondissement_city_id on city_locations  (cost=0.29..6.31 rows=1 width=8) (actual time=0.028..0.028 rows=0 loops=1)
   Index Cond: (arrondissement_city_id = 56)
   Filter: ((type)::text = 'Arrondissement'::text)
 Planning Time: 0.233 ms
 Execution Time: 0.075 ms

执行时间为:0.075ms，速度超快，结果为NULL。

当我用它的结果 NULL 替换子请求时，它非常快。

EXPLAIN ANALYZE 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 (NULL));

QUERY PLAN

 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))
 Planning Time: 0.395 ms
 Execution Time: 0.412 ms

执行时间为0.412 毫秒

我的问题是为什么第一个请求很慢，而它的单独请求很快？

我是否因为 WHERE IN 子句而错过了一些优化？

Answer 1

第一:简化

---

EXPLAIN ANALYZE 
SELECT AVG(ad.price) 
FROM adverts a
WHERE a.type IN ('Businesses::Restaurant') 
AND a.discarded_at IS NULL
AND a.visible = true 
AND (a.city_location_id = 56 
     OR a.city_location_id IN (
        SELECT c.id
        FROM city_locations c
        WHERE c.type IN ('Arrondissement')                                  
        AND c.arrondissement_city_id = 56))
        ;

---

下一步:将IN(...)改写成EXISTS(...)

---

EXPLAIN ANALYZE
SELECT AVG(a.price)
FROM adverts a
WHERE a.type IN ('Businesses::Restaurant') 
AND a.discarded_at IS NULL
AND a.visible = true 
AND (a.city_location_id = 56 
     OR EXISTS(
        SELECT *
        FROM city_locations c
        WHERE a.city_location_id = c.id 
        AND c.type IN ('Arrondissement')
        AND c.arrondissement_city_id = 56))
        ;

---

现在，您可以将丑陋的 OR 插入子查询:(假设子查询的基数较低)

-> 优化器可能不会足够聪明来将这个OR项下调

---

EXPLAIN ANALYZE
SELECT AVG(a.price)  
FROM adverts a
WHERE a.type IN ('Businesses::Restaurant') 
AND a.discarded_at IS NULL
AND a.visible = true 
AND EXISTS(
        SELECT *
        FROM city_locations c
        WHERE a.city_location_id = c.id
        AND (c.type IN ('Arrondissement') AND c.arrondissement_city_id = 56
            OR c.city_location_id = 56
                )
        ;

---

如果子查询的结果集足够小，您可以尝试将其移动到 CTE。