主页 / user-222914

Antonio L.'s questions

Martin Hope
Antonio L.
Asked: 2021-01-29 18:50:42 +0800 CST
  • 0

假设以下关系：

  • 匹配（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