Início / dba / Perguntas / 251300
Accepted
Tiago Stapenhorst Martins
Asked: 2019-10-17 14:25:52 +0800 CST

Como obter os valores mais antigos de cada id em uma tabela do PostgreSQL de forma eficiente?

  • 772

Como o PostgreSQL pode retornar uma lista dos valores de carimbo de data/hora mais antigos em uma tabela de medições de identificação do sensor?

Deixe-me explicar a situação com uma tabela de exemplo:

CREATE TABLE sensor_data(
sensor_id INTEGER,
time TIMESTAMPTZ,
value NUMERIC,
PRIMARY KEY (sensor_id, time)
)

Exemplo de tabela preenchida:

+-----------+------------------+-------+
| sensor_id |       time       | value |
+-----------+------------------+-------+
|         1 | 2018-01-01 00:00 |     1 |
|         1 | 2018-01-01 01:00 |     2 |
|         3 | 2018-01-01 03:00 |     4 |
|         3 | 2018-01-01 04:00 |     3 |
|         4 | 2018-01-01 03:00 |     5 |
|         4 | 2018-01-01 04:00 |     6 |
+-----------+------------------+-------+

Ao usar algo como sensor_id (1,3) dentro da consulta, quero que ele retorne algo assim:

+-----------+------------------+-------+
| sensor_id |       time       | value |
+-----------+------------------+-------+
|         1 | 2018-01-01 01:00 |     2 |
|         3 | 2018-01-01 04:00 |     3 |
+-----------+------------------+-------+

Como posso fazer isso em uma consulta usando o PRIMARY KEYíndice para acelerá-la?

postgresql performance

2 respostas

  1. Best Answer

    Existem muitos estilos de consulta possíveis, a maioria usará prontamente seu índice PK (sensor_id, time)conforme ele se encaixa na tarefa. (O Postgres pode ler índices de trás para frente praticamente tão rápido.) Isso deve ser quase perfeito:

    SELECT s.sensor_id, sd.time, sd.value
FROM   unnest ('{1,3}'::int[]) s(sensor_id)
LEFT   JOIN LATERAL (
   SELECT *
   FROM   sensor_data sd
   WHERE  sd.sensor_id = s.sensor_id
   ORDER  BY time DESC
   LIMIT  1
   ) sd ON true;

    db<>fique aqui

    LEFT JOIN .. ON truemantém os sensores sem nenhuma entrada de dados no resultado - com valores NULL no lugar de valores.

    Relacionado:

    Como você está no Postgres 11, um índice de cobertura pode pagar:

    ... PRIMARY KEY (sensor_id, time) INCLUDE (value)

    Mas torna o índice maior e grava na tabela mais caro, e seus nomes indicam uma tabela de gravação pesada. E enquanto você consulta apenas algumas linhas de cada vez, as consultas não ficam muito mais rápidas de qualquer maneira. Então provavelmente melhor do jeito que você tem. Relacionado:

    • 2

  2. Apenas para fins educacionais, aqui está outra maneira de resolvê-lo:

    (
    SELECT sensor_id, time, value
    FROM sensor_data
    WHERE sensor_id = 1
    LIMIT 1
)
UNION ALL
(
    SELECT sensor_id, time, value
    FROM sensor_data
    WHERE sensor_id = 3
    LIMIT 1
);

    A desvantagem desta consulta é a necessidade de gerar consultas dinamicamente.

    A solução de Erwin é muito mais apropriada nesse sentido.

    • 0

relate perguntas