Início / dba / Perguntas / 341338
Accepted
Megakoresh
Asked: 2024-08-01 01:53:31 +0800 CST

Como usar uma expressão como date_trunc() em GROUP BY?

  • 772

Eu tenho algumas tabelas unidas com dados industriais:

create table v2.tag (
  tag_id integer generated always as identity,
  tag text not null,

  primary key (tag_id),
  unique (tag)
);

create table v2.state (
  tag_id integer not null,
  "timestamp" timestamp without time zone not null,
  value float not null,

  primary key (tag_id, timestamp),
  foreign key (tag_id) references v2.tag (tag_id)
) partition by range (timestamp);

A statetabela contém dados de série temporal de cerca de 50 milhões de linhas dos últimos 6 meses e preciso executar um benchmark com várias consultas. A tabela é particionada mensalmente.

A consulta que tentei simplesmente obtém o número de pontos de dados por dia e tags, que qualquer TSDB real pode fazer sem suar a camisa em um conjunto de dados tão pequeno:

SELECT 
    count(*) as points,date_trunc('day', timestamp) as timestamp,tag.tag 
FROM 
    v2.state 
JOIN 
    v2.tag USING (tag_id) 
GROUP BY 
    timestamp, tag 
ORDER BY 
    timestamp ASC;

O problema é que, por algum motivo, essa consulta faz com que o banco de dados ocupe quase 3 GB de RAM e retorne um monte de duplicatas. Assim:

 2024-02-01 00:00:00 | /Heave         |      1
 2024-02-01 00:00:00 | /Pitch         |      1
 2024-02-01 00:00:00 | /Roll          |      1
 2024-02-01 00:00:00 | /Velocity      |      1
 2024-02-01 00:00:00 | /Heave         |      1
 ...

E assim por diante. Tudo no mesmo dia, não consegui rolar para o próximo, apenas repetia essas linhas no resultado em vez de contá-las por tag como eu esperava. Portanto, em vez de contar o número de pontos de dados por dia/tag, parece apenas produzir uma duplicata para cada linha real dos aproximadamente 50 milhões de linhas no banco de dados.

Então algo não está funcionando na agregação. Eu esperaria que essa consulta retornasse cerca de 12 mil linhas ( 65*30*6), mas, em vez disso, ela retorna milhões de linhas, fazendo com que o notebook Jupyter no qual estou tentando carregá-lo seja eliminado pelo OOM.

Tentei rodar isso com EXPLAIN ANALYZE, mas como sou novato no Postgres, isso realmente não explica nada:

 Sort  (cost=700769.72..700798.22 rows=11400 width=78) (actual time=80503.260..83825.211 rows=47499969 loops=1)
   Sort Key: (date_trunc('day'::text, state."timestamp"))
   Sort Method: external merge  Disk: 4703296kB
   ->  Finalize GroupAggregate  (cost=697027.86..700001.55 rows=11400 width=78) (actual time=35609.801..64328.719 rows=47
499969 loops=1)
         Group Key: state."timestamp", tag.tag
         ->  Gather Merge  (cost=697027.86..699688.05 rows=22800 width=70) (actual time=35609.453..55143.276 rows=4749996
9 loops=1)
               Workers Planned: 2
               Workers Launched: 2
               ->  Sort  (cost=696027.84..696056.34 rows=11400 width=70) (actual time=34526.070..42018.956 rows=15833323 
loops=3)
                     Sort Key: state."timestamp", tag.tag
                     Sort Method: external merge  Disk: 1414088kB
                     Worker 0:  Sort Method: external merge  Disk: 1446832kB
                     Worker 1:  Sort Method: external merge  Disk: 1470664kB
                     ->  Partial HashAggregate  (cost=695145.67..695259.67 rows=11400 width=70) (actual time=8690.289..20
138.661 rows=15833323 loops=3)
                           Group Key: state."timestamp", tag.tag
                           Batches: 1029  Memory Usage: 8241kB  Disk Usage: 1694608kB
                           Worker 0:  Batches: 901  Memory Usage: 8241kB  Disk Usage: 1727928kB
                           Worker 1:  Batches: 773  Memory Usage: 8241kB  Disk Usage: 1748528kB
                           ->  Hash Join  (cost=2.28..652834.40 rows=5641502 width=62) (actual time=138.598..4142.702 row
s=15833323 loops=3)
                                 Hash Cond: (state.tag_id = tag.tag_id)
                                 ->  Parallel Append  (cost=0.00..599769.83 rows=19794743 width=12) (actual time=138.383.
.2665.699 rows=15833323 loops=3)
                                       ->  Parallel Seq Scan on state_y2024m04 state_4  (cost=0.00..221214.31 rows=874583
1 width=12) (actual time=39.308..827.302 rows=6996457 loops=3)
                                       ->  Parallel Seq Scan on state_y2024m02 state_2  (cost=0.00..172317.34 rows=680943
4 width=12) (actual time=58.866..1102.604 rows=8171318 loops=2)
                                       ->  Parallel Seq Scan on state_y2024m03 state_3  (cost=0.00..78305.04 rows=3095204
 width=12) (actual time=0.766..694.493 rows=7428501 loops=1)
                                       ->  Parallel Seq Scan on state_y2024m05 state_5  (cost=0.00..28879.42 rows=1141442
 width=12) (actual time=180.418..416.467 rows=2739461 loops=1)
                                       ->  Parallel Seq Scan on state_y2024m01 state_1  (cost=0.00..20.00 rows=1000 width
=12) (actual time=0.000..0.001 rows=0 loops=1)
                                       ->  Parallel Seq Scan on state_y2024m06 state_6  (cost=0.00..20.00 rows=1000 width
=12) (actual time=0.000..0.001 rows=0 loops=1)
                                       ->  Parallel Seq Scan on state_y2024m07 state_7  (cost=0.00..20.00 rows=1000 width
=12) (actual time=0.000..0.001 rows=0 loops=1)
                                       ->  Parallel Seq Scan on state_y2024m08 state_8  (cost=0.00..20.00 rows=1000 width
=12) (actual time=0.002..0.002 rows=0 loops=1)
                                 ->  Hash  (cost=1.57..1.57 rows=57 width=58) (actual time=0.149..0.268 rows=65 loops=3)
                                       Buckets: 1024  Batches: 1  Memory Usage: 14kB
                                       ->  Seq Scan on tag  (cost=0.00..1.57 rows=57 width=58) (actual time=0.031..0.036 
rows=65 loops=3)
 Planning Time: 2.447 ms
 JIT:
   Functions: 96
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 14.487 ms, Inlining 105.515 ms, Optimization 203.723 ms, Emission 143.355 ms, Total 467.081 ms
 Execution Time: 86164.911 ms

