LEFT JOIN lento no CTE com intervalos de tempo
Estou tentando depurar uma consulta no PostgreSQL que criei para reunir dados de mercado em intervalos de tempo emintervalos de tempo arbitrários. Aqui está a minha definição de tabela:
CREATE TABLE historical_ohlcv (
exchange_symbol TEXT NOT NULL,
symbol_id TEXT NOT NULL,
kafka_key TEXT NOT NULL,
open NUMERIC,
high NUMERIC,
low NUMERIC,
close NUMERIC,
volume NUMERIC,
time_open TIMESTAMP WITH TIME ZONE NOT NULL,
time_close TIMESTAMP WITH TIME ZONE,
CONSTRAINT historical_ohlcv_pkey
PRIMARY KEY (exchange_symbol, symbol_id, time_open)
);
CREATE INDEX symbol_id_idx
ON historical_ohlcv (symbol_id);
CREATE INDEX open_close_symbol_id
ON historical_ohlcv (time_open, time_close, exchange_symbol, symbol_id);
CREATE INDEX time_open_idx
ON historical_ohlcv (time_open);
CREATE INDEX time_close_idx
ON historical_ohlcv (time_close);
A tabela tem ~ 25m linhas atualmente. Minha consulta como exemplo por 1 hora, mas pode ser de 5 minutos, 10 minutos, 2 dias etc.
EXPLAIN ANALYZE WITH vals AS (
SELECT
NOW() - '5 months' :: INTERVAL AS frame_start,
NOW() AS frame_end,
INTERVAL '1 hour' AS t_interval
)
, grid AS (
SELECT
start_time,
lead(start_time, 1)
OVER (
ORDER BY start_time ) AS end_time
FROM (
SELECT
generate_series(frame_start, frame_end,
t_interval) AS start_time,
frame_end
FROM vals
) AS x
)
SELECT max(high)
FROM grid g
LEFT JOIN historical_ohlcv ohlcv ON ohlcv.time_open >= g.start_time
WHERE exchange_symbol = 'BINANCE'
AND symbol_id = 'ETHBTC'
GROUP BY start_time;
A cláusula WHERE pode ter qualquer valor válido na tabela.
Essa técnica foi inspirada em:
A melhor maneira de contar registros por intervalos de tempo arbitrários no Rails + Postgres.A idéia é criar uma tabela comum e juntar os dados à esquerda para indicar em que itens do bucket está. Esta consulta é realmente lenta! Atualmente leva 15s. Com base no planejador de consultas, temos um loop aninhado realmente caro:
QUERY PLAN
HashAggregate (cost=2758432.05..2758434.05 rows=200 width=40) (actual time=16023.713..16023.817 rows=542 loops=1)
Group Key: g.start_time
CTE vals
-> Result (cost=0.00..0.02 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=1)
CTE grid
-> WindowAgg (cost=64.86..82.36 rows=1000 width=16) (actual time=2.986..9.594 rows=3625 loops=1)
-> Sort (cost=64.86..67.36 rows=1000 width=8) (actual time=2.981..4.014 rows=3625 loops=1)
Sort Key: x.start_time
Sort Method: quicksort Memory: 266kB
-> Subquery Scan on x (cost=0.00..15.03 rows=1000 width=8) (actual time=0.014..1.991 rows=3625 loops=1)
-> ProjectSet (cost=0.00..5.03 rows=1000 width=16) (actual time=0.013..1.048 rows=3625 loops=1)
-> CTE Scan on vals (cost=0.00..0.02 rows=1 width=32) (actual time=0.008..0.009 rows=1 loops=1)
-> Nested Loop (cost=0.56..2694021.34 rows=12865667 width=14) (actual time=7051.730..16015.873 rows=31978 loops=1)
-> CTE Scan on grid g (cost=0.00..20.00 rows=1000 width=16) (actual time=2.988..11.635 rows=3625 loops=1)
-> Index Scan using historical_ohlcv_pkey on historical_ohlcv ohlcv (cost=0.56..2565.34 rows=12866 width=22) (actual time=3.712..4.413 rows=9 loops=3625)
Index Cond: ((exchange_symbol = 'BINANCE'::text) AND (symbol_id = 'ETHBTC'::text) AND (time_open >= g.start_time))
Filter: (time_close < g.end_time)
Rows Removed by Filter: 15502
Planning time: 0.568 ms
Execution time: 16023.979 ms
Meu palpite é que esta linha está fazendo muito:
LEFT JOIN historical_ohlcv ohlcv ON ohlcv.time_open >= g.start_time
AND ohlcv.time_close < g.end_time
Mas não tenho certeza de como fazer isso de outra maneira.
P.S. desculpas se isso pertence ao dba.SE. Eu li o FAQ e isso parecia básico demais para o site, então eu postei aqui.
Edita conforme solicitado:
SELECT avg(pg_column_size(t)) FROM historical_ohlcv t TABLESAMPLE SYSTEM (0.1);
retorna 107.632
Paraexchange_symbol
, existem 3 valores exclusivos, parasymbol_id
existem ~ 400
Versão do PostgreSQL: PostgreSQL 10.3 (Ubuntu 10.3-1.pgdg16.04 + 1) no x86_64-pc-linux-gnu, compilado pelo gcc (Ubuntu 5.4.0-6ubuntu1 ~ 16.04.9) 5.4.0 20160609, 64 bits.
A tabela crescerá cerca de ~ 1 milhão de registros por dia, portanto, não é exatamente somente leitura. Tudo isso é feito localmente e tentarei mudar para o RDS ou para ajudar a gerenciar problemas de hardware.
Relacionado: se eu quisesse adicionar outros agregados, especificamente 'primeiro no intervalo', 'último no intervalo', min, soma, minha estratégia de indexação mudaria?