PostgreSQL 12 700万行表如何不使用高成本Distinct获取前5条唯一记录

1. 最优方案：创建针对性覆盖索引（一劳永逸，性能提升最明显） #

你的查询慢的核心原因是：普通DISTINCT需要先拉取所有符合col3='abc'、col4='xyz'的记录，做完整体排序去重后才能取前5条，符合条件的记录量级大的话开销极高。
创建如下覆盖索引可以直接让查询效率提升到和不带DISTINCT的版本几乎一致：

-- 生产环境建议加CONCURRENTLY避免锁表，不影响业务写入
CREATE INDEX CONCURRENTLY idx_tab_a_opt ON tab_a (col3, col4, col2, col1);

索引原理：

• 等值过滤条件col3、col4放在最左侧，符合B树索引最左匹配原则，能快速定位到符合条件的数据集
• 排序列col2紧随其后，让索引中符合条件的记录天然按col2有序，查询时不需要额外排序
• 最后加上col1做覆盖，查询时直接从索引取数不需要回表
  配合这个索引，执行器扫描时只要按顺序取不重复的col1、col2组合，拿到5条就直接终止，不需要扫描全部符合条件的记录，耗时可降到0.3秒以内。

2. 语法优化：使用PostgreSQL专属DISTINCT ON语法 #

PostgreSQL提供的DISTINCT ON语法专门针对"取唯一组合"的场景，比通用DISTINCT执行效率更高，配合上面的索引效果最佳：

SELECT DISTINCT ON (col1, col2) col1, col2
FROM tab_a
WHERE col3 = 'abc' AND col4 = 'xyz'
-- DISTINCT ON指定的字段必须放在ORDER BY最前，同时满足你按col2排序的需求
ORDER BY col2, col1
LIMIT 5;

该语法的执行逻辑是按排序顺序扫描，遇到重复的col1、col2组合直接跳过，不需要对全量数据做去重排序，只要拿到5个唯一组合就停止执行。

3. 临时方案优化（暂时无法建索引时使用） #

你原来的子查询limit 50方案鲁棒性不足的核心问题是：如果前50条记录重复率过高，可能凑不够5个唯一的col1、col2组合，导致返回结果少于5条不符合业务需求。
可以根据业务实际重复率估算调整内层limit值，比如如果最大重复率是20:1，就把内层limit设为1000，即使扫描1000条去重的开销也远低于全量数据去重，既能保持高性能，鲁棒性也足够覆盖绝大多数场景：

SELECT DISTINCT col1, col2
FROM (
       SELECT col1, col2
       FROM tab_a
       WHERE col3='abc' AND col4='xyz'
       ORDER BY col2
       LIMIT 1000 -- 根据业务重复率调整为足够安全的阈值
) t
LIMIT 5;

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