PostgreSQL：如何高效获取GROUP BY子句外的关联列transmission_tag

场景说明 #

现有存储设备遥测数据的telemetry表，结构如下：

• transmission_tag varchar -- 传输包标识
• equipment_id int
• measurement int -- 测量值
• uts int -- 测量时间的Unix时间戳

已知(equipment_id, uts)组合唯一，且该字段组合建有索引。此前通过以下查询可高效获取每台设备的最新时间戳：

SELECT equipment_id, max(uts)
FROM telemetry
GROUP BY equipment_id

但需要获取对应记录的transmission_tag时，拼接字段匹配的方法会触发全表扫描，耗时极长：

SELECT transmission_tag
FROM telemetry
WHERE CAST(equipment_id as VARCHAR) || '_' || CAST(uts as VARCHAR) IN
 (SELECT CAST(equipment_id as VARCHAR) || '_' || CAST(max(uts) as VARCHAR)
  FROM telemetry
  GROUP BY equipment_id)

高效解决方案 #

方案1：JOIN关联子查询 #

利用已有的高效分组子查询，通过equipment_id和uts关联原表，直接命中(equipment_id, uts)索引：

SELECT t.transmission_tag
FROM telemetry t
JOIN (
    SELECT equipment_id, max(uts) AS max_uts
    FROM telemetry
    GROUP BY equipment_id
) t_max 
    ON t.equipment_id = t_max.equipment_id 
    AND t.uts = t_max.max_uts;

此方案复用了原有的高效分组逻辑，关联时依赖索引快速定位目标记录，性能接近原查询。

方案2：窗口函数ROW_NUMBER() #

按equipment_id分区，以uts降序排序后取每组第一条记录：

SELECT transmission_tag
FROM (
    SELECT 
        transmission_tag,
        ROW_NUMBER() OVER (PARTITION BY equipment_id ORDER BY uts DESC) AS rn
    FROM telemetry
) t
WHERE rn = 1;

PostgreSQL会利用(equipment_id, uts)索引进行分区排序，无需全表扫描，适合大表场景。

方案3：PostgreSQL特有的DISTINCT ON语法 #

这是PostgreSQL针对此类场景的最优方案之一，语法简洁且性能优异：

SELECT DISTINCT ON (equipment_id) transmission_tag
FROM telemetry
ORDER BY equipment_id, uts DESC;

DISTINCT ON会按指定字段分组，取ORDER BY排序后的第一条记录。只要存在(equipment_id, uts)索引，数据库会直接通过索引反向扫描（降序）获取数据，完全避免全表扫描。

原方案性能差的原因 #

将equipment_id和uts拼接为字符串后，数据库无法利用(equipment_id, uts)的复合索引，只能对全表每条记录进行字符串拼接和匹配，导致性能急剧下降。

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