相关子查询（Correlated Subquery）

本节讲解相关子查询（Correlated Subquery）的执行模型、SubPlan 与 InitPlan 的识别、逐行求值的复杂度代价、三种改写手法（聚合连接 / 窗口函数 / LATERAL），以及何时保留相关子查询反而是最优选择。学完本节，你将能准确判断一个相关子查询该不该改写。

前置知识

阅读本节前，建议先了解：IN 与 NOT IN 子查询

基础概念

相关子查询（Correlated Subquery） 指内部引用了外层查询列的子查询。它不能独立求值，必须对外层的每一行执行一次，因此执行次数与外表行数成正比。

概念	术语	说明
相关性	Correlation	子查询引用外层列
外层引用	Outer Reference	子查询中指向外层的列引用
子计划	SubPlan	计划中每个外层行执行一次的节点
初始化计划	InitPlan	只执行一次的独立子查询节点
逐行求值	Per-Row Evaluation	外层每行都触发一次内层计算
侧向连接	Lateral Join	相关子查询的显式连接写法
去关联	Decorrelation	优化器移除相关性的改写
提升	Pull-up	把子查询展开为半连接/反连接

判断相关性的唯一标准：子查询内部是否出现外层表的列。

子查询形态	是否相关	执行次数	计划节点
(SELECT avg(amount) FROM orders)	❌ 独立	1	InitPlan
(SELECT sum(amount) FROM orders WHERE user_id = u.id)	✅ 相关	外层行数	SubPlan
EXISTS (SELECT 1 FROM orders WHERE user_id = u.id)	✅ 相关	外层行数（但可提升）	提升后为 Semi Join
(SELECT max(id) FROM orders)	❌ 独立	1	InitPlan

语法与用法

相关子查询出现在表达式与谓词中：

-- 在 SELECT 列表中对每行求值
SELECT 列, (SELECT 聚合 FROM 内表 WHERE 内表.列 = 外表.列) AS 别名
FROM 外表 AS 别名;

-- 在 WHERE 中作为比较右值
SELECT ... FROM 外表 WHERE 列 运算符 (SELECT 值 FROM 内表 WHERE 内表.列 = 外表.列);

-- 在 EXISTS 中（最容易被优化器去关联）
SELECT ... FROM 外表 WHERE EXISTS (SELECT 1 FROM 内表 WHERE 内表.列 = 外表.列);

-- 在 HAVING 中引用分组列
SELECT 分组列, count(*) FROM 外表 GROUP BY 分组列
HAVING count(*) > (SELECT 阈值 FROM 阈值表 WHERE 阈值表.类别 = 外表.分组列);

准备本节贯穿使用的示例表：

-- 用户表
CREATE TABLE users (
  id       INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name     TEXT    NOT NULL,
  city     TEXT    NOT NULL,
  vip      BOOLEAN NOT NULL DEFAULT false
);

-- 订单表
CREATE TABLE orders (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no   TEXT           NOT NULL UNIQUE,
  user_id    INTEGER,
  amount     NUMERIC(10, 2) NOT NULL CHECK (amount >= 0),
  status     TEXT           NOT NULL,
  created_at DATE           NOT NULL
);

-- 订单明细
CREATE TABLE order_items (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id   INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  qty        INTEGER NOT NULL CHECK (qty > 0),
  price      NUMERIC(10, 2) NOT NULL
);

-- 城市目标表：用于 HAVING 中的相关子查询
CREATE TABLE city_targets (
  city       TEXT    PRIMARY KEY,
  min_orders INTEGER NOT NULL
);

INSERT INTO users (name, city, vip) VALUES
  ('alice', 'beijing',  true),
  ('bob',   'shanghai', false),
  ('carol', 'shenzhen', true),
  ('dave',  'chengdu',  false),
  ('erin',  'beijing',  false);

