主页 / dba / 问题 / 284332
Accepted
Antonio L.
Asked: 2021-01-29 18:50:42 +0800 CST

RECURSIVE CTE 不使用索引。（禁用 seqscan 会强制它使用索引，但是速度更快）

  • 772

假设以下关系：

  • 匹配（match_id）
  • 事件（match_id，seq，gt，...）

有以下指标：

  • 匹配（match_id）
  • 事件（match_id，seq）

进一步说明：

  • gt 单调递增
  • 对于给定的比赛，我有一组在特定“gt”时间发生的事件
  • match 和 event 都是 mat 视图。
  • 项目清单

我正在使用 postgresql 13.1

我的目标是提出一个 RECURSIVE CTE 查询来计算一个事件和下一个事件之间的增量，但是我发现这非常慢。虽然这实际上可以通过自加入来解决，但我对此不感兴趣，我想找出为什么我的 CTE 很慢。我相信它不应该那么慢。

更多数字：

  • 匹配数为 400
  • 每场比赛平均有 541 个事件

我的递归 CTE 查询如下：


WITH RECURSIVE
    delta_gts AS (
        SELECT m.match_id, 1 AS seq, 0 AS gt, 0 AS delta
        FROM matches m

        UNION

        SELECT dgt.match_id, ev.seq AS seq, ev.gt AS gt, (ev.gt - dgt.gt) AS delta
        FROM delta_gts dgt
        JOIN events ev ON ev.match_id = dgt.match_id AND ev.seq = (dgt.seq + 1)

    )

SELECT * FROM delta_gts g

进一步说明我还尝试添加以下内容（仅限一场比赛）：

WHERE g.match_id = 'ita_1672780'

我在计划中发现没有谓词下推。我认为这是在 pgsql 13.1 中实现的

这是计划：

QUERY PLAN
CTE Scan on delta_gts g  (cost=160601.44..161032.40 rows=21548 width=76) (actual time=173.940..354185.831 rows=220268 loops=1)
"  Buffers: shared hit=5453034 read=596370, temp read=1340253 written=1581611"
  CTE delta_gts
    ->  Recursive Union  (cost=0.00..160601.44 rows=21548 width=76) (actual time=173.931..353944.926 rows=220268 loops=1)
"          Buffers: shared hit=5453034 read=596370, temp read=1340253 written=1580590"
          ->  Seq Scan on netcastingdocument_matches m  (cost=0.00..10.08 rows=408 width=28) (actual time=173.917..174.265 rows=408 loops=1)
                Buffers: shared hit=6
          ->  Hash Join  (cost=14121.22..16016.04 rows=2114 width=76) (actual time=259.550..305.356 rows=190 loops=1158)
                Hash Cond: ((dgt.match_id = ev.match_id) AND ((dgt.seq + 1) = ev.seq))
"                Buffers: shared hit=5453028 read=596370, temp read=1340253 written=1580590"
                ->  WorkTable Scan on delta_gts dgt  (cost=0.00..81.60 rows=4080 width=72) (actual time=0.005..0.067 rows=190 loops=1158)
                ->  Hash  (cost=8106.89..8106.89 rows=288289 width=24) (actual time=257.949..257.949 rows=288323 loops=1158)
                      Buckets: 65536  Batches: 8  Memory Usage: 2484kB
"                      Buffers: shared hit=5453022 read=596370, temp written=1565616"
                      ->  Seq Scan on netcastingdocument_events ev  (cost=0.00..8106.89 rows=288289 width=24) (actual time=0.016..92.171 rows=288323 loops=1158)
                            Buffers: shared hit=5453022 read=596370
Planning:
  Buffers: shared hit=107
Planning Time: 50.290 ms
JIT:
  Functions: 13
"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
"  Timing: Generation 4.108 ms, Inlining 0.000 ms, Optimization 19.158 ms, Emission 154.531 ms, Total 177.796 ms"
Execution Time: 355489.930 ms

注意事项：

  • 当执行 CTE 的递归部分时，它根本没有使用事件表上的索引 (match_id, seq)。
  • 禁用 seqscan 可以解决问题，因为它将使用事件索引。

经过一番调查，问题似乎是正在执行 SeqScan 以查找下一个在我的情况下不正确的事件。

postgresql postgresql-13

2 个回答

  1. Best Answer

    可能有几个原因；我不能确定，因为您没有发布EXPLAIN (ANALYZE, BUFFERS)两次执行的输出。

    • PostgreSQL 可能会错误估计行数。在这里像你一样运行ANALYZE是一种很好的方法，但在递归 CTE 中，行数通常很难预测，而且很难修复这些估计值。

      如果你不介意一个讨厌的技巧，你可以尝试添加另一个多余的连接条件，让 PostgreSQL 认为结果将有更少的行：

      JOIN events ev
   ON ev.match_id = dgt.match_id
      AND ev.seq = dgt.seq + 1
      AND ev.seq - 1 = dgt.seq

    • PostgreSQL 可能对索引扫描定价过高，这导致它选择顺序扫描和散列连接而不是嵌套循环连接。

      • 如果你有一个 SSD 作为磁盘，你应该降低random_page_cost到 1 或 1.1，让 PostgreSQL 优化器知道索引扫描的成本不是顺序扫描的四倍。

      • 如果你有足够的 RAM，你应该设置effective_cache_size足够高，以便 PostgreSQL 知道数据可能被缓存了。这也将降低索引扫描的成本。

    • 2

  2. 计算一个事件和下一个事件之间的增量

    为什么不使用窗口函数 LEAD() 或 LAG() 来做你想做的事。如果它可以从索引中获取行的顺序，它就不需要进行任何排序。

    BEGIN;
