子查询优化

本节讲解优化器如何处理子查询：什么情况下子查询会被「提升」成连接、什么情况下会退化成逐行执行、InitPlan 与 SubPlan 的性能含义，以及面对慢子查询时的系统化改写清单。学完本节，你将能看着 EXPLAIN 输出判断一个子查询是否值得改写。

前置知识

阅读本节前，建议先了解：相关子查询（Correlated Subquery）

基础概念

子查询优化 的核心是「去关联（Decorrelation）」：优化器尝试把子查询里对外层列的引用消掉，从而把「外层每行执行一次内层」的嵌套循环，改写成一次性的连接操作。

概念	术语	说明
提升	Pull-up	把子查询整体展开进外层查询
去关联	Decorrelation	消除子查询对外层列的引用
半连接	Semi Join	只判断「存在匹配」，不复制右表行
反连接	Anti Join	只判断「不存在匹配」
初始化计划	InitPlan	不相关子查询，只执行一次
子计划	SubPlan	相关子查询，外层每行执行一次
子查询扫描	Subquery Scan	派生表未被展开时的节点
物化	Materialization	子查询结果先算完再供外层使用

先建立一张「子查询形态 → 计划节点 → 复杂度」的对照表，它是本节所有判断的基础：

子查询写法	常见计划节点	执行次数	复杂度
(SELECT max(x) FROM t) 不相关标量	InitPlan	1	O(M)
(SELECT sum(x) FROM t WHERE t.k = o.k) 相关标量	SubPlan	外层行数 N	O(N·M)
WHERE EXISTS (SELECT 1 FROM t WHERE t.k = o.k)	Hash Semi Join / Nested Loop Semi Join	1 次连接	O(N+M)
WHERE NOT EXISTS (...)	Hash Anti Join	1 次连接	O(N+M)
WHERE k IN (SELECT k FROM t)	Hash Semi Join（右表先去重）	1 次连接	O(N+M)
WHERE k NOT IN (SELECT k FROM t)	Hash Anti Join 或 Hash Filter: NOT (hashed SubPlan)	1 次或 N 次	视提升成功与否
FROM (SELECT ...) AS d 无 LIMIT	直接展开，通常无独立节点	1	O(N+M)
FROM (SELECT ... LIMIT 10) AS d	Subquery Scan	1	O(M)+O(10)

关键结论：InitPlan 是好事，SubPlan 出现在大表外层通常是坏事，Semi Join / Anti Join 是优化器去掉关联后的理想形态——它既保留了「只判断存在性」的语义，又只扫描一次右表。

语法与用法

用于诊断子查询执行方式的标准手段：

-- 查看计划与真实耗时（含缓冲命中）
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

-- 提高规划器对联接的偏好（让优化器更愿意改写为连接）
SET join_collapse_limit = 8;

-- 允许优化器把 EXISTS / IN 提升为半连接
SET enable_hashjoin = on;
SET enable_nestloop = on;

-- 关闭提升做对照实验（观察退化后的计划）
SET enable_seqscan = off;

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

-- 用户表：2 万行
CREATE TABLE users (
  id   INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT NOT NULL
);

-- 订单表：10 万行
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
);

-- 商品表：500 行
CREATE TABLE products (
  id    INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku   TEXT           NOT NULL UNIQUE,
  title TEXT           NOT NULL,
  price NUMERIC(10, 2) NOT NULL
);

-- 造数
INSERT INTO users (name, city)
SELECT '用户' || g, (ARRAY['北京','上海','广州','深圳'])[1 + g % 4]
FROM generate_series(1, 20000) AS g;

INSERT INTO orders (order_no, user_id, amount, status, created_at)
SELECT
  'SO-' || lpad(g::text, 6, '0'),
  CASE WHEN g % 997 = 0 THEN NULL ELSE 1 + g % 20000 END,
  (random() * 1000)::numeric(10, 2),
  (ARRAY['paid','shipped','done','closed'])[1 + g % 4],
  DATE '2025-01-01' + (g % 365)
FROM generate_series(1, 100000) AS g;