INSERT INTO orders (order_no, user_id, amount, status, created_at) VALUES
  ('SO-1001', 1,    607.00, 'paid',    DATE '2026-01-05'),
  ('SO-1002', 2,    199.00, 'paid',    DATE '2026-01-08'),
  ('SO-1003', 1,    518.00, 'paid',    DATE '2026-02-09'),
  ('SO-1004', 3,    195.00, 'pending', DATE '2026-02-02'),
  ('SO-1005', 2,    490.00, 'paid',    DATE '2026-02-15'),
  ('SO-1006', NULL,  89.00, 'paid',    DATE '2026-03-01'),
  ('SO-1007', 3,    350.00, 'pending', DATE '2026-03-11');

INSERT INTO order_items (order_id, product_id, qty, price) VALUES
  (1, 1, 1, 349.00),
  (1, 2, 2, 129.00),
  (2, 3, 1, 199.00),
  (3, 1, 1, 349.00),
  (3, 3, 1, 199.00),
  (5, 1, 1, 349.00),
  (5, 3, 1, 199.00);

INSERT INTO city_targets (city, min_orders) VALUES
  ('beijing',  2),
  ('shanghai', 2),
  ('shenzhen', 2),
  ('chengdu',  1);

CREATE INDEX idx_orders_user_id       ON orders (user_id);
CREATE INDEX idx_order_items_order_id ON order_items (order_id);

ANALYZE users, orders, order_items, city_targets;

详细说明

SubPlan 与 InitPlan 的识别

这是判断相关子查询性能的关键技能：

-- 相关子查询：外层每行执行一次
EXPLAIN (ANALYZE, BUFFERS)
SELECT
  u.name,
  (SELECT count(*) FROM orders AS o WHERE o.user_id = u.id) AS order_cnt
FROM users AS u
ORDER BY u.name;

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Sort  (cost=... ) (actual time=0.098..0.100 rows=5 loops=1)
   Sort Key: u.name
   ->  Seq Scan on users u  (cost=0.00..12.70 rows=370 width=32) (actual time=0.011..0.027 rows=5 loops=1)
         SubPlan 1
           ->  Aggregate  (cost=17.17..17.18 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=5)
                 ->  Index Only Scan using idx_orders_user_id on orders o  (cost=0.28..17.15 rows=3 width=0)
                                                                               (actual time=0.005..0.006 rows=1 loops=5)
                       Index Cond: (user_id = u.id)
                       Heap Fetches: 0
 Execution Time: 0.211 ms

 name  | order_cnt
-------+-----------
 alice |         2
 bob   |         2
 carol |         2
 dave  |         0
 erin  |         0
(5 rows)

关键读法：

• SubPlan 1 出现在 Seq Scan on users 的内部，说明它绑定在外层节点上。
• loops=5 出现在 Aggregate 与 Index Only Scan 上，等于外层行数 5。
• 若外层是 10 万行，loops 就是 10 万，内层索引探测 10 万次。

-- 独立子查询：只执行一次
EXPLAIN (ANALYZE, BUFFERS)
SELECT
  u.name,
  (SELECT count(*) FROM orders) AS total_cnt
FROM users AS u
ORDER BY u.name;

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Sort  (cost=... ) (actual time=0.077..0.079 rows=5 loops=1)
   InitPlan 1 (returns $0)
     ->  Aggregate  (cost=17.15..17.16 rows=1 width=8) (actual time=0.040..0.041 rows=1 loops=1)
           ->  Seq Scan on orders  (cost=0.00..13.70 rows=370 width=0) (actual time=0.009..0.023 rows=7 loops=1)
   ->  Seq Scan on users u  (cost=0.00..12.70 rows=370 width=32) (actual time=0.009..0.011 rows=5 loops=1)
 Execution Time: 0.104 ms

InitPlan 的 loops=1 是它与 SubPlan 的决定性区别。两种节点的对照：

