PostgreSQL 如何用 LATERAL 子查询替换相关子查询提升性能

相关子查询在postgresql中易变慢，因其每行外层数据都重执行内层查询，若无索引或复杂JOIN，性能呈指数恶化；LATERAL可显式声明依赖、支持下推过滤与更优连接算法。

为什么相关子查询在 PostgreSQL 里容易变慢

相关子查询（correlated subquery）每次外层行都会重新执行一次内层查询，如果外层有 10 万行，内层又没走索引或涉及多表 JOIN，性能会指数级恶化。PostgreSQL 无法对多数相关子查询做有效物化，优化器常被迫选择嵌套循环（Nested Loop），而 LATERAL 显式声明依赖关系后，优化器能更早识别可下推的过滤条件、复用索引扫描，甚至改用 Hash Join 或 Merge Join。

用 LATERAL 替换单值标量子查询的写法

常见场景是“查每个用户最新一条订单”：

select u.id, u.name,   (SELECT o.amount FROM orders o WHERE o.user_id = u.id ORDER BY o.created_at DESC LIMIT 1) AS last_amount FROM users u;

改成 LATERAL 后：

SELECT u.id, u.name, l.amount AS last_amount FROM users u LEFT JOIN LATERAL (   SELECT o.amount   FROM orders o   WHERE o.user_id = u.id   ORDER BY o.created_at DESC   LIMIT 1 ) l ON true;

• LATERAL 子查询可直接引用 u.id，且只对当前 u 行执行一次
• LEFT JOIN ... ON true 保证用户行不丢失（对应原标量子查询返回 NULL 的行为）
• 必须给子查询起别名（如 l），否则语法报错：Error: syntax error at or near "SELECT"
• 若确定每用户必有订单，可用 JOIN LATERAL 省去 ON true

替换多列/多行结果时要注意 JOIN 类型和别名作用域

当原相关子查询返回多列或多行（如每个用户最近 3 笔订单），LATERAL 更自然：

SELECT u.id, u.name, o.id AS order_id, o.amount, o.created_at FROM users u JOIN LATERAL (   SELECT id, amount, created_at   FROM orders o2   WHERE o2.user_id = u.id   ORDER BY o2.created_at DESC   LIMIT 3 ) o ON true;

• 不能写成 SELECT * FROM users u, LATERAL (…) —— 虽然语法允许，但语义模糊，易误读为 CROSS JOIN
• LATERAL 子查询里的表别名（如 o2）不能和外层同名，否则报错：table name "o2" specified more than once
• 若子查询可能无结果，又想保留用户行，必须用 LEFT JOIN LATERAL，且 ON true 不可省略
• 子查询中 ORDER BY + LIMIT 若缺索引，仍会慢；确保 (user_id, created_at DESC) 有联合索引

哪些情况 LATERAL 反而更差？

不是所有相关子查询都适合换。以下情形要警惕：

• 子查询逻辑极简单（如 SELECT u.id * 2），LATERAL 增加解析开销，无实际收益
• 外层结果集极小（LATERAL 不改变执行次数，只是让计划更“显式”，未必提速
• 子查询含不可下推的聚合或窗口函数（如 ROW_NUMBER() OVER (PARTITION BY ...)），LATERAL 无法规避重复计算
• 使用了 UNION 或多个 CTE 嵌套的复杂子查询，LATERAL 可能导致计划退化，需对比 EXPLAIN (ANALYZE, BUFFERS)

真正关键的是：先看执行计划里是否出现高频的 Subplan 节点，再测 LATERAL 版本——它不自动加速，只是给了优化器更清晰的优化路径。索引缺失时，换写法也救不了性能。