INSERT INTO products (sku, title, price)
SELECT 'SKU-' || lpad(g::text, 4, '0'), '商品' || g, (random() * 500)::numeric(10, 2)
FROM generate_series(1, 500) AS g;

ANALYZE users, orders, products;

详细说明

子查询提升（Pull-up）

当「提升」在语义上安全时，优化器会把子查询拉平为连接。判断标准不是子查询出现在 SELECT 还是 WHERE，而是改写后结果集是否与原语义完全一致。

子查询位置	是否可提升	原因
WHERE EXISTS (...)	✅ 通常可	语义等价于半连接
WHERE IN (SELECT 单列 ...)	✅ 通常可	等价于半连接（右表自动去重）
WHERE NOT EXISTS (...)	✅ 通常可	等价于反连接
WHERE NOT IN (SELECT 可空列 ...)	⚠️ 受限	NULL 语义使反连接改写不平凡
SELECT (SELECT 相关值 ...)	❌ 一般不提升	标量输出需保持每行一个值
FROM (SELECT ... LIMIT n) d	❌ 不提升	LIMIT 阻断了谓词下推

用一个可验证的例子观察提升效果：

-- 相关标量查询：写起来直白，但外层每行都要执行内层
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.id, (SELECT sum(o.amount) FROM orders o WHERE o.user_id = u.id) AS total
FROM users u
WHERE u.city = '北京';

                                          QUERY PLAN
--------------------------------------------------------------------------------------------
 Index Scan using idx_users_city on users u  (cost=0.29..6985.42 rows=5000 width=36)
                                             (actual time=0.021..12.474 rows=5000 loops=1)
   Index Cond: (city = '北京'::text)
   SubPlan 1
     ->  Aggregate  (cost=1.31..1.32 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=5000)
           ->  Index Only Scan using idx_orders_user_amount on orders o
                 (cost=0.42..1.30 rows=5 width=16) (actual time=0.001..0.001 rows=5 loops=5000)
                 Index Cond: (user_id = u.id)
                 Heap Fetches: 0
 Planning Time: 0.312 ms
 Execution Time: 13.207 ms

注意 SubPlan 1 ... loops=5000：内层聚合被反复执行了 5000 次（外层过滤后的行数）。把同样的语义改写成连接：

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.id, coalesce(o.total, 0) AS total
FROM users u
LEFT JOIN (
  SELECT user_id, sum(amount) AS total FROM orders GROUP BY user_id
) AS o ON o.user_id = u.id
WHERE u.city = '北京';

                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Hash Left Join  (cost=2541.00..3421.10 rows=5000 width=36) (actual time=32.5..36.1 rows=5000 loops=1)
   Hash Cond: (u.id = o.user_id)
   ->  Index Scan using idx_users_city on users u  (actual time=0.02..2.11 rows=5000 loops=1)
         Index Cond: (city = '北京'::text)
   ->  Hash  (cost=1916.00..1916.00 rows=100000 width=12) (actual time=28.4..28.4 rows=100000 loops=1)
         ->  HashAggregate  (cost=1916.00..1916.00 rows=100000 width=12) (actual time=21.7..24.9 rows=100000)
               Group Key: orders.user_id
               ->  Seq Scan on orders  (actual time=0.01..9.31 rows=100000 loops=1)
 Execution Time: 38.402 ms

改写后内层聚合只算一次（loops=1），外层 5000 行只做哈希探测。当外层行数很大时，改写收益会急剧放大。

三类节点的性能含义

节点	出现时机	好/坏	判读要点
InitPlan	不相关子查询	好	loops=1，结果被缓存复用
SubPlan	相关子查询未被提升	视情况	看 loops= 是否等于外层行数
Subquery Scan	派生表未被展开	视情况	看其上层是否有 Filter 而非 Index Cond

InitPlan 会把它算出的值当作参数缓存，后续引用不再重复计算：