对比项	InitPlan	SubPlan
执行次数	1 次	外层行数次
位置	计划顶部独立段落	挂在某个节点内部
是否相关	否	是
复杂度	O(1) 摊销	O(N × 内层代价)
识别关键词	loops=1	loops = 外层行数

复杂度分析与改写

相关子查询的复杂度是外层行数 N 与内层单次成本 M 的乘积。改写为连接后复杂度降为 O(N + M)：

-- 原写法：SubPlan，复杂度 O(N × M)
SELECT u.name, (SELECT sum(o.amount) FROM orders AS o WHERE o.user_id = u.id) AS total
FROM users AS u;

-- 改写 1：聚合 + 外连接，一次扫描
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name, sum(o.amount) AS total
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
GROUP BY u.id, u.name
ORDER BY u.name;

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 GroupAggregate  (cost=... ) (actual time=0.104..0.116 rows=5 loops=1)
   Group Key: u.id, u.name
   ->  Sort  (cost=... ) (actual time=... rows=7 loops=1)
         Sort Key: u.id, u.name
         ->  Hash Left Join  (cost=... ) (actual time=... rows=7 loops=1)
               Hash Cond: (o.user_id = u.id)          -- 一次哈希，无逐行重复

 name  |  total
-------+---------
 alice | 1125.00
 bob   |  689.00
 carol |  545.00
 dave  |
 erin  |
(5 rows)

-- 改写 2：窗口函数，避免 GROUP BY 后丢失明细列
SELECT DISTINCT
  u.name,
  sum(o.amount) OVER (PARTITION BY u.id) AS total
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
ORDER BY u.name;

-- 改写 3：LATERAL，语义最接近"每个用户取一组数据"
SELECT u.name, t.total
FROM users AS u
LEFT JOIN LATERAL (
  SELECT sum(amount) AS total FROM orders o WHERE o.user_id = u.id
) AS t ON true
ORDER BY u.name;

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=... ) (actual time=0.078..0.108 rows=5 loops=1)
   ->  Seq Scan on users u  (cost=... ) (actual time=... rows=5 loops=1)
   ->  Subquery Scan on t  (cost=... ) (actual time=... rows=1 loops=5)
         ->  Aggregate  (cost=... ) (actual time=... rows=1 loops=5)
               ->  Index Only Scan using idx_orders_user_id on orders o  (cost=... )
                     Index Cond: (user_id = u.id)

三种改写与原始写法的对比：

方案	复杂度	适用场景	是否保留明细列
相关子查询	O(N × M)	外层行数很小	✅
聚合 + 外连接	O(N + M)	需要分组聚合	❌（被聚合掉）
窗口函数	O(N + M)	需要保留明细	✅
LATERAL	O(N × log M)	每组取 Top-N / 多列	✅

相关子查询的必要场景

并非所有相关子查询都应改写。以下场景中它是最优解：

-- 场景 1：每行取"最新一条"，用索引 + LIMIT 1 极快
EXPLAIN (ANALYZE, BUFFERS)
SELECT
  u.name,
  (SELECT o.amount FROM orders AS o
   WHERE o.user_id = u.id
   ORDER BY o.created_at DESC, o.id DESC
   LIMIT 1) AS last_amount
FROM users AS u
ORDER BY u.name;

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Sort  (cost=... ) (actual time=0.121..0.125 rows=5 loops=1)
   Sort Key: u.name
   ->  Seq Scan on users u  (cost=... ) (actual time=... rows=5 loops=1)
         SubPlan 1
           ->  Limit  (cost=... ) (actual time=0.007..0.007 rows=1 loops=5)
                 ->  Index Scan using idx_orders_user_created on orders o  (cost=... )
                                                                          (actual time=0.005..0.005 rows=1 loops=5)
                       Index Cond: (user_id = u.id)
                       Filter: ...

-- 需要这个复合索引支撑（ORDER BY 列 + 唯一列）
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC, id DESC);

 name  | last_amount
