PostgreSQL中带UUID的ORDER BY与LIMIT查询为何如此缓慢？

问题本质 #

你遇到的情况核心原因是索引使用逻辑差异：

• 执行WHERE fk = ? ORDER BY id时，数据库大概率先走fk的索引找出所有匹配行，然后需要把这些行的id（UUID字符串）提取出来做排序操作——UUID是无序的字符串，当匹配的行数较多时，排序的CPU和IO成本极高，这就是耗时50秒的根源。
• 而ORDER BY fk时，因为查询条件就是fk = ?，数据库直接顺着fk的有序索引读取数据，不需要额外排序，所以速度快。另外移除ORDER BY后，数据库只需返回匹配的前5条数据，也跳过了排序步骤，所以耗时骤降。

解决方案 #

1. 创建联合覆盖索引
   直接把id纳入fk的索引中，让索引本身就能提供有序的结果：

   CREATE INDEX idx_fk_id ON your_table(fk, id);
   

   这个索引的结构是先按fk分组，同一fk下的id会按顺序存储。查询时数据库可以直接通过这个索引找到匹配fk的行，并且已经是按id排序好的，直接取前5条即可，完全跳过排序步骤。

2. 验证执行计划
   用EXPLAIN命令查看查询的执行计划，之前的慢查询应该会显示Using filesort（表示做了磁盘排序），创建新索引后，这个标记会消失，执行计划会显示使用idx_fk_id索引。

3. 进阶：索引覆盖查询
   如果你的查询不需要返回全表字段（SELECT *），可以把需要的字段也加到索引里，实现完全从索引取数据，连回表操作都省了：

   CREATE INDEX idx_fk_id_needed_cols ON your_table(fk, id, col1, col2);
   

   这样查询时数据库直接从索引读取所需数据，性能会进一步提升。

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