Tenho uma tabela grande com medições de sensores que tem esta estrutura:
create table measurements (
sensor_id varchar(50) not null,
timestamp timestamptz not null,
value decimal(10, 6) not null,
primary key (sensor_id, timestamp)
)
Ele contém medições de sensores (pluvômetros, mas isso não importa muito) tiradas em intervalos de 5 minutos. Os valores do sensor podem ser 0 ou positivos, mas não negativos. Atualmente, o conjunto de dados remonta a um pouco mais de um ano, mas deve ser capaz de lidar com anos de dados.
Quero recuperar as medições mais recentes por sensor para análise posterior, de modo que eu obtenha pelo menos duas semanas de medições, e o conjunto de medições deve conter pelo menos 100 valores diferentes de zero. A consulta que usei é esta:
select *
from (
select sensor_id, timestamp, "value",
sum(cast("value" > 0 as INT)) over w as cum_nonzero_measurements,
row_number() over w as cum_measurements,
first_value(timestamp) over w - timestamp as age
from measurements
window w as (partition by sensor_id order by timestamp desc)
) windowed
where (cum_nonzero_measurements <= 100 or age < interval '2 weeks' )
and sensor_id in ($1)
order by sensor_id, timestamp desc
Esta consulta precisa apenas das medições N mais recentes para um sensor, embora N possa variar. A maneira inteligente de executar tal consulta seria começar a ler a partir do valor mais recente, voltando no tempo. No entanto, o Postgresql não percebe que a consulta pode ser executada dessa forma e insiste em carregar todas as medições para os s fornecidos sensor_id, fazendo uma agregação de janela sobre todas as linhas e, somente então, filtrando a maioria das linhas para obter o resultado.
Eu tentei com índices diferentes, o que ajuda a recuperar todas as linhas para os sensores solicitados mais rapidamente, mas o que quer que eu faça, o Postgres continua carregando todas as linhas para os sensores em questão. Atualmente, o desempenho é aceitável, mas isso não escala muito bem se o conjunto de dados cresce.
Existe alguma maneira de convencer o Postgres de que ele não precisa carregar todas as linhas, apenas as mais recentes?