Início / user-222914

Antonio L.'s questions

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

Suponha as seguintes relações:

  • match(match_id)
  • event(match_id, seq, gt, ...)

Existem os seguintes índices:

  • match(match_id)
  • event(match_id, seq)

Notas adicionais:

  • gt está aumentando monotonicamente
  • Para uma determinada partida, tenho uma coleção de eventos que acontecem em um horário 'gt' específico
  • tanto a partida quanto o evento são visualizações de tapete.
  • Item da lista

Estou usando o postgresql 13.1

Meu objetivo é criar uma consulta CTE RECURSIVA que calcule o delta entre um evento e o próximo, porém acho isso muito lento. Embora isso possa ser resolvido praticamente com uma auto-junção, não estou interessado nisso, quero descobrir por que meu CTE está lento. Acredito que não deve ser tão lento.

Mais números:

  • número de partidas é 400
  • cada partida tem uma média de 541 eventos

Minha consulta CTE RECURSIVA é a seguinte:


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

Outras notas que também tentei adicionando o seguinte (apenas para uma partida):

WHERE g.match_id = 'ita_1672780'

e descubro no plano que não há pushdown de predicado. Eu acho que isso foi implementado no pgsql 13.1

Este é o plano:

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

Considerações:

  • Ele não está usando o índice (match_id, seq) na tabela de eventos quando a parte recursiva do CTE é executada.
  • Desabilitar o seqscan faz o truque, pois usará o índice para eventos.

Após alguma investigação, parece que o problema é que um SeqScan está sendo executado para procurar o próximo evento que não está correto na minha situação.

postgresql postgresql-13