EXPLAIN (ANALYZE)
SELECT id, amount FROM orders WHERE amount > (SELECT avg(amount) FROM orders);

                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Seq Scan on orders  (cost=1908.00..3217.00 rows=33333 width=12) (actual time=30.1..35.9 rows=50042 loops=1)
   Filter: (amount > $0)
   InitPlan 1 (returns $0)
     ->  Aggregate  (cost=1916.00..1916.01 rows=1 width=32) (actual time=21.6..21.6 rows=1 loops=1)
           ->  Seq Scan on orders  (actual time=0.01..9.31 rows=100000 loops=1)
 Execution Time: 36.104 ms

InitPlan 1 (returns $0) 说明这个 avg 只算了一次，外层 Filter 直接复用 $0。这类子查询不需要改写。

Subquery Scan 则常意味着谓词没能下推。对比下面两段：

-- 无 LIMIT：派生表被展开，谓词直接下推到索引
EXPLAIN (ANALYZE)
SELECT d.order_no, d.amount
FROM (SELECT order_no, amount, user_id FROM orders) AS d
WHERE d.user_id = 42;

                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Index Scan using idx_orders_user_amount on orders  (actual time=0.02..0.06 rows=5 loops=1)
   Index Cond: (user_id = 42)
 Execution Time: 0.092 ms

-- 有 LIMIT：派生表被物化，外层只能整表过滤
EXPLAIN (ANALYZE)
SELECT d.order_no, d.amount
FROM (SELECT order_no, amount, user_id FROM orders LIMIT 100) AS d
WHERE d.user_id = 42;

                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Subquery Scan on d  (cost=0.00..5.00 rows=5 width=18) (actual time=0.02..0.11 rows=1 loops=1)
   Filter: (d.user_id = 42)
   Rows Removed by Filter: 99
   ->  Limit  (actual time=0.01..0.07 rows=100 loops=1)
         ->  Seq Scan on orders  (actual time=0.01..0.06 rows=100 loops=1)
 Execution Time: 0.142 ms

看到 Subquery Scan on d 加 Rows Removed by Filter，就说明外层条件是在子查询结果之后才过滤的，无法用索引。

半连接与反连接的提升条件

EXISTS / IN 之所以能被提升，是因为它们只关心「有没有匹配行」，不需要右表的列，也不要求右表行的数量。

-- EXISTS：提升为 Hash Semi Join
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.id, u.name FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 900);

                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Hash Semi Join  (cost=3908.00..4210.20 rows=1820 width=25) (actual time=31.2..41.8 rows=1832 loops=1)
   Hash Cond: (u.id = o.user_id)
   ->  Seq Scan on users u  (actual time=0.01..3.20 rows=20000 loops=1)
   ->  Hash  (cost=3880.00..3880.00 rows=2240 width=4) (actual time=28.9..28.9 rows=2243 loops=1)
         Buckets: 4096  Batches: 1  Memory Usage: 145kB
         ->  Seq Scan on orders o  (actual time=0.01..24.9 rows=2243 loops=1)
               Filter: (amount > 900::numeric)
               Rows Removed by Filter: 97757
 Execution Time: 42.310 ms

Hash Semi Join 意味着右表即使有 100 行匹配，也只会让左表对应行输出一次——这正是 EXISTS 的语义。

-- NOT EXISTS：提升为 Hash Anti Join
EXPLAIN (ANALYZE)
SELECT u.id, u.name FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Hash Anti Join  (cost=3108.00..3410.20 rows=980 width=25) (actual time=28.4..35.1 rows=991 loops=1)
   Hash Cond: (u.id = o.user_id)
   ->  Seq Scan on users u  (actual time=0.01..3.12 rows=20000 loops=1)
   ->  Hash  (cost=1916.00..1916.00 rows=100000 width=4) (actual time=25.1..25.1 rows=100000 loops=1)
         ->  Seq Scan on orders  (actual time=0.01..9.31 rows=100000 loops=1)
 Execution Time: 35.702 ms

Anti Join 是「左表有、右表无」的最优形态：只需对右表建一次哈希表，然后逐行探测左表。

NOT IN 无法提升时的退化

NOT IN 的语义里藏着 NULL 陷阱：只要右表某行返回 NULL，整个 NOT IN 结果就可能恒为 NULL。这个语义障碍会让优化器放弃提升。

