PostgreSQL 15.1时序表按事件类型取最新值的高效查询方案

针对千万级数据、上千种事件类型的场景，以下方案可充分利用索引实现高效查询：

核心方案：使用DISTINCT ON（PostgreSQL 特有） #

这是性能最优的方案，配合专用复合索引可直接通过索引扫描获取结果，无需全表遍历或昂贵聚合操作。

1. 创建必备索引 #

先创建按evt_type分组、evt_time降序排列的复合索引：

CREATE INDEX idx_events_type_time_desc ON events (evt_type, evt_time DESC);

该索引会将同一evt_type的记录按时间从新到旧排列，数据库可直接定位每个类型的第一条最新记录。

2. 查询SQL #

SELECT DISTINCT ON (evt_type)
       evt_type, evt_val_01, evt_val_02, evt_time
FROM events
ORDER BY evt_type, evt_time DESC;

• DISTINCT ON (evt_type)按evt_type分组，保留每个组内排序后的第一条记录
• ORDER BY必须以evt_type开头，后跟evt_time DESC，与索引顺序匹配，确保数据库走索引扫描

备选方案1：窗口函数ROW_NUMBER() #

若更习惯窗口函数写法，同样可利用上述索引实现高效查询：

SELECT evt_type, evt_val_01, evt_val_02, evt_time
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY evt_type ORDER BY evt_time DESC) AS rn
    FROM events
) sub
WHERE rn = 1;

数据库会通过索引为每个evt_type分区排序，直接筛选出rn=1的最新记录，避免全表排序开销。

备选方案2：LATERAL连接（修正版） #

若之前尝试LATERAL未成功，可能是写法问题，以下是正确的高效写法：

SELECT e.*
FROM (SELECT DISTINCT evt_type FROM events) et
JOIN LATERAL (
    SELECT evt_type, evt_val_01, evt_val_02, evt_time
    FROM events
    WHERE evt_type = et.evt_type
    ORDER BY evt_time DESC
    LIMIT 1
) e ON true;

每个LATERAL子查询都会通过索引快速定位对应evt_type的最新记录，适合evt_type数量较多的场景。

为什么常规关联MAX(evt_time)方法慢？ #

常规方法需先通过GROUP BY聚合得到每个evt_type的最大时间，再和原表关联，这会导致两次大规模数据扫描（一次聚合、一次关联），在千万级数据下开销极大。而上述方案都通过索引直接获取最新记录，避免了重复扫描。

内容的提问来源于stack exchange，提问作者Peter K.