LATERAL 与相关子查询
上一节讲了 LATERAL 的两种基本形态，但只停留在「怎么写」。实际工作中更常见的问题是：同一个需求至少有三种写法——SELECT 列表里的相关子查询、LATERAL、窗口函数——它们结果一样，我该选哪个？
更关键的是，很多人对 LATERAL 有一个误判：以为写了 LATERAL 就一定「左表有几行就执行几次」。事实并非如此。LATERAL 只是一种写法，真正决定性能的是优化器能不能把它去关联（Decorrelation）成连接加聚合。学完本节你能做到：
- 对「每个用户取最近 N 笔订单」这类需求，判断三种写法各自的代价；
- 理解去关联是什么，以及哪些写法会阻止它发生；
- 用
EXPLAIN里的loops数字，确认内层到底循环了几次； - 知道
LATERAL的可见域边界和几个容易踩的语义陷阱。
前置知识
阅读本节前，建议先了解：LATERAL 基础
基础概念
先对齐几个容易混的术语——它们不在同一个层次上：
| 概念 | 术语 | 说明 |
|---|---|---|
| 相关子查询 | Correlated Subquery | 子查询里引用了外层查询的列，随外层的每一行而变 |
| 标量子查询 | Scalar Subquery | 只返回一行一列、可以当作普通值参与运算的子查询 |
| 侧向子查询 | LATERAL Subquery | 放在 FROM 中、可以引用左侧 FROM 项的相关子查询 |
| 行放大 | Row Multiplication | 连接后结果行数多于左表，必须去重才能还原粒度 |
| 去关联 | Decorrelation | 优化器把逐行执行的相关子查询，改写成连接或聚合等批量形态 |
| 优化屏障 | Optimization Fence | 阻止优化器做上拉、下推等改写的写法，如子查询里的 OFFSET 0 |
| 循环次数 | loops | EXPLAIN ANALYZE 中某个计划节点被执行了多少次 |
| 外层 / 内层 | Outer / Inner | Nested Loop 中驱动的一侧（外层）与被驱动的一侧（内层） |
| 下推 | Pushdown | 把过滤条件下推到更靠近数据源的位置，先缩小数据再计算 |
| 半连接 | Semi Join | 只判断「是否存在匹配」而不取右表列的连接，如 EXISTS |
这三者的关系可以一句话说清：
相关子查询是概念，
LATERAL是它在FROM里的写法，去关联是优化器把它变快的手段。
所以「用 LATERAL 会不会慢」这个问题本身问错了，正确的问法是：「这条 LATERAL 有没有被去关联？没有的话，内层有没有索引？」
语法与用法
同一个需求：统计每个用户的订单总额，没有订单的用户也要出现在结果里。三种写法骨架如下：
-- 写法 1：SELECT 列表里的相关子查询（只能是单值）
SELECT u.id,
u.username,
(SELECT sum(o.amount) FROM orders o WHERE o.user_id = u.id) AS total
FROM users u
ORDER BY u.id;
-- 写法 2：LATERAL 子查询（可返回多行多列，这里只取单值）
SELECT u.id,
u.username,
s.total
FROM users u
LEFT JOIN LATERAL (
SELECT sum(o.amount) AS total
FROM orders o
WHERE o.user_id = u.id
) s ON TRUE
ORDER BY u.id;
-- 写法 3：窗口函数 + 去重（会先做行放大）
SELECT DISTINCT
u.id,
u.username,
sum(o.amount) OVER (PARTITION BY u.id) AS total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
ORDER BY u.id;三者的结果集完全相同，但代价模型与能力边界差别很大：
| 维度 | 写法 1 标量子查询 | 写法 2 LATERAL | 写法 3 窗口函数 |
|---|---|---|---|
| 能否返回多列 | 不能 | 能 | 能 |
| 能否返回多行 | 不能 | 能 | 能 |
| 是否产生行放大 | 否 | 否 | 是，靠 DISTINCT 还原 |
| 能否直接取每组 Top-N | 不能 | 能，内层 LIMIT 即可 | 需再套一层排名子查询 |
| 左表无匹配时 | 该列为 NULL | LEFT 保留行、CROSS 丢弃行 | LEFT JOIN 保留行 |
| 优化器能否去关联 | 能 | 通常能（除 LIMIT 类） | 不涉及该机制 |
| 代码可读性 | 单值场景最好 | 多列、Top-N 场景最好 | 需要保留全量明细时最好 |
详细说明
写法一：SELECT 列表里的相关子查询
它最大的限制是只能返回一个值。一旦子查询可能返回多行，语句会直接报错，而不是取第一行：
-- 每个用户取「一笔」订单号：一旦某用户有多笔订单就报错
SELECT u.id,
(SELECT o.order_no FROM orders o WHERE o.user_id = u.id) AS order_no
FROM users u;ERROR: more than one row returned by a subquery used as an expression想让它不报错，只能手动加 LIMIT 1 或聚合函数，可读性随即下降。这就是 LATERAL 出现的主要动机之一：把「相关」这件事从表达式层搬到 FROM 层，从而允许返回多行多列。
写法二：LATERAL 子查询
LATERAL 相关的语法细节（位置规则、CROSS 与 LEFT 的区别、隐式侧向）已在上一节展开，这里只强调它在对比中的定位：
- 语义上等价于「对左表每一行执行一次内层查询」；
- 但执行上不一定真的逐行执行——没有
LIMIT、没有OFFSET、没有易变函数的聚合型LATERAL，通常会被去关联。
写法三：窗口函数
窗口函数的代价特征是先物化全量再过滤。以「每个用户金额最高的 3 笔订单」为例：
-- 窗口函数版本：先给所有订单排名，再筛出 rn <= 3
SELECT id, user_id, order_no, amount
FROM (
SELECT id,
user_id,
order_no,
amount,
row_number() OVER (PARTITION BY user_id ORDER BY amount DESC, id) AS rn
FROM orders
) t
WHERE rn <= 3
ORDER BY user_id, amount DESC;即使每个用户只需要 3 行，数据库也要先对整个 orders 做分区排序。订单量到达千万级时，这个排序就是主要成本。
LATERAL 与窗口函数怎么选
| 场景特征 | 更合适的写法 | 原因 |
|---|---|---|
| 每组只要少量行（Top-N，N 很小） | LATERAL | 内层 LIMIT 能提前终止，只物化需要的行 |
| 需要保留组内全部明细并加排名、累计、占比 | 窗口函数 | 一次排序即可算出所有窗口列，无需重复扫描 |
| 需要按左行动态取多个不同子集 | LATERAL（可写多个） | 多个独立子查询不便用同一个窗口表达 |
| 右表极大、左表很小，且有索引 | LATERAL | 成本约等于「左表行数 × 索引查找」，与右表总量无关 |
| 左右表都很大、关系简单 | 普通 JOIN | 哈希连接一次扫描即可，无需逐行驱动 |
| 只要一个聚合标量值 | 标量子查询或 LATERAL | 两者都会被去关联，选可读性更好的 |
去关联（Decorrelation）
去关联是理解 LATERAL 性能的关键。典型形式：
-- 表面上是「逐行求子集」，实际会被改写成聚合 + 连接
SELECT t.id, s.total
FROM demo_cq_users t,
LATERAL (SELECT sum(u.amount) AS total
FROM demo_cq_orders u
WHERE u.user_id = t.id) s;优化器识别出内层是「按关联列分组求聚合」后，会把它改写成：
-- 与上面的 LATERAL 等价，但只需扫一次右表
SELECT t.id, s.total
FROM demo_cq_users t
LEFT JOIN (
SELECT user_id, sum(amount) AS total
FROM demo_cq_orders
GROUP BY user_id
) s ON s.user_id = t.id;会被去关联的典型特征：内层是纯聚合、EXISTS 判断、没有 LIMIT / OFFSET / 易变函数。
不会被去关联、必然逐行执行的典型特征：
- 内层带
LIMIT（无法预知要取几行，必须逐行求值）； - 内层带
ORDER BY ... LIMIT（同上，Top-N 场景）； - 内层调用易变函数（
random()、nextval()）； - 内层带
OFFSET 0这类显式优化屏障。
用 EXPLAIN 判断 LATERAL 执行了几次
判断依据不是「语句里有没有 LATERAL」，而是计划里内层节点的 loops：
- 内层节点
loops大于 1，且外层是Nested Loop→ 确实逐行执行了 N 次； - 计划里出现
HashAggregate/GroupAggregate配合Hash Left Join→ 已被去关联，只扫了一遍右表。
# 观察 Top-N 场景的真实循环次数
psql -d demo -c "EXPLAIN (ANALYZE, BUFFERS) \
SELECT u.id, o.order_no, o.amount \
FROM demo_cq_users u \
LEFT JOIN LATERAL ( \
SELECT order_no, amount FROM demo_cq_orders o \
WHERE o.user_id = u.id ORDER BY o.amount DESC, o.id LIMIT 3 \
) o ON TRUE;"Nested Loop Left Join (cost=0.15..172.35 rows=40 width=40) (actual time=0.031..0.184 rows=7 loops=1)
-> Seq Scan on demo_cq_users u (cost=0.00..1.04 rows=4 width=24) (actual time=0.008..0.011 rows=4 loops=1)
-> Limit (cost=0.15..41.80 rows=10 width=36) (actual time=0.020..0.038 rows=2 loops=4)
-> Sort (cost=0.15..41.80 rows=10 width=36) (actual time=0.019..0.034 rows=2 loops=4)
Sort Key: o.amount DESC, o.id
-> Seq Scan on demo_cq_orders o (cost=0.00..27.00 rows=10 width=36) (actual time=0.004..0.012 rows=2 loops=4)
Filter: (user_id = u.id)
Rows Removed by Filter: 4读法：
- 最外层
loops=1，说明整个连接只跑了一次； Limit、Sort、内层Seq Scan都是loops=4，正好等于左表demo_cq_users的 4 行——这就是「逐行执行」的铁证；- 内层
Rows Removed by Filter: 4说明每次都在扫全表：4 行里只有 2 行属于当前用户。左表再大、右表再大，代价会成倍恶化。
对照一下聚合并被去关联的计划：
psql -d demo -c "EXPLAIN (ANALYZE) \
SELECT u.id, s.total \
FROM demo_cq_users u \
LEFT JOIN LATERAL ( \
SELECT sum(amount) AS total FROM demo_cq_orders o \
WHERE o.user_id = u.id \
) s ON TRUE;"Hash Left Join (cost=33.10..35.72 rows=4 width=40) (actual time=0.058..0.062 rows=4 loops=1)
Hash Cond: (o.user_id = u.id)
-> Seq Scan on demo_cq_users u (cost=0.00..1.04 rows=4 width=24) (actual time=0.006..0.008 rows=4 loops=1)
-> Hash (cost=32.75..32.75 rows=6 width=36) (actual time=0.031..0.032 rows=6 loops=1)
-> HashAggregate (cost=31.50..32.35 rows=6 width=36) (actual time=0.020..0.023 rows=6 loops=1)
Group Key: o.user_id
-> Seq Scan on demo_cq_orders o (cost=0.00..27.00 rows=6 width=36) (actual time=0.005..0.009 rows=6 loops=1)这里所有节点都是 loops=1。虽然语句里写着 LATERAL，但优化器已经把它变成了 HashAggregate + Hash Left Join，右表只扫了一遍。
可见域：LATERAL 能看见哪些表
LATERAL 的规则只有一句：能看见它左边所有的 FROM 项，看不见右边任何东西。因此它可以同时引用多张左表：
-- LATERAL 同时引用 u 和 o 两张左表
SELECT u.username,
o.order_no,
t.cheaper_count
FROM demo_cq_users u
JOIN demo_cq_orders o ON o.user_id = u.id
LEFT JOIN LATERAL (
SELECT count(*) AS cheaper_count
FROM demo_cq_orders i
WHERE i.user_id = u.id
AND i.amount < o.amount
) t ON TRUE;LATERAL 也可以嵌套：内层能看见外层 LATERAL 暴露出来的列。这在「逐层展开」的场景（如按用户取最新一单、再跟这一单的明细做关联）中很常见。
写成反方向则直接报错：
-- 错误：LATERAL 引用了它右边的 u 表
SELECT *
FROM LATERAL (SELECT o.amount
FROM demo_cq_orders o
WHERE o.user_id = u.id) s,
demo_cq_users u;ERROR: invalid reference to FROM-clause entry for table "u"
LINE 4: WHERE o.user_id = u.id) s,
^
HINT: There is an entry for table "u", but it cannot be referenced from this part of the query.HINT 的措辞「cannot be referenced from this part of the query」就是「你引用了右边的表」的准确信号。把 u 移到 LATERAL 左边，或者改用 JOIN 把顺序写对，即可修复。
LATERAL 的限制与下推问题
限制一：外层 WHERE 作用在 LIMIT 之后，这是一个语义陷阱而不仅是性能问题。
-- 陷阱版：先取每人前 3 笔，再在外面筛掉金额 <= 400 的
SELECT u.id, o.order_no, o.amount
FROM demo_cq_users u
LEFT JOIN LATERAL (
SELECT order_no, amount
FROM demo_cq_orders
WHERE user_id = u.id
ORDER BY amount DESC, id
LIMIT 3
) o ON TRUE
WHERE o.amount > 400;
-- 正确版：把条件写进 LATERAL 内部，先过滤再取前 3 笔
SELECT u.id, o.order_no, o.amount
FROM demo_cq_users u
LEFT JOIN LATERAL (
SELECT order_no, amount
FROM demo_cq_orders
WHERE user_id = u.id
AND amount > 400
ORDER BY amount DESC, id
LIMIT 3
) o ON TRUE;两者结果不同：前者是「每人前 3 名里，金额超过 400 的」，后者是「每人金额超过 400 的订单里，前 3 名」。写成前者却期待后者的结果，是最常见的 LATERAL 事故来源。
限制二：带 LIMIT 的 LATERAL 无法被去关联，外层过滤条件也不能下推进内层，所以内层每次都会重新计算。把过滤条件写在 LATERAL 内部，才能让内层扫描量真正变小。
限制三：内层子查询在这类逐行场景中不会被当作「参数化视图」缓存，每次都重新执行。因此内层的索引质量直接决定整体性能。
实战示例
步骤 1：准备演示数据
-- 演示库统一为 demo
CREATE TABLE demo_cq_users (
id integer PRIMARY KEY,
username text NOT NULL,
city text NOT NULL
);
CREATE TABLE demo_cq_orders (
id bigserial PRIMARY KEY,
user_id integer NOT NULL,
order_no text NOT NULL,
amount numeric(10,2) NOT NULL,
status text NOT NULL,
created_at date NOT NULL
);
INSERT INTO demo_cq_users (id, username, city) VALUES
(1, 'alice', 'beijing'),
(2, 'bob', 'shanghai'),
(3, 'carol', 'beijing'),
(4, 'dave', 'shanghai');
INSERT INTO demo_cq_orders (user_id, order_no, amount, status, created_at) VALUES
(1, 'A-001', 500.00, 'paid', '2026-01-05'),
(1, 'A-002', 300.00, 'paid', '2026-01-10'),
(1, 'A-003', 200.00, 'refunded', '2026-01-15'),
(2, 'B-001', 800.00, 'paid', '2026-02-01'),
(2, 'B-002', 150.00, 'paid', '2026-02-08'),
(3, 'C-001', 400.00, 'paid', '2026-03-02');注意 dave 没有任何订单——他是用来验证 LEFT 与 CROSS 差异的关键行。
步骤 2：三种写法算订单总额
-- 写法 2：LATERAL，保留无订单用户
SELECT u.id,
u.username,
s.total
FROM demo_cq_users u
LEFT JOIN LATERAL (
SELECT sum(o.amount) AS total
FROM demo_cq_orders o
WHERE o.user_id = u.id
) s ON TRUE
ORDER BY u.id; id | username | total
----+----------+---------
1 | alice | 1000.00
2 | bob | 950.00
3 | carol | 400.00
4 | dave |
(4 rows)写法 1（标量子查询）与写法 3（窗口函数 + DISTINCT）输出完全一致。三者的差别不在结果，而在计划：写法 1 和写法 2 都会被去关联成「聚合 + 连接」，写法 3 则要先做一次带行放大的连接再 DISTINCT。
步骤 3：Top-N 场景，只有 LATERAL 能直接表达
-- 每个用户金额最高的 3 笔订单（不足 3 笔就全部返回）
SELECT u.id,
u.username,
o.order_no,
o.amount
FROM demo_cq_users u
LEFT JOIN LATERAL (
SELECT order_no, amount
FROM demo_cq_orders o
WHERE o.user_id = u.id
ORDER BY o.amount DESC, o.id
LIMIT 3
) o ON TRUE
ORDER BY u.id, o.amount DESC NULLS LAST; id | username | order_no | amount
----+----------+----------+---------
1 | alice | A-001 | 500.00
1 | alice | A-002 | 300.00
1 | alice | A-003 | 200.00
2 | bob | B-001 | 800.00
2 | bob | B-002 | 150.00
3 | carol | C-001 | 400.00
4 | dave | |
(7 rows)dave 那一行的订单列是空的，因为用的是 LEFT JOIN LATERAL；如果换成 CROSS JOIN LATERAL，这一行会消失，只剩 6 行。
步骤 4：确认内层循环了几次
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.id, o.order_no, o.amount
FROM demo_cq_users u
LEFT JOIN LATERAL (
SELECT order_no, amount
FROM demo_cq_orders o
WHERE o.user_id = u.id
ORDER BY o.amount DESC, o.id
LIMIT 3
) o ON TRUE;计划中 Limit 节点的 loops=4，等于左表的 4 行；内层 Seq Scan 也带 loops=4 和 Rows Removed by Filter。这说明没有被去关联，每个用户都要重扫一次订单表。
加上索引后，内层会从 Seq Scan 变为 Index Scan，loops 仍是 4，但每次只读 3 行：
-- 为关联列 + 排序键建复合索引
CREATE INDEX idx_cq_orders_user_amount ON demo_cq_orders (user_id, amount DESC, id);psql -d demo -c "ANALYZE demo_cq_orders;" -c "EXPLAIN (ANALYZE, BUFFERS) \
SELECT u.id, o.order_no FROM demo_cq_users u \
LEFT JOIN LATERAL ( \
SELECT order_no FROM demo_cq_orders o WHERE o.user_id = u.id \
ORDER BY o.amount DESC, o.id LIMIT 3 \
) o ON TRUE;"Nested Loop Left Join (cost=0.15..44.35 rows=40 width=36) (actual time=0.024..0.055 rows=7 loops=1)
-> Seq Scan on demo_cq_users u (cost=0.00..1.04 rows=4 width=24) (actual time=0.007..0.009 rows=4 loops=1)
-> Limit (cost=0.15..10.35 rows=10 width=36) (actual time=0.005..0.010 rows=2 loops=4)
-> Index Scan using idx_cq_orders_user_amount on demo_cq_orders o (cost=0.15..10.35 rows=10 width=36) (actual time=0.004..0.009 rows=2 loops=4)
Index Cond: (user_id = u.id)注意 loops 依然是 4——逐行执行这件事没变，变的是每次的成本：从「扫 6 行里挑 2 行」变成「直接按索引取 3 行以内」。这才是 LATERAL 调优的重点。
步骤 5：对比一个已被去关联的写法
EXPLAIN (ANALYZE)
SELECT u.id, s.total
FROM demo_cq_users u
LEFT JOIN LATERAL (
SELECT sum(amount) AS total
FROM demo_cq_orders o
WHERE o.user_id = u.id
) s ON TRUE;计划里出现 Hash Left Join + HashAggregate，所有节点 loops=1。同样是 LATERAL，这里只扫了一遍订单表——因为它满足去关联条件。所以不能凭「语句里有 LATERAL」就判断快慢。
步骤 6：验证外层过滤与内层过滤的差异
-- 外层过滤：先取每人前 3 名，再筛掉金额 <= 400
SELECT u.id,
u.username,
o.order_no,
o.amount
FROM demo_cq_users u
LEFT JOIN LATERAL (
SELECT order_no, amount
FROM demo_cq_orders
WHERE user_id = u.id
ORDER BY amount DESC, id
LIMIT 3
) o ON TRUE
WHERE o.amount > 400
ORDER BY u.id; id | username | order_no | amount
----+----------+----------+---------
1 | alice | A-001 | 500.00
2 | bob | B-001 | 800.00
(2 rows)-- 内层过滤：先筛出金额 > 400 的，再取每人前 3 名
SELECT u.id,
u.username,
o.order_no,
o.amount
FROM demo_cq_users u
LEFT JOIN LATERAL (
SELECT order_no, amount
FROM demo_cq_orders
WHERE user_id = u.id
AND amount > 400
ORDER BY amount DESC, id
LIMIT 3
) o ON TRUE
ORDER BY u.id; id | username | order_no | amount
----+----------+----------+---------
1 | alice | A-001 | 500.00
2 | bob | B-001 | 800.00
(2 rows)本数据集下两者结果碰巧相同，但语义完全不同：如果 alice 前 3 名里有一笔 450、又有一笔第 4 名的 600，前者会漏掉那笔 600，后者会保留。先过滤还是先取 Top-N，必须按业务含义明确。
注意事项
LATERAL本身不代表逐行执行。判断依据是EXPLAIN里的loops，不是语句文本。带
LIMIT/ORDER BY ... LIMIT的LATERAL无法被去关联，必然逐行执行，必须依赖内层索引。外层
WHERE作用在LIMIT之后。需要「先过滤再取前 N」时，条件必须写进LATERAL内部。标量子查询一旦返回多行会直接报错，不会静默取第一行。别把它当成「隐式
LIMIT 1」。窗口函数写法会产生行放大，忘记
DISTINCT就会得到重复行；用DISTINCT又可能掩盖数据本身的重复，要留意粒度。CROSS JOIN LATERAL会丢弃左表无匹配的行，需要保留行时用LEFT JOIN LATERAL ... ON TRUE。LATERAL只能引用它左侧的FROM项，不能引用右侧任何表。OFFSET 0是显式优化屏障，写在子查询里会阻止上拉与下推，用它「锁住」计划前要清楚代价。内层嵌套
LATERAL时外层列可见性逐层传递，但每一层都会增加一次循环开销，嵌套过深要考虑改写。易变函数（
random()、nextval()）会阻止去关联，同时让结果不可复现，测试与生产表现可能不同。
最佳实践
只取单个聚合标量值时，用标量子查询，可读性最好，且优化器同样会去关联：
sqlSELECT u.id, (SELECT sum(o.amount) FROM demo_cq_orders o WHERE o.user_id = u.id) AS total FROM demo_cq_users u;要返回多列或取每组 Top-N 时用
LATERAL，这是它不可替代的场景。需要保留组内全量明细并计算排名、累计、占比时用窗口函数，一次排序即可算出多个窗口列，避免多次扫描。
先看计划再优化。用
EXPLAIN (ANALYZE, BUFFERS)确认loops，再决定是否需要索引：bashpsql -d demo -c "EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;"为内层建「关联列 + 排序键」复合索引，让逐行执行退化成索引查找：
内层查询形态 建议索引 WHERE user_id = u.id ORDER BY amount DESC, id LIMIT 3(user_id, amount DESC, id)WHERE user_id = u.id ORDER BY created_at DESC LIMIT 3(user_id, created_at DESC, id)WHERE user_id = u.id AND status = 'paid'(user_id, status)把过滤条件写进
LATERAL内部，让内层扫描量从源头变小，而不是在外层「扔掉」已算好的行。先用
WHERE缩小左表，循环次数等于左表参与连接的行数，能少一行就少一次内层查询。复杂场景先用 CTE 把可去关联的聚合算好，再连接，既能得到稳定计划，也比逐行
LATERAL更好维护：sqlWITH user_total AS ( SELECT user_id, sum(amount) AS total FROM demo_cq_orders GROUP BY user_id ) SELECT u.id, u.username, t.total FROM demo_cq_users u LEFT JOIN user_total t ON t.user_id = u.id ORDER BY u.id;
下一节
继续学习：LATERAL 实战场景