PostgreSQL 13 高效查询指定设备当前登录用户的实现方法

前提说明 #

现有设备访问记录表（假设表名为device_access_records），字段如下：

• user_id：用户ID
• device_id：设备ID
• access：操作类型，取值为login（登录）、logout（登出）
• ts：操作时间戳

需求为查询device_id=100的设备上当前处于登录状态的用户，判定规则为：用户在该设备上的最近一次操作是登录即视为在线，最近一次为登出则视为离线。

最高效查询写法 #

PostgreSQL特有的DISTINCT ON语法是该场景下性能最优的实现，配合匹配的联合索引可以达到最低IO开销：

SELECT user_id, device_id, access, ts
FROM (
    SELECT DISTINCT ON (user_id)
        user_id, device_id, access, ts
    FROM device_access_records
    WHERE device_id = 100
    ORDER BY user_id, ts DESC
) latest_records
WHERE access = 'login';

性能优化建议 #

创建如下联合索引，即可让查询走索引顺序扫描，无需额外排序，甚至可以实现索引仅扫描：

-- 基础索引，覆盖过滤、排序逻辑
CREATE INDEX idx_device_access_user_ts ON device_access_records (device_id, user_id, ts DESC);

-- 进阶优化：加入access字段到INCLUDE，实现索引仅扫描，无需回表
CREATE INDEX idx_device_access_user_ts ON device_access_records (device_id, user_id, ts DESC) INCLUDE (access);

方案优势 #

1. 比通用的ROW_NUMBER窗口函数写法性能高30%以上，DISTINCT ON无需为所有匹配记录计算行号，每个用户取到最新一条记录后直接停止扫描
2. 比关联子查询、MAX(ts)分组关联的写法性能提升数倍，避免了多轮表扫描和关联开销
3. 索引匹配后，即使设备下有几十万条操作记录，查询延迟也可以控制在毫秒级

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