PostgreSQL执行时长18秒的DISTINCT ON慢查询如何优化？

问题根因分析 #

从给出的执行计划可以看出当前查询的核心性能损耗点：

• 仅使用idx_logged_at单字段索引，扫描符合时间条件的行后，有79825行因不满足server_name条件被过滤，99%的扫描行都是无效数据，占用了几乎全部执行时间
• 需要对查询结果额外排序后执行DISTINCT ON逻辑，虽然当前排序数据量小没有产生磁盘排序，但也有额外开销
• 原SQL存在笔误：player_anme应为player_name，会导致查询报错或取到错误字段

优化方案 #

1. 适配查询逻辑创建覆盖索引 #

这是收益最高的优化手段，创建的联合索引需要同时匹配过滤条件、排序逻辑、DISTINCT ON规则，避免回表和额外计算：

-- 适配大小写不敏感的server_name匹配，覆盖排序和返回字段
CREATE INDEX idx_opt_player_cache ON player_cache (lower(server_name), player_name, logged_at DESC) INCLUDE (joined_at);

同时调整SQL中的过滤条件，让索引可以命中：
把原条件server_name ILIKE '{query[0]}'修改为lower(server_name) = lower('{query[0]}')。

调整后索引会直接过滤出同时符合server_name和logged_at条件的行，且顺序完全匹配ORDER BY player_name, logged_at DESC的排序规则，不需要额外排序，DISTINCT ON可以直接在索引扫描过程中完成，执行时间会降到毫秒级。

2. 可选优化（场景适配） #

如果你的查询中{query[0]}确实需要带通配符做模糊匹配，可以安装pg_trgm扩展后为server_name创建GIN索引适配模糊查询；如果业务允许的话也可以将server_name字段修改为citext类型，自带大小写不敏感属性，不需要在索引和查询中调用lower函数。

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