多传感器时序观测表查询优化：如何高效获取单传感器最新观测记录

原有方案的性能瓶颈说明 #

当前使用的DISTINCT ON语法本身逻辑严谨、结果正确，但在大表场景下查询开销高的核心原因是：当没有匹配的索引支撑时，数据库需要对全量数据先按sensor、time DESC做全局排序，再从排序结果中截取每个传感器的首条记录。这个排序操作会占用大量内存和磁盘IO，当表行数达到千万、亿级时，查询延迟会从毫秒级上涨到秒级甚至分钟级。
这个优化过程完全不需要拆分原表结构、搭建冗余的最新观测表，仅通过匹配的覆盖索引即可把查询开销压到最低。

创建匹配查询逻辑的覆盖索引 #

索引的排序规则必须和查询的ORDER BY顺序、方向完全对齐，同时将查询需要返回的字段全部纳入索引，实现无回表的索引-only扫描，建索引SQL如下：

CREATE INDEX idx_observations_sensor_time_covering 
ON observations (sensor ASC, time DESC)
INCLUDE (value);

索引的性能优势 #

• 索引本身的物理存储结构就是先按sensor字段升序聚类，同一个传感器的所有记录连续存储，且同一传感器下的记录严格按照time从新到旧排列
• 数据库执行查询时，只需要遍历索引结构，每个传感器碰到的第一行就是该传感器最新的观测记录，直接从索引上就能读取到需要的value字段，不需要回表查询原数据表
• 实际执行时的扫描成本近似等于「总传感器数量」，哪怕原表有上亿行数据，查询也可以实现毫秒级返回

验证优化效果 #

加完索引后，原来的DISTINCT ON查询不需要做任何修改，直接执行即可。可以通过EXPLAIN ANALYZE查看执行计划，确认走了索引-only扫描：

EXPLAIN ANALYZE
SELECT DISTINCT ON (sensor) sensor, time, value
FROM observations
ORDER BY sensor, time DESC;

如果执行计划中显示Index Only Scan using idx_observations_sensor_time_covering，就说明优化完全生效。
如果需要跨数据库兼容的标准SQL写法，也可以换为逻辑等价的关联查询，在同样的索引支撑下可以达到和DISTINCT ON几乎一致的性能：

SELECT o.sensor, o.time, o.value
FROM observations o
INNER JOIN (
    -- 直接通过索引拿到每个传感器的最大观测时间，无需全局排序
    SELECT sensor, MAX(time) AS latest_time
    FROM observations
    GROUP BY sensor
) latest
ON o.sensor = latest.sensor 
AND o.time = latest.latest_time;

方案对比 #

和拆分独立最新观测表的冗余方案相比，单表加索引的方案有明确优势：

• 不需要额外维护冗余表的同步逻辑（包括插入触发、并发更新冲突处理、定期数据一致性校验等）
• 索引的维护开销和普通二级索引完全一致，远低于冗余表的同步开销
• 不会出现冗余表和原表数据不一致的问题，始终读取的是原始观测数据

内容的提问来源于stack exchange，提问作者sob