PostgreSQL如何单遍扫描表获取每个distinct relation_id的一行数据？

方法1：用DISTINCT ON子句（最直接） #

PostgreSQL原生支持DISTINCT ON语法，能在一次扫描过程中完成分组，直接返回每个relation_id对应的指定行，完全不需要多次遍历表。

示例代码：

SELECT DISTINCT ON (relation_id) id, value, relation_id
FROM your_table
ORDER BY relation_id, id; -- 第二个排序字段决定取每组的哪一行，比如这里按id取最小的那行

• 注意：DISTINCT ON要求ORDER BY的第一个字段必须和DISTINCT ON指定的字段一致，这样数据库扫描时就能实时分组收集数据，不会额外消耗资源。
• 如果建了(relation_id, id)的复合索引，查询会直接走索引扫描，比全表扫描快得多。

方法2：用窗口函数ROW_NUMBER() #

窗口函数也是单遍扫描的方案，给每个relation_id分组的行编序号，再筛选出序号为1的行，全程只扫一次表。

示例代码：

SELECT id, value, relation_id
FROM (
    SELECT 
        id, value, relation_id,
        ROW_NUMBER() OVER (PARTITION BY relation_id ORDER BY id) AS rn
    FROM your_table
) t
WHERE rn = 1;

• 同样，搭配(relation_id, id)的复合索引，窗口函数可以直接利用索引排序，避免内存里做大量排序操作，性能会大幅提升。

为什么你之前的UNION方法慢？ #

用UNION加多个LIMIT子查询的问题在于，每个子查询都会单独扫一遍全表，数十亿行的表扫个几次，开销直接翻倍甚至更多，完全是做无用功。上面两种方法都是一次扫描就搞定，效率差好几个数量级。

额外优化建议 #

• 优先给relation_id加包含排序字段的复合索引，比如执行CREATE INDEX idx_relation_id_id ON your_table (relation_id, id);，能让查询直接走索引，不用扫全表。
• 如果表是分区表，可以结合分区裁剪进一步缩小扫描范围，但核心逻辑还是用上面两种单遍扫描的方式。

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