-------+-------------
 alice |      518.00
 bob   |      490.00
 carol |      350.00
 dave  |
 erin  |
(5 rows)

-- 场景 2：子查询结果小于外层行数且代价极低时的 EXISTS
SELECT u.name
FROM users AS u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 600);

 name
------
 alice
(1 row)

-- 场景 3：HAVING 中引用分组列的相关子查询（无法直接改写为连接）
SELECT
  u.city,
  count(*) AS order_cnt
FROM users  AS u
JOIN orders AS o ON o.user_id = u.id
GROUP BY u.city
HAVING count(*) >= (
  SELECT ct.min_orders FROM city_targets AS ct WHERE ct.city = u.city
);

   city   | order_cnt
----------+-----------
 beijing  |         2
 shanghai |         2
 shenzhen |         2

注意这里的 u.city 是分组列，子查询相关于分组结果。

实战示例

下面用完整流程对比相关子查询与改写方案的性能。

-- 步骤 1：制造较大数据量以观察差异
INSERT INTO users (name, city, vip)
SELECT 'user_' || i, (ARRAY['beijing','shanghai','shenzhen','chengdu'])[1 + (i % 4)], (i % 10 = 0)
FROM generate_series(6, 20000) AS i;

INSERT INTO orders (order_no, user_id, amount, status, created_at)
SELECT 'SO-' || lpad(i::text, 8, '0'), 1 + (i % 20000), (i % 1000)::numeric / 100,
       CASE WHEN i % 20 = 0 THEN 'pending' ELSE 'paid' END, DATE '2026-01-01' + (i % 90)
FROM generate_series(1, 100000) AS i;

ANALYZE users, orders;

-- 步骤 2：记录相关子查询基线耗时
EXPLAIN (ANALYZE, BUFFERS)
SELECT
  u.name,
  (SELECT sum(o.amount) FROM orders AS o WHERE o.user_id = u.id) AS total
FROM users AS u
WHERE u.city = 'chengdu';

 Execution Time: 42.318 ms       -- 5000 行外层 × 内层索引探测

-- 步骤 3：改写为聚合 + 连接
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name, sum(o.amount) AS total
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE u.city = 'chengdu'
GROUP BY u.id, u.name;

 Execution Time: 12.684 ms      -- 一次哈希连接

-- 步骤 4：用 EXPLAIN 对比节点类型（SubPlan vs Hash Join）
-- 步骤 2 的输出中出现 SubPlan 且 loops≈5000
-- 步骤 3 的输出中出现 Hash Left Join + GroupAggregate

-- 步骤 5：需要"取每组最新一条"时，用 LATERAL 替代相关子查询
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name, t.order_no, t.amount
FROM users AS u
LEFT JOIN LATERAL (
  SELECT o.order_no, o.amount
  FROM orders AS o
  WHERE o.user_id = u.id
  ORDER BY o.created_at DESC, o.id DESC
  LIMIT 1
) AS t ON true
WHERE u.city = 'chengdu'
ORDER BY u.name
LIMIT 10;

 Execution Time: 0.312 ms       -- 配合复合索引，只取 10 行输出

-- 步骤 6：确认改写的行数完全一致
WITH corr AS (
  SELECT u.id, (SELECT sum(o.amount) FROM orders o WHERE o.user_id = u.id) AS total
  FROM users u WHERE u.city = 'chengdu'
),
join_ver AS (
  SELECT u.id, sum(o.amount) AS total
  FROM users u LEFT JOIN orders o ON o.user_id = u.id
  WHERE u.city = 'chengdu'
  GROUP BY u.id
)
SELECT
  (SELECT count(*) FROM corr)     AS corr_rows,
  (SELECT count(*) FROM join_ver) AS join_rows,
  (SELECT count(*) FROM corr c JOIN join_ver j ON j.id = c.id
     WHERE (c.total, j.total) IS DISTINCT FROM (j.total, j.total)) AS mismatch;

 corr_rows | join_rows | mismatch
