PostgreSQL中ORDER BY搭配LIMIT未按预期走索引的性能问题

表结构说明 #

现有两张表event_deltas和deltas_to_retrieve，均在(event_id, version)列上建有BTREE索引，建表语句如下：

CREATE TABLE event_deltas
(
  event_id     UUID REFERENCES events(id) NOT NULL,
  version      INT NOT NULL,
  json_patch   JSONB NOT NULL,
  PRIMARY KEY (event_id, version)
);

CREATE TABLE deltas_to_retrieve(event_id UUID NOT NULL, version INT NOT NULL);
CREATE UNIQUE INDEX event_id_version ON deltas_to_retrieve (event_id, version);

数据规模与查询语句 #

• deltas_to_retrieve是仅约500行的小型查找表
• event_deltas表约有700万行数据
  使用的查询语句如下，期望单次最多返回5000行结果：

SELECT ed.event_id, ed.version
FROM deltas_to_retrieve zz, event_deltas ed
WHERE zz.event_id = ed.event_id
  AND ed.version > zz.version
ORDER BY ed.event_id, ed.version
LIMIT 5000;

不加LIMIT时该查询约返回3万行结果，加ORDER BY时执行时间约10秒，不加则不到1秒，性能差异巨大。

官方文档说明 #

PostgreSQL官方文档明确说明：

ORDER BY搭配LIMIT n是一个重要的特殊场景：显式排序需要处理所有数据才能筛选出前n行，但如果存在匹配ORDER BY顺序的索引，可以直接获取前n行，无需扫描剩余数据。
按该说明现有event_deltas的主键索引完全匹配ORDER BY的顺序，加ORDER BY不应导致性能下降，但实际执行结果和预期不符。

执行计划差异原因分析 #

无ORDER BY的执行计划 #

优化器选择Nested Loop执行路径：先全表扫描deltas_to_retrieve的500行数据，再逐行通过主键索引查找event_deltas中符合条件的记录，攒够5000行就直接返回，总执行时间仅2秒左右。
小表deltas_to_retrieve走全表扫描不是问题，500行数据的全表扫描成本远低于走索引，属于优化器的正常选择。

有ORDER BY的执行计划 #

优化器错误选择了Merge Join执行路径：

1. 先对deltas_to_retrieve按索引排序，再沿着event_deltas的主键索引顺序扫描全表做归并连接
2. 因为有ed.version > zz.version的过滤条件，大量event_deltas的行被过滤，实际需要扫描180多万行event_deltas才能攒够5000条符合条件的结果，最终执行时间飙升到500秒以上
   这是PostgreSQL 11版本优化器对JOIN+ORDER BY+LIMIT场景的行过滤率预估错误导致的，不属于单表ORDER BY走索引的场景，所以不符合官方文档提到的优化前提。

解决方案 #

方案1：会话级别临时关闭Merge Join #

执行查询前先在当前会话执行：

SET enable_mergejoin = off;

即可强制优化器选择Nested Loop执行路径，性能和不加ORDER BY的版本一致。

方案2：使用LATERAL JOIN改写查询 #

通过显式的LATERAL子查询引导优化器走正确的执行路径：

SELECT ed.event_id, ed.version
FROM deltas_to_retrieve zz
CROSS JOIN LATERAL (
    SELECT event_id, version 
    FROM event_deltas 
    WHERE event_id = zz.event_id AND version > zz.version
    ORDER BY event_id, version
) ed
ORDER BY ed.event_id, ed.version
LIMIT 5000;

方案3：使用查询提示强制走Nested Loop #

如果安装了pg_hint_plan插件，可以直接在查询中加提示指定连接方式：

/*+ NestLoop(zz ed) */
SELECT ed.event_id, ed.version
FROM deltas_to_retrieve zz, event_deltas ed
WHERE zz.event_id = ed.event_id
  AND ed.version > zz.version
ORDER BY ed.event_id, ed.version
LIMIT 5000;

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