-- 右列可空：优化器不敢改写成反连接
EXPLAIN (ANALYZE)
SELECT u.id, u.name FROM users u
WHERE u.id NOT IN (SELECT o.user_id FROM orders o WHERE o.user_id IS NULL);

                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Seq Scan on users u  (cost=0.00..2065.00 rows=10 width=25) (actual time=0.02..0.09 rows=0 loops=1)
   Filter: (NOT (hashed SubPlan 1))
   Rows Removed by Filter: 20000
   SubPlan 1
     ->  Seq Scan on orders o  (actual time=0.01..0.03 rows=100 loops=1)
           Filter: (user_id IS NULL)
 Execution Time: 0.121 ms

Filter: (NOT (hashed SubPlan 1)) 是退化的信号：虽然子查询被哈希缓存（hashed），但它是在外层过滤阶段逐行探测的，而不是由连接算子统一处理。更关键的是——当子查询返回集合中含 NULL 时，整个 NOT IN 会返回 0 行：

-- 子查询全表（含 NULL 的 user_id）：结果为空
SELECT count(*) FROM users u WHERE u.id NOT IN (SELECT user_id FROM orders);

 count
-------
     0
(1 row)

-- 改用 NOT EXISTS：语义正确，且提升为 Anti Join
SELECT count(*) FROM users u WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

 count
-------
   991
(1 row)

结论：只要子查询列可能为 NULL，就把 NOT IN 换成 NOT EXISTS——既避免语义错误，又得到 Anti Join 的性能。

相关子查询的改写清单

面对一个慢的相关子查询，按下面的顺序逐个尝试：

改写手法	适用场景	目标计划
改 EXISTS / NOT EXISTS	只判断存在性	Semi Join / Anti Join
改 LEFT JOIN ... IS NULL	反连接且需要右表列	Hash Anti Join（PG 会自动转）
聚合 + 连接	子查询是 sum/count/max	Hash Left Join + HashAggregate
窗口函数	需要「每组取最新/排名」	WindowAgg + Index Scan
LATERAL	需要「每组取前 N 条」	Nested Loop Left Join
DISTINCT ON	需要「每组去重取首行」	Unique + Sort

用「每组取最新一条订单」这个经典问题展示三种改写：

-- 原写法：相关子查询 + max，外层每行执行一次
SELECT u.id, u.name,
       (SELECT o.order_no FROM orders o
        WHERE o.user_id = u.id ORDER BY o.created_at DESC, o.id DESC LIMIT 1) AS latest
FROM users u WHERE u.city = '上海';

-- 改写一：DISTINCT ON 先取出每个用户的最新订单，再连接
SELECT u.id, u.name, t.order_no AS latest
FROM users u
LEFT JOIN (
  SELECT DISTINCT ON (user_id) user_id, order_no
  FROM orders ORDER BY user_id, created_at DESC, id DESC
) AS t ON t.user_id = u.id
WHERE u.city = '上海';

-- 改写二：窗口函数编号后取第 1 行
SELECT u.id, u.name, t.order_no AS latest
FROM users u
LEFT JOIN (
  SELECT user_id, order_no,
         row_number() OVER (PARTITION BY user_id ORDER BY created_at DESC, id DESC) AS rn
  FROM orders
) AS t ON t.user_id = u.id AND t.rn = 1
WHERE u.city = '上海';

