如何有效地獲取 PostgreSQL 表中每個 id 的最舊值？

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索引來加快速度？

有許多可能的查詢樣式，大多數都會很容易地使用您的 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 值代替值。

有關的：

• 優化 GROUP BY 查詢以檢索每個使用者的最新行
• LATERAL 和 PostgreSQL 中的子查詢有什麼區別？

由於您使用的是 Postgres 11，因此覆蓋索引可能會支付：

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

但是它使索引更大並且寫入表的成本更高，並且您的名字表明一個寫入繁重的表。雖然您一次只查詢幾行，但無論如何查詢都不會變得更快。所以可能是你擁有它的最佳方式。有關的：

• 具有主鍵和外鍵的查詢是否比僅具有主鍵的查詢執行得更快？

引用自：https://dba.stackexchange.com/questions/251300