Então, o que há de errado com minha consulta? Por que não está agregando?

postgresql

2 respostas

  1. Best Answer

    Problema imediato

    Sua resposta não define o problema real. Você pode usar "timestamp" como alias. Bem como "dia". (Não significa que seja uma boa ideia.) "timestamp" e "day" são palavras reservadas no SQL padrão. Ambos são menos restritos no Postgres, mas ainda é melhor evitar todas as palavras reservadas como identificadores em qualquer caso. Essa não é a questão imediata, no entanto.

    A questão imediata é esta:

    Se um nome simples GROUP BYcorresponder ao nome da coluna de saída e ao nome da coluna de entrada, ele será interpretado como o nome da coluna de entrada . No seu caso, GROUP BY timestampresolve para GROUP BY state.timestamp. Não é o que você quer. Como isso dificilmente agrupa nenhuma linha, você acaba com milhões de linhas de saída, reduzindo o desempenho como efeito colateral.

    ORDER BYusa a precedência oposta. Tudo isso está definido no padrão SQL, devido a razões históricas, e reflete a sequência de eventos em um SELECTcomando. Ver:

    Solução

    Evite nomes de colunas de saída que colidam com nomes de colunas de entrada para evitar qualquer confusão. Se tais colisões forem inevitáveis ​​(sério?), repita a expressão com base nas colunas de entrada GROUP BYou use referências posicionais. Como:

    SELECT count(*) AS points, date_trunc('day', state.timestamp) AS timestamp, tag.tag 
FROM   v2.state
JOIN   v2.tag USING (tag_id) 
GROUP  BY date_trunc('day', state.timestamp), tag.tag 
--   GROUP BY 2, 3  -- or just this for short
ORDER  BY timestamp;

    Mantendo seu apelido questionável "timestamp" e algumas outras falhas.

    Melhor solução

    SELECT s.points, s.the_day, t.tag
FROM  (
   SELECT count(*) AS points, s.timestamp::date AS the_day, s.tag_id
   FROM   v2.state s
   GROUP  BY 2, 3
   ) s
JOIN   v2.tag t USING (tag_id) 
ORDER  BY s.the_day, t.tag;  -- this deterministic order seems more adequate

    Evite palavras reservadas.
    Agregar antes da junção. Normalmente mais rápido. Ver:

    Use aliases de tabela para manter o nível de ruído baixo.

    Qualifique todas as colunas em consultas onde possam surgir conflitos ou confusão.

    timestamp::dateé equivalente a date_trunc('day', timestamp)um tipo de dados real timestamp, retorna type dateem vez de timestampe é um pouco mais eficiente - principalmente para o índice abaixo.

    Como esta questão é (também) sobre desempenho: se a tabela for principalmente somente leitura e o desempenho desta consulta for importante, considere este índice de expressão de múltiplas colunas correspondente:

    CREATE INDEX my_idx ON ON state ((timestamp::date), tag_id);

    Certifique-se de que a tabela esteja VACUUMeditada o suficiente para fornecer varreduras somente de índice .
    Também espero um ótimo local para date+ integer= 8 bytes. Ver:

    E considere um nome limpo em vez de state.timestamp.

    • 2

  2. Ok, então eu ser um novato no Postgres foi a causa raiz aqui. Por algum motivo, não posso usar timestampcomo alias para um campo. Quando mudei a consulta para ficar assim:

    SELECT 
    count(*) as points,date_trunc('day', timestamp) as day,tag.tag 
FROM 
    v2.state 
JOIN 
    v2.tag USING (tag_id) 
GROUP BY 
    day, tag 
ORDER BY 
    day ASC;

    Foi executado em 3,5 segundos e com os resultados esperados. Ok, dokie, então...

    • 0

relate perguntas