-- 改写三：LATERAL，靠索引精确取一组一条
SELECT u.id, u.name, t.order_no AS latest
FROM users u
LEFT JOIN LATERAL (
  SELECT o.order_no FROM orders 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 = '上海';

四者的计划形态完全不同：

写法	计划节点	扫描量	适用外层规模
相关子查询	SubPlan + Index Scan	外层行数次索引探测	外层很小
DISTINCT ON	Unique + Sort	全量排序一次	外层很大、内层也大
窗口函数	WindowAgg + Sort	全量排序一次	外层很大、需多列
LATERAL	Nested Loop Left Join + Limit	每组一次索引探测	外层中等、索引完备

实战示例

目标：找出「下单金额总和超过 5000 但从未有过退款（status = 'closed'）的用户」，并对比三种写法的耗时。

步骤 1：准备索引

CREATE INDEX idx_orders_user_amount ON orders (user_id, amount);
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
ANALYZE orders;

步骤 2：写法 A —— 相关子查询

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.id, u.name
FROM users u
WHERE (SELECT sum(o.amount) FROM orders o WHERE o.user_id = u.id) > 5000
  AND NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'closed');

                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Nested Loop Anti Join  (cost=1.30..20812.40 rows=476 width=25) (actual time=0.05..168.42 rows=4102 loops=1)
   ->  Seq Scan on users u  (actual time=0.01..3.10 rows=20000 loops=1)
         Filter: ((SubPlan 1) > 5000::numeric)
         Rows Removed by Filter: 15898
         SubPlan 1
           ->  Aggregate  (actual time=0.004..0.004 rows=1 loops=20000)
                 ->  Index Only Scan using idx_orders_user_amount on orders o
                       (actual time=0.002..0.003 rows=5 loops=20000)
                       Index Cond: (user_id = u.id)
   ->  Index Only Scan using idx_orders_user_status on orders o
         (actual time=0.003..0.004 rows=1 loops=4102)
         Index Cond: ((user_id = u.id) AND (status = 'closed'::text))
 Execution Time: 175.204 ms

SubPlan 1 ... loops=20000：即使外层被过滤掉了 15898 行，聚合仍然对全部 2 万用户执行了。这是典型的「过滤条件里含子查询 → 子查询无法先被过滤」。

步骤 3：写法 B —— 聚合 + 反连接

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.id, u.name
FROM users u
JOIN (SELECT user_id, sum(amount) AS total FROM orders GROUP BY user_id) AS s
  ON s.user_id = u.id AND s.total > 5000
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'closed');

                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Hash Anti Join  (cost=2541.00..3012.40 rows=476 width=25) (actual time=30.1..48.6 rows=4102 loops=1)
   Hash Cond: (orders.user_id = u.id)
   ->  Seq Scan on orders  (actual time=0.01..9.32 rows=100000 loops=1)
         Filter: (status = 'closed'::text)
         Rows Removed by Filter: 74995
   ->  Hash  (cost=2491.00..2491.00 rows=4000 width=25) (actual time=28.4..28.4 rows=4884 loops=1)
         ->  Hash Join  (cost=1916.00..2491.00 rows=4000 width=25) (actual time=27.9..28.1 rows=4884 loops=1)
               Hash Cond: (orders.user_id = u.id)
               ->  HashAggregate  (actual time=21.7..22.4 rows=19009 loops=1)
                     Group Key: orders.user_id
                     ->  Seq Scan on orders  (actual time=0.01..9.31 rows=100000 loops=1)
               ->  Hash  (actual time=3.10..3.10 rows=20000 loops=1)
                     ->  Seq Scan on users u  (actual time=0.01..3.10 rows=20000 loops=1)
 Execution Time: 49.812 ms

耗时从 175ms 降到约 50ms，且 loops=1——所有算子都只执行一次。

步骤 4：写法 C —— 条件聚合合并为单次扫描

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.id, u.name
FROM users u
JOIN (
  SELECT user_id,
         sum(amount)                                    AS total,
         count(*) FILTER (WHERE status = 'closed')       AS closed_cnt
  FROM orders GROUP BY user_id
) AS s ON s.user_id = u.id
WHERE s.total > 5000 AND s.closed_cnt = 0;

                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Hash Join  (cost=2166.00..2510.00 rows=4000 width=25) (actual time=26.1..32.4 rows=4102 loops=1)
   Hash Cond: (orders.user_id = u.id)
   ->  HashAggregate  (actual time=21.9..22.6 rows=19009 loops=1)
         Group Key: orders.user_id
         ->  Seq Scan on orders  (actual time=0.01..9.31 rows=100000 loops=1)
   ->  Hash  (actual time=3.10..3.10 rows=20000 loops=1)
         ->  Seq Scan on users u  (actual time=0.01..3.10 rows=20000 loops=1)
 Execution Time: 33.907 ms