-----------+-----------+----------
      5001 |      5001 |        0

-- 步骤 7：为相关子查询场景补上复合索引
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC, id DESC);
ANALYZE orders;
-- 建索引后，"每用户最新一条"的相关子查询可用 Index Scan，loops 大但单次成本极低

注意事项

• 不要在外层行数很大时使用 SELECT 列表中的相关子查询求聚合值。 应改写为聚合连接。

-- 错误！N 行外层触发 N 次聚合
SELECT u.name, (SELECT sum(o.amount) FROM orders o WHERE o.user_id = u.id) FROM users u;

-- 正确
SELECT u.name, sum(o.amount) FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.id, u.name;

• 不要忽略 SubPlan 的 loops 乘积。 评估成本时必须把 loops 乘进内层代价。

-- 内层 Index Scan 单次 0.005ms，但 loops=5000 → 累计 25ms

• 不要用相关子查询替代 EXISTS。 后者可被去关联为半连接，前者不会。

-- 不推荐：相关标量子查询，每个外层行都要判断
SELECT u.name FROM users u WHERE (SELECT count(*) FROM orders o WHERE o.user_id = u.id) > 0;

-- 推荐：EXISTS 转为 Semi Join
SELECT u.name FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

• 相关子查询中不能引用外层别名的限定列时，检查别名是否写对。 外层别名需在子查询可见作用域内。
• 不要在相关子查询中写 ORDER BY 却没有 LIMIT。 排序结果会被丢弃。
• 不要在 HAVING 的嵌套子查询中引用非分组列。 会报 must appear in the GROUP BY clause。

-- 错误！u.name 不是分组列
SELECT u.city FROM users u GROUP BY u.city HAVING count(*) > (SELECT min_orders FROM city_targets ct WHERE ct.city = u.name);

-- 正确：引用分组列 u.city
SELECT u.city FROM users u GROUP BY u.city HAVING count(*) >= (SELECT min_orders FROM city_targets ct WHERE ct.city = u.city);

• 不要忘记为相关列建索引。 相关子查询的收益完全依赖内层的索引探测。

CREATE INDEX idx_orders_user_id ON orders (user_id);

• 不要在相关子查询中返回多行。 标量位置的子查询多行会直接报错。

最佳实践

1. 先用 EXPLAIN (ANALYZE, BUFFERS) 确认是否为 SubPlan。 出现 SubPlan 且 loops 大，就必须评估改写。

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name, (SELECT count(*) FROM orders o WHERE o.user_id = u.id) FROM users u;

2. 求聚合值的相关子查询一律改写为聚合 + 外连接。

SELECT u.name, sum(o.amount) AS total
FROM users u LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

3. 需要保留明细列时用窗口函数。

SELECT DISTINCT u.name, sum(o.amount) OVER (PARTITION BY u.id) AS total
FROM users u LEFT JOIN orders o ON o.user_id = u.id;

4. "每组取 Top-N / 多列"用 LATERAL + 复合索引。

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC, id DESC);

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

5. 外层行数小且内层有索引时，保留相关子查询反而最简洁高效。

-- 单行外层：只执行 1 次子查询
SELECT (SELECT count(*) FROM orders WHERE user_id = 42) AS cnt;

6. 为相关列建立覆盖所需列的复合索引，让内层用 Index Only Scan 避免回表。

CREATE INDEX idx_orders_user_amount ON orders (user_id, amount);

7. 改写后用 IS NOT DISTINCT FROM 验证结果一致性。

SELECT a.total IS NOT DISTINCT FROM b.total AS same FROM (...) a JOIN (...) b ON a.id = b.id;

下一节

继续学习：子查询优化

参考链接

• Subquery Expressions
• Scalar Subqueries
• Using EXPLAIN
• Performance Tips