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?