17秒→50ms：LATERAL JOIN 真·义父

某次压测后，发现数据不是很理想还。老样子，还是打开GCP的监控，发现了某条SQL响应时间：17 秒， CPU：持续 90%+，但是磁盘 IO：接近 0。

IO 几乎没压力，CPU 在空转，说明数据库在做大量无用计算。

问题现场

为了脱敏，我将我们的业务场景转换成了一个等价的例子：某个电商平台的订单列表，需要显示每个订单的商品明细。

数据规模：

orders 表：10 万条订单
order_items 表：50 万条商品明细（平均每单 5 件商品）

需求： 查询最近 10 个待处理订单，并展示每个订单的商品列表（聚合成 JSON）。

问题代码

开发写了一个看起来很合理的 CTE：

-- ❌ 优化前
WITH all_order_items_json AS (
  SELECT
    order_id,
    json_agg(json_build_object(
      'product', product_name,
      'price', price
    )) as items_json
  FROM
    order_items
  GROUP BY
    order_id
)
SELECT
  o.id,
  o.user_name,
  o.created_at,
  COALESCE(i.items_json, '[]') as items
FROM
  orders o
LEFT JOIN
  all_order_items_json i ON o.id = i.order_id
WHERE
  o.status = 'PENDING'
ORDER BY
  o.created_at DESC
LIMIT 10;

执行逻辑：

CTE 先扫描 order_items 表的 50 万行，全部聚合成 JSON
生成临时表，包含所有订单的商品明细
主查询关联临时表，筛选、排序
最后取 10 条, 辛辛苦苦处理完 50,000 行，排好序，最后只取了前 10 个，扔掉了 99.9% 的计算结果。

问题核心： PostgreSQL 不知道你最后只要 10 条，它按照 CTE 的定义，先把 50 万条数据全部处理完，才执行主查询。

跑一次 EXPLAIN ANALYZE 验证：

CTE Scan on all_order_items_json
  ->  HashAggregate
        Group Key: order_id
        Rows: 100000  Width: 64
        Buffers: shared hit=5000
->  Hash Join
      Rows: 10  Width: 256
      Buffers: shared hit=5100
->  Sort
      Sort Key: created_at DESC
Execution Time: 17530.25 ms

关键数字解读：

扫描原始数据： order_items 表的 50 万行全部被处理
聚合后结果： Rows: 100000 — 生成 10 万行临时表（每个订单一行）
内存开销： Buffers: shared hit=5000 — 读取 5000 个 Blocks = 40MB 数据

而我们最后只需要 10 条记录。

优化方案：LATERAL JOIN

核心思路：延迟关联（Later Join），先筛选出需要的 10 条订单，再去查它们的商品明细。

-- 优化后
SELECT
  o.id,
  o.user_name,
  o.created_at,
  COALESCE(details.items_json, '[]') as items
FROM
  orders o
LEFT JOIN LATERAL (
  SELECT
    json_agg(json_build_object(
      'product', product_name,
      'price', price
    )) as items_json
  FROM
    order_items i
  WHERE
    i.order_id = o.id  -- 👈 关键：子查询引用外层变量
) details ON TRUE
WHERE
  o.status = 'PENDING'
ORDER BY
  o.created_at DESC
LIMIT 10;

执行逻辑：

主查询先筛选 orders 表，找出 10 个 PENDING 订单
对每个订单 ID，执行一次 LATERAL 子查询，查询对应的商品明细
只处理 10 个订单 × 5 件商品 = 50 行

LATERAL 允许子查询引用外层查询的列（o.id），相当于一个 For Loop：

for order in get_pending_orders(limit=10):
    order.items = get_order_items(order.id)

再看执行计划：

Limit  (rows=10)
  ->  Nested Loop Left Join
        ->  Index Scan Backward on orders
              Filter: (status = 'PENDING')
              Rows: 10
        ->  Aggregate
              ->  Index Scan on order_items
                    Index Cond: (order_id = o.id)
                    Rows: 5
Execution Time: 48.73 ms

Rows: 10 + Rows: 5 × 10 = 60 行 — 只扫描了需要的数据，没有任何浪费。

结果对比

指标	CTE (Before)	LATERAL (After)	提升
执行时间	17530 ms	49 ms	350倍+
扫描行数	500,000 行（全表）	~60 行	↓99.99%
内存开销	大（临时表存 10 万行）	极小（只处理 10 次）	大幅降低
CPU 使用率	90%+	< 5%	彻底释放
适用场景	全量统计/报表	分页查询/Top-N	—

为什么 CTE 会这么慢？

通过查询，PostgreSQL 12 之前，CTE 会被 **Optimization Fence ** 阻挡，优化器无法把 LIMIT 下推到 CTE 内部。

从 PostgreSQL 12 开始，简单的 CTE 可以被内联优化，但如果 CTE 包含 聚合函数（GROUP BY、json_agg），优化器依然会先执行完整个 CTE。

解决方案：

用 LATERAL JOIN 替代 CTE
或者把 LIMIT 逻辑提到 CTE 内部（如果可行）

适用场景

LATERAL JOIN 适合：

分页查询（LIMIT + OFFSET）
Top-N 查询（只要前几条）
每行数据需要执行复杂子查询（如聚合、排序）

CTE 适合：

全量数据分析/报表
需要多次引用同一个子查询结果
递归查询（WITH RECURSIVE）

写在最后

这次优化的核心不是换了一种写法，而是改变了执行顺序：

CTE：先算全量，再筛选
LATERAL：先筛选，再按需计算

写 SQL 时，脑海里除了要思考是不是要加索引，还要思考一下数据量的问题：数据库是在处理全量数据，还是只处理需要的数据？

遇到性能问题，先跑 EXPLAIN (ANALYZE, BUFFERS)，看看数据库到底在干什么。

Holly

Explorer