用 count(*) FILTER (WHERE ...) 把「求和」和「是否存在 closed」一次性算完，orders 表只被扫描一次，耗时进一步降到约 34ms。

步骤 5：验证三种写法结果完全一致

WITH a AS (
  SELECT u.id FROM users u
  WHERE (SELECT sum(o.amount) FROM orders o WHERE o.user_id = u.id) > 5000
    AND NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'closed')
), b AS (
  SELECT u.id FROM users u
  JOIN (
    SELECT user_id, sum(amount) AS total,
           count(*) FILTER (WHERE status = 'closed') AS closed_cnt
    FROM orders GROUP BY user_id
  ) AS s ON s.user_id = u.id
  WHERE s.total > 5000 AND s.closed_cnt = 0
)
SELECT
  (SELECT count(*) FROM a) AS a_cnt,
  (SELECT count(*) FROM b) AS b_cnt,
  (SELECT count(*) FROM (SELECT id FROM a EXCEPT SELECT id FROM b) AS d) AS only_in_a,
  (SELECT count(*) FROM (SELECT id FROM b EXCEPT SELECT id FROM a) AS d) AS only_in_b;

 a_cnt | b_cnt | only_in_a | only_in_b
-------+-------+-----------+-----------
  4102 |  4102 |         0 |         0
(1 row)

两侧差集均为 0，改写等价性得到验证。

步骤 6：耗时汇总

写法	计划核心节点	执行耗时	相对提升
A 相关子查询	Nested Loop Anti Join + SubPlan loops=20000	175.2 ms	基线
B 聚合 + 反连接	Hash Anti Join + HashAggregate	49.8 ms	3.5×
C 条件聚合	Hash Join + HashAggregate	33.9 ms	5.2×

注意事项

1. 不要在 WHERE 的过滤谓词里放需要先聚合才能判断的子查询。 过滤在聚合之前执行，会导致聚合被全量计算。

-- 错误！SubPlan 对全部外层行执行，无法先过滤
SELECT u.id FROM users u
WHERE (SELECT sum(amount) FROM orders o WHERE o.user_id = u.id) > 5000
  AND u.city = '北京';

-- 正确：先缩小外层，再算聚合
SELECT u.id FROM users u
JOIN (SELECT user_id, sum(amount) AS total FROM orders GROUP BY user_id) AS s
  ON s.user_id = u.id
WHERE s.total > 5000 AND u.city = '北京';

2. NOT IN 遇到可空列会静默返回 0 行，务必改成 NOT EXISTS：

-- 错误！user_id 可能为 NULL，结果恒为空
SELECT count(*) FROM users u WHERE u.id NOT IN (SELECT user_id FROM orders);

-- 正确
SELECT count(*) FROM users u WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

3. 不要为了「好看」把派生表写成带 LIMIT 的形式，LIMIT 会阻止谓词下推，让 Index Cond 退化为 Filter。

4. SubPlan 不一定是错的。当外层过滤后只剩几十行、内层有精确索引时，逐行探测可能比全表哈希更快——此时 Nested Loop 的启动代价优势反而更重要。

5. 改写后一定要用 EXCEPT 求差集验证结果集，不要只看首几行。

最佳实践

1. 先看 loops=，再看耗时。 loops 远大于 1 且值很大时，优先考虑去关联。

2. 优先让 EXISTS / NOT EXISTS 被提升为 Semi Join / Anti Join，这是语义与性能的最优组合。

3. 聚合类子查询一律改写为「先聚合再连接」，把 SubPlan 变成一次 HashAggregate。

4. 把多个互相独立的存在性判断合并进一次聚合扫描，用 count(*) FILTER (WHERE ...) 或 bool_or(...)。

5. 为 LATERAL / 相关子查询的外键列建立复合索引，让内层能用 Index Only Scan：

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

6. 用 IS NOT DISTINCT FROM 或 EXCEPT 做改写前后的等价性回归，把验证写进测试脚本。

7. enable_* 开关只用于诊断：关掉某个算子看计划如何变化，从而判断瓶颈所在，但不要在生产中持久化这些设置。

下一节

继续学习：集合操作 UNION

参考链接

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