Início / dba / Perguntas / 343852
Accepted
JanKanis
Asked: 2024-11-26 22:34:56 +0800 CST

Como posso dizer ao Postgres para escanear apenas os valores mais recentes nesta consulta?

  • 772

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?

postgresql

1 respostas

  1. Best Answer

    É o clássico problema "Top-N por categoria", com uma pequena reviravolta na trama.

    A solução mais rápida provavelmente será uma junção lateral. Isso permite executar uma junção com uma subconsulta dependente.

    Se você tiver uma tabela separada listando os sensor_ids, é muito simples. A ideia é exatamente o que você escreveu na sua pergunta: para cada sensor_id, comece a ler do valor mais recente indo para trás no tempo.

    Aqui está um caso de teste rápido que obtém os 100 valores mais recentes para todos os sensores:

    create unlogged table measurements (
  sensor_id    int       not null,
  timestamp    float       not null,
  value        decimal(10, 6)   not null,
  primary key (sensor_id, timestamp)
);

insert into measurements 
select random()*100, n, case when random()>0.5 then random() else 0 end 
from generate_series( 1, 1000000 ) n;

vacuum analyze measurements;

CREATE UNLOGGED TABLE sensors AS SELECT sensor_id FROM measurements GROUP BY sensor_id;

EXPLAIN ANALYZE SELECT *
FROM sensors s
CROSS JOIN LATERAL (SELECT * FROM measurements m WHERE m.sensor_id = s.sensor_id ORDER BY timestamp DESC LIMIT 200) top;

    Ele faz um loop aninhado em sensores e, em seguida, indexa a varredura para trás em medições. Para obter apenas sensores específicos:

    SELECT *
FROM (VALUES (1),(2),(3)) s
CROSS JOIN LATERAL (SELECT * FROM measurements m WHERE m.sensor_id = s.column1 ORDER BY timestamp DESC LIMIT 100) top;

    Ao contrário de uma junção regular, a expressão de tabela na junção lateral depende de s.column1 de tabelas anteriores. Ela é reavaliada para cada linha de VALUES() e o limite é aplicado. É isso que faz funcionar. Sem LATERAL, a expressão de tabela na junção seria aplicada apenas uma vez, então o LIMIT seria aplicado apenas uma vez, em vez de por sensor, que é o que você quer.

    Em vez de VALUES(), você também pode colocar os sensor_id's em uma matriz e unnest().

    No entanto, isso só dá os 100 valores mais recentes, o que não é o que você quer! Aqui vai uma sugestão:

    SELECT *
FROM (VALUES (1),(2),(3)) s
CROSS JOIN LATERAL (SELECT * FROM measurements m WHERE m.sensor_id = s.column1 AND value>0 ORDER BY timestamp DESC LIMIT 1 OFFSET 99) first
CROSS JOIN LATERAL (SELECT * FROM measurements m WHERE m.sensor_id = s.column1 AND timestamp >= first.timestamp ORDER BY timestamp DESC LIMIT 200) top;

    A primeira junção lateral tem "valor>0" na condição WHERE, então ela considera apenas essas linhas. Graças ao limite/deslocamento, ela pula 99 linhas indo para trás no tempo, então pega o timestamp para essa linha, que é usado como um ponto de partida para a segunda junção lateral que simplesmente retorna todas as linhas posteriores a esse timestamp.

    Em ambos os casos, ele usará o índice, portanto não deverá ler linhas muito antigas e, portanto, não deverá ficar lento à medida que o tamanho da tabela aumenta.

    • 3

relate perguntas