主页 / dba / 问题 / 251300
Accepted
Tiago Stapenhorst Martins
Asked: 2019-10-17 14:25:52 +0800 CST

如何有效地获取 PostgreSQL 表中每个 id 的最旧值？

  • 772

PostgreSQL 如何在传感器 id 测量表上返回最旧时间戳值的列表？

让我用一个示例表来解释一下情况：

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

填充表示例：

+-----------+------------------+-------+
| 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 |
+-----------+------------------+-------+

在查询中使用类似 sensor_id (1,3) 的内容时，我希望它返回如下内容：

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

如何在查询中使用PRIMARY KEY索引来加快速度？

postgresql performance

2 个回答

  1. Best Answer

    有许多可能的查询样式，大多数都会很容易地使用您的 PK 索引，(sensor_id, time)因为它适合任务。（Postgres 几乎可以以同样快的速度向后读取索引。）这应该接近完美：

    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<>在这里摆弄

    LEFT JOIN .. ON true使传感器在结果中没有任何数据条目 - 用 NULL 值代替值。

    有关的：

    由于您使用的是 Postgres 11，因此覆盖索引可能会支付：

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

    但是它使索引更大并且写入表的成本更高，并且您的名字表明一个写入繁重的表。虽然您一次只查询几行，但无论如何查询都不会变得更快。所以可能是你拥有它的最佳方式。有关的：

    • 2

  2. 仅出于教育目的，这是解决它的另一种方法：

    (
    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
);

    此查询的缺点是需要动态生成查询。

    从这个意义上说，欧文的解决方案要合适得多。

    • 0

相关问题