如何优化PostgreSQL查询以高效获取各id1的最低score对应数据

问题说明 #

现有test表结构及数据如下：

需要实现的需求是：快速查询每个id1分组下，score值最低的所有id2记录，期望输出结果如下：

当前使用的查询语句为：

SELECT m1.id1, m1.id2, m2.min_score 
FROM test m1
INNER JOIN (
        SELECT id1, MIN(score) as min_score
        FROM test
        GROUP BY id1
        ) m2
ON (m1.id1 = m2.id1 AND m1.score = m2.min_score) 
ORDER BY m1.id1, m2.min_score

以下是几种更高效的实现方式：

1. 使用窗口函数（推荐） #

窗口函数可以避免原查询中的JOIN操作，只需一次表扫描就能完成计算，逻辑简洁且性能更优。因为要保留所有最低分的记录，这里用RANK()或DENSE_RANK()都可以：

SELECT id1, id2, score AS min_score
FROM (
    SELECT 
        id1, 
        id2, 
        score,
        RANK() OVER (PARTITION BY id1 ORDER BY score) AS rnk
    FROM test
) t
WHERE rnk = 1
ORDER BY id1, min_score;

这个写法会给每个id1分组内的记录按score排名，筛选出排名为1的记录，就是该分组的最低分所有记录。

2. 给原查询添加索引优化 #

如果想保留原JOIN的写法，给test表建立复合索引(id1, score)，可以让数据库直接通过索引完成分组求最小值和后续的JOIN匹配，避免全表扫描：

CREATE INDEX idx_test_id1_score ON test(id1, score);

添加索引后，原查询的子查询可以快速获取每个id1的最低分，JOIN阶段也能通过索引快速定位到对应的记录，性能会大幅提升。

3. 使用LATERAL连接（适合大表场景） #

PostgreSQL的LATERAL连接可以针对每个id1单独查询其最低分记录，适合数据量极大且分组较多的场景：

SELECT t.id1, s.id2, s.score AS min_score
FROM (SELECT DISTINCT id1 FROM test) t
LEFT JOIN LATERAL (
    SELECT id2, score
    FROM test
    WHERE id1 = t.id1
    ORDER BY score ASC
    LIMIT ALL
) s ON true
WHERE s.score = (SELECT MIN(score) FROM test WHERE id1 = t.id1)
ORDER BY t.id1, min_score;

不过这个写法相对复杂，一般优先选择窗口函数的方案。

性能总结 #

• 窗口函数方案：一次表扫描，逻辑清晰，在大多数场景下性能最优，尤其是表数据量较大时，比原JOIN写法少一次表扫描。
• 索引优化后的原查询：性能接近窗口函数，但需要额外维护索引，适合需要保留原有查询结构的场景。
• LATERAL连接：适合超大规模表，但写法繁琐，一般场景下必要性不高。

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