CREATE TABLE events( match_id INTEGER NOT NULL, seq INTEGER NOT NULL, value FLOAT );
INSERT INTO events SELECT n/500, n%500, random() FROM generate_series(1,500*500) n;
ALTER TABLE events ADD PRIMARY KEY (match_id, seq);
CREATE TABLE matches( match_id INTEGER PRIMARY KEY );
INSERT INTO matches SELECT DISTINCT match_id FROM events;
COMMIT;
VACUUM ANALYZE matches, events;

EXPLAIN ANALYZE SELECT match_id, seq, value, 
    lag(value,1,0::FLOAT) OVER (PARTITION BY match_id ORDER BY seq) 
    FROM events;

 WindowAgg  (cost=0.42..14009.61 rows=250000 width=24) (actual time=0.037..371.799 rows=250000 loops=1)
   ->  Index Scan using events_pkey on events  (cost=0.42..9634.61 rows=250000 width=16) (actual time=0.024..98.620 rows=250000 loops=1)
 Planning Time: 0.090 ms
 Execution Time: 390.870 ms

    如果您想要一行和前一行之间的差异，请使用 "value-lag(value,1)" ；lag() 也采用默认参数，因此如果您希望第一个参数为 0 而不是 NULL，请使用 lag(value,1,0::FLOAT)。如果类型没有显式转换，它似乎不起作用。

    现在，原来的问题...

    WITH RECURSIVE
    delta_gts AS (
        SELECT m.match_id, 1 AS seq, 0::FLOAT AS value, 0::FLOAT AS delta FROM matches m
        UNION ALL
        SELECT dgt.match_id, ev.seq AS seq, ev.value, (ev.value - dgt.value) AS delta
        FROM delta_gts dgt
        JOIN events ev ON ev.match_id = dgt.match_id AND ev.seq = (dgt.seq + 1)
    )
SELECT * FROM delta_gts g;

    UNION 删除重复的行。由于不会有重复的行，因为每个递归查询添加的新行都与之前的不同，这很浪费CPU，所以我将它替换为UNION ALL，它不会做额外的工作。这使它快了大约 2 倍。

     CTE Scan on delta_gts g  (cost=78436.57..79448.59 rows=50601 width=24) (actual time=0.019..715.390 rows=249501 loops=1)
   CTE delta_gts
     ->  Recursive Union  (cost=0.00..78436.57 rows=50601 width=24) (actual time=0.016..437.205 rows=249501 loops=1)
           ->  Seq Scan on matches m  (cost=0.00..8.01 rows=501 width=24) (actual time=0.014..0.133 rows=501 loops=1)
           ->  Hash Join  (cost=7602.00..7741.65 rows=5010 width=24) (actual time=0.294..0.733 rows=499 loops=499)
                 Hash Cond: ((dgt.match_id = ev.match_id) AND ((dgt.seq + 1) = ev.seq))
                 ->  WorkTable Scan on delta_gts dgt  (cost=0.00..100.20 rows=5010 width=16) (actual time=0.000..0.061 rows=500 loops=499)
                 ->  Hash  (cost=3852.00..3852.00 rows=250000 width=16) (actual time=145.065..145.066 rows=250000 loops=1)
                       Buckets: 262144  Batches: 1  Memory Usage: 14744kB
                       ->  Seq Scan on events ev  (cost=0.00..3852.00 rows=250000 width=16) (actual time=0.012..59.861 rows=250000 loops=1)
 Planning Time: 0.278 ms
 Execution Time: 745.422 ms

    使用 UNION 和 UNION ALL，我的计划与您的计划几乎相同，只是速度要快得多。所以，同样的查询，同样的计划，不同的速度，这很奇怪。

    最大的区别是您的哈希使用：批次：8 内存使用量：2484kB

    而我的只用了一批。所以我将 work_mem 设置为 2MB（它是 64MB）并且还得到了一个多批次散列，它和你的查询一样慢。

    看来，当哈希可以在一批中完成时，postgres 只会对整个查询执行一次，但如果必须分几批完成，它将为递归查询的每次迭代重做一次，这意味着约500次。这应该可以解释为什么它很慢。显然计划者没有意识到这一点，所以它选择了 hashjoin。

    在查询之前使用set enable_hashjoin to 'f';使其使用索引。这比一次完成一次 1 桶哈希要慢得多，而且比每次迭代都重新完成一次哈希要快得多。

    但实际上，正确的解决方案是使用窗口函数。它还将正确处理 WHERE match_id=... 之类的条件

    • 0

相关问题