Início / dba / Perguntas / 231957
Accepted
raphael
Asked: 2019-03-13 12:03:24 +0800 CST

Otimização de índice para Datetime comparando dia da semana e hora

  • 772

Tenho uma tabela de observações de sensores com obs_ts timestamp, sensor_id text, sensor_val int, e para preencher lacunas com dados temos modelos por dia da semana e hora do dia: model_id int, hour_of_day int, model_val int.

Para preencher os valores ausentes, juntaríamos essas duas tabelas por meio de uma tabela de cruzamento que é:sensor_id text, day_of_week int, model_id int

Se nossa tabela de observações for enorme, qual seria uma maneira ideal de indexá-la para unir isodowe hour. A indexação de um carimbo de data/hora também indexa funções como EXTRACT(isodow FROM obs_ts)ou devo tornar esses índices funcionais explícitos, por exemplo CREATE INDEX ON observations (EXTRACT isodow FROM obs_tx). Para ingressar na hora, seria melhor converter o hour_of_daypara um timerange?

postgresql index-tuning

1 respostas

  1. Best Answer

    Por sugestão de Verace, criei uma tabela de teste com 10 milhões de registros. O TLDR: Índices em funções de data e hora precisam ser explícitos, qualquer conversão dessa coluna de data e hora para junção/filtragem não será atendida por um índice.

    SELECT obs_ts AS weekday_indexed, obs_ts AS hour_indexed , * 
INTO index_test
FROM observations
LIMIT 10000000;

CREATE INDEX ON index_test(EXTRACT('isodow' from weekday_indexed));
CREATE INDEX ON index_test(EXTRACT('hour' FROM hour_indexed));
CREATE INDEX ON index_test(obs_ts);
ANALYSE index_test;

    O tamanho da tabela é de 1,7 GB. Todos os índices são 214 MB. Dos meus testes, o índice obs_tsnunca é usado (provavelmente porque essas comparações envolvem a alteração do tipo de dados da coluna timestamp...). Os índices específicos da função são usados, portanto, há definitivamente uma troca de tamanho de índice de desempenho.

    Dia da semana

    SELECT COUNT(1)
FROM index_test
WHERE extract('isodow' from obs_ts )=5
--3 secs 276 msec.

SELECT COUNT(1)
FROM index_test
WHERE extract('isodow' from weekday_indexed)=5
--1 secs 586 msec.

    Hora do dia

    SELECT COUNT(1)
FROM index_test
WHERE extract('hour' from obs_ts )=5
-- Total query runtime: 2 secs 420 msec.

EXPLAIN ANALYZE SELECT COUNT(1)
FROM index_test
WHERE obs_ts::TIME >= '05:00' AND obs_ts::TIME < '06:00'
-- Total query runtime: 2 secs 391 msec.

SELECT COUNT(1)
FROM index_test
WHERE tx::TIME @> timerange('05:00', '06:00')
-- Total query runtime: 1 secs 928 msec.

SELECT COUNT(1)
FROM index_test
WHERE extract('hour' from hour_indexed)=5
-- 779 msec.
    • 0

relate perguntas