PostgreSQL DISTINCT ON 取每设备最新上报：索引与执行计划对比

问题场景

设备上报表通常持续写入，每个 device_id 有多条记录。业务要取每台设备最新一条上报，常见写法是用 row_number() 窗口函数按 device_id 分区、按 reported_at 降序编号，再筛 rn=1。这个写法语义清楚，但通常要为全部行计算窗口编号并排序。PostgreSQL 提供了 DISTINCT ON，它按指定表达式保留排序后的第一行，配合 (device_id, reported_at DESC) 复合索引，有机会让索引提供顺序，减少显式排序和窗口计算。本文给出最小可运行示例和 EXPLAIN ANALYZE 对比方法。

需要注意：DISTINCT ON 并不会自动减少需要读取的行数，它主要影响排序和去重方式；实际计划要以 EXPLAIN ANALYZE 为准。

建表、数据与索引

以下脚本可在测试库执行。设备号、时间范围和条数均为占位值，按需替换。

DROP TABLE IF EXISTS device_report;

CREATE TABLE device_report (
    id          bigserial PRIMARY KEY,
    device_id   integer       NOT NULL,
    reported_at timestamptz   NOT NULL,
    metric      numeric(10,2) NOT NULL
);

INSERT INTO device_report (device_id, reported_at, metric)
SELECT d,
       timestamptz '2025-01-01 00:00:00+00' - (g || ' minutes')::interval,
       (d * 100 + g % 100)::numeric(10,2)
FROM generate_series(1, 5) AS d
CROSS JOIN generate_series(1, 1000) AS g;

CREATE INDEX idx_device_report_device_time
    ON device_report (device_id, reported_at DESC, id DESC);

ANALYZE device_report;

索引列顺序与查询的 ORDER BY 对齐：先 device_id，再 reported_at DESC，最后 id DESC 作为同一时间的兜底排序。若业务不需要同一时间多条去重，可以去掉 id 列；但为了保证结果确定，建议保留 tie-breaker。

两种写法

写法 A：row_number() 窗口子查询。

EXPLAIN (ANALYZE, BUFFERS)
SELECT device_id, reported_at, id, metric
FROM (
    SELECT id,
           device_id,
           reported_at,
           metric,
           row_number() OVER (
               PARTITION BY device_id
               ORDER BY reported_at DESC, id DESC
           ) AS rn
    FROM device_report
) AS t
WHERE rn = 1
ORDER BY device_id;

写法 B：DISTINCT ON。

EXPLAIN (ANALYZE, BUFFERS)
SELECT DISTINCT ON (device_id)
       device_id, reported_at, id, metric
FROM device_report
ORDER BY device_id, reported_at DESC, id DESC;

DISTINCT ON 的表达式必须与 ORDER BY 最左侧表达式一致；这里都是 device_id。ORDER BY 后面的 reported_at DESC, id DESC 决定每组保留哪一行。若写成 ORDER BY reported_at DESC 而 DISTINCT ON (device_id)，会报错或语义不符合预期。

运行与计划对比

在 psql 中执行：

psql -h <host> -U <user> -d <db> -f demo.sql

然后分别执行两条 EXPLAIN 语句。重点观察：

• 节点结构：DISTINCT ON 常见为 Unique 上接 Index Scan；row_number 常见为 WindowAgg 上接 Sort 或 Incremental Sort。具体以实际计划为准。
• 扫描行数：看 Seq Scan/Index Scan 的 rows 与 actual rows，以及两种写法是否读取了相同的行数。
• 排序开销：看 Sort Method、Sort Key、Memory，确认是否出现显式 Sort。
• 缓冲区：看 Buffers 的 shared hit/read，判断回表与缓存影响。

如果数据量很小，规划器可能对两种写法都选择 Seq Scan + Sort，这是成本估算的结果，不代表 DISTINCT ON 无效。可以先用 ANALYZE 更新统计信息，再在测试环境观察；不要在生产环境随意关闭 enable_seqscan 来“制造”索引计划。

另外，DISTINCT ON 仍要消费有序输入中的全部行才能输出所有设备，因此不要期待扫描行数一定下降。它的收益主要来自避免窗口函数计算和显式排序；是否真正更快，需要结合设备数、每设备记录数、索引回表成本和缓存命中情况判断。

边界与限制

1. 同一设备同一 reported_at 可能有多条。必须加 id DESC 之类的 tie-breaker，并同步到索引，否则“最新一条”不唯一。
2. 若只要全局最新一条，不要用 DISTINCT ON，直接 ORDER BY reported_at DESC LIMIT 1 更直接。
3. 若每台设备要取前 N 条，DISTINCT ON 不适合，窗口函数或 LATERAL 子查询更合适。
4. 带时间范围过滤时，索引仍可能被使用，但 ORDER BY 的全局有序性与过滤条件可能让规划器改选其他计划，需要用真实数据验证。
5. 复合索引会增加写入和维护成本；只为高频查询创建。若查询列较少，可考虑 INCLUDE 覆盖列，但要权衡索引体积。
6. EXPLAIN ANALYZE 会实际执行语句。生产环境只读查询也应评估影响；写语句请用事务回滚或仅用 EXPLAIN。

小结

DISTINCT ON 配合匹配的复合索引，是取每设备最新一条记录的简洁写法。它不能保证扫描行数更少，但通常能减少窗口计算和显式排序。用 EXPLAIN ANALYZE 对比节点、扫描行数、排序方法和缓冲区，再决定是否替换 row_number() 写法。