sql - 为什么添加一个窗口函数会使这个查询变得如此缓慢？

Question

查询 A 在微秒内执行:

SELECT t1.id
 FROM (SELECT t0.id AS id FROM t0) AS t1
 WHERE NOT (EXISTS (SELECT 1
        FROM t2
        WHERE t2.ph_id = t1.id AND t2.me_id = 1 AND t2.rt_id = 4))
 LIMIT 20 OFFSET 0

但是查询 B 大约需要 25 秒:

SELECT t1.id, count(*) OVER () AS count
 FROM (SELECT t0.id AS id FROM t0) AS t1
 WHERE NOT (EXISTS (SELECT 1
        FROM t2
        WHERE t2.ph_id = t1.id AND t2.me_id = 1 AND t2.rt_id = 4))
 LIMIT 20 OFFSET 0

(不同之处仅在于 select 子句中的一项 - 窗口聚合)​​

EXPLAIN 输出如下，对于 A:

 Limit  (cost=0.00..1.20 rows=20 width=4)
   ->  Nested Loop Anti Join  (cost=0.00..3449.22 rows=57287 width=4)
         Join Filter: (t2.ph_id = t0.id)
         ->  Seq Scan on t0  (cost=0.00..1323.88 rows=57288 width=4)
         ->  Materialize  (cost=0.00..1266.02 rows=1 width=4)
               ->  Seq Scan on t2  (cost=0.00..1266.01 rows=1 width=4)
                     Filter: ((me_id = 1) AND (rt_id = 4))

对于 B:

 Limit  (cost=0.00..1.45 rows=20 width=4)
   ->  WindowAgg  (cost=0.00..4165.31 rows=57287 width=4)
         ->  Nested Loop Anti Join  (cost=0.00..3449.22 rows=57287 width=4)
               Join Filter: (t2.ph_id = t0.id)
               ->  Seq Scan on t0  (cost=0.00..1323.88 rows=57288 width=4)
               ->  Materialize  (cost=0.00..1266.02 rows=1 width=4)
                     ->  Seq Scan on t2  (cost=0.00..1266.01 rows=1 width=4)
                           Filter: ((me_id = 1) AND (rt_id = 4))

为了构建分页 UI，我正在添加窗口聚合以获取 LIMITing 之前的总行数。

Answer 1

你原来的查询可以这样写:

SELECT t0.id
FROM t0
WHERE NOT EXISTS (SELECT 1
                  FROM t2
                  WHERE t2.ph_id = t1.id AND t2.me_id = 1 AND t2.rt_id = 4
                 )
LIMIT 20 OFFSET 0;

您没有order by，因此查询可以在为结果集找到结果时开始返回结果。添加窗口函数时:

SELECT t.0.id, count(*) over ()

现在它正在计算结果集中的行数，所以它必须生成整个结果集。因此，查询必须生成所有这些行，而不是只获取前 20 行。这需要更多时间。