A CTE RECURSIVA não usa um ÍNDICE. (A desativação do seqscan o força a usar um índice e é mais rápido)

Pode haver várias causas; Não posso ter certeza, porque você não postou a EXPLAIN (ANALYZE, BUFFERS)saída para ambas as execuções.

• O PostgreSQL pode estimar incorretamente as contagens de linhas. Executar ANALYZEcomo você fez é uma boa abordagem aqui, mas em uma CTE recursiva as contagens de linhas geralmente são difíceis de prever e é difícil corrigir essas estimativas.

  Se você não se importa com um truque desagradável, você pode tentar adicionar outra condição de junção supérflua para fazer o PostgreSQL pensar que o resultado terá menos linhas:

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

• O PostgreSQL pode precificar uma varredura de índice muito alta, o que o induz a escolher uma varredura sequencial e uma junção de hash em vez de uma junção de loop aninhado.

  • Se você tiver um SSD como disco, deve diminuir random_page_costpara 1 ou 1.1 para dar ao otimizador do PostgreSQL uma ideia de que as varreduras de índice não são quatro vezes mais caras que as varreduras sequenciais.

  • Se você tiver RAM suficiente, deve definir effective_cache_sizeum valor alto o suficiente para que o PostgreSQL saiba que os dados provavelmente estão armazenados em cache. Isso também reduzirá o custo de uma verificação de índice.

calcular o delta entre um evento e o próximo

por que não usar a função de janela LEAD() ou LAG() que fará o que você deseja. E se puder obter a ordem das linhas de um índice, não precisará fazer nenhuma classificação.

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

Se você quiser a diferença entre uma linha e a anterior, use "value-lag(value,1)" ; também lag() recebe um parâmetro padrão, então se você quiser que o primeiro seja 0 em vez de NULL, use lag(value,1,0::FLOAT). Não parece funcionar se o tipo não for explicitamente convertido.

Agora, a pergunta inicial...

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 remove linhas duplicadas. Como não haverá linhas duplicadas, pois as novas linhas adicionadas por cada consulta recursiva são diferentes das anteriores, isso é um desperdício de CPU, então eu a substituí por UNION ALL, que não faz o trabalho extra. Isso o torna cerca de 2x mais rápido.

 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

Com UNION e UNION ALL, eu tenho praticamente o mesmo plano que o seu, exceto que é muito mais rápido. Então, mesma consulta, mesmo plano, velocidade diferente, isso é estranho.

A grande diferença são seus usos de hash: Lotes: 8 Uso de memória: 2484kB

E o meu usa apenas um lote. Então eu configurei work_mem para 2 MB (estava em 64 MB) e também obtive um hash multi-lote, que era tão lento quanto sua consulta.

Parece que, quando o hash pode ser feito em um lote, o postgres o fará apenas uma vez para toda a consulta, mas se tiver que ser feito em vários lotes, ele o refaz para cada iteração da consulta recursiva, ou seja cerca de 500 vezes. Isso deve explicar por que é lento. Aparentemente, o planejador não está ciente disso, então ele escolhe o hashjoin.

Usar set enable_hashjoin to 'f';antes da consulta faz com que ela use o índice. Isso é muito mais lento do que um hash de 1 bucket feito uma vez e muito mais rápido do que um hash refeito para cada iteração.

Mas realmente, a solução adequada é usar uma função de janela. Ele também irá lidar adequadamente com uma condição como WHERE match_id=...