postgresql 14 禁止在 order by 中直接使用相关标量子查询，因其强制逐行嵌套执行、无法利用索引排序；必须重写为 left join 或 lateral join 才能避免 sort 节点并启用索引。
标量子查询在ORDER BY里会强制嵌套循环
PostgreSQL 14 不允许在 ORDER BY 子句中直接使用相关标量子查询（例如 ORDER BY (SELECT x FROM t2 WHERE t2.id = t1.ref_id)），语法上就会报错：ERROR: subquery in ORDER BY is not allowed。所以实际遇到的“慢”，往往不是排序本身慢，而是你把标量子查询写在了 SELECT 列表或 WHERE 中，又依赖它的结果参与排序逻辑——这时 PostgreSQL 只能先算出每一行的子查询值，再整体排序，彻底失去索引优势。
为什么不能靠索引加速这种组合
即使你在子查询涉及的字段上建了索引，也救不了：标量子查询在执行时是逐行触发的（nested loop），每算一次都要查一次 t2；而 ORDER BY 需要所有行的排序键就绪后才能启动排序。这意味着：
- 无法提前利用索引顺序输出结果（Index Scan + no Sort）
- 无法把子查询“拉平”成 JOIN（优化器对 SELECT 列里的相关子查询基本不重写）
- 如果子查询返回 NULL 或多行，还会在运行时报错，打断整个查询
必须重写为 JOIN 或 LATERAL 才能走索引
真正可行的路径只有两个，且都要求把子查询从表达式位置“提出来”：
-
用 LEFT JOIN 替代（推荐）：把子查询改写成显式连接，确保
t2上有id索引，再在ORDER BY t2.sort_field上建复合索引。例如：SELECT t1.*, t2.sort_field FROM t1 LEFT JOIN t2 ON t2.id = t1.ref_id ORDER BY t2.sort_field; -
用 LATERAL JOIN（更灵活）：当子查询逻辑复杂（含 LIMIT、聚合、多表）时，
LATERAL是唯一能保持语义又可控的方式。注意必须给LATERAL子查询的输出列加别名，才能用于ORDER BY：SELECT t1.*, s.val FROM t1 LEFT JOIN LATERAL (SELECT sort_val AS val FROM t2 WHERE t2.id = t1.ref_id ORDER BY t2.priority LIMIT 1) s ON true ORDER BY s.val;
这两种写法都能让 PostgreSQL 在计划中生成 Hash Join 或 Nested Loop，并可能复用 t2 的索引完成排序，避免最终的 Sort 节点。
work_mem 和排序方式仍需验证
即使重写成功，也要用 EXPLAIN (ANALYZE, BUFFERS) 确认是否真跳过了 Sort 节点。如果仍有 Sort Method: external merge，说明中间结果太大，work_mem 不够——这不是索引问题，而是内存配置或数据量问题。此时可临时调高：SET LOCAL work_mem = '128MB';
但更治本的做法是加 LIMIT、缩小 WHERE 范围，或确认 LATERAL 子查询是否真的只返回一行（否则会放大结果集）。
最容易被忽略的是：标量子查询一旦进入 SELECT 列，就锁死了执行模型；你没法靠调参数绕过它，只能重写。别在 ORDER BY 附近打补丁，得从源头拆掉那个子查询。
