导语
子查询是 SQL 的利器，但也是性能陷阱的高发区。在磐维（PanWeiDB/openGauss）中，同样的业务逻辑，写成 IN、EXISTS、NOT IN、NOT EXISTS 或 JOIN，优化器可能生成截然不同的执行计划，性能差距可达数千倍。
本文将分章节剖析半连接（Semi Join）与反连接（Anti Join）的原理差异，通过真实案例展示如何改写 SQL 避开性能陷阱，并系统介绍分析函数（Window Function）在子查询优化中的妙用。
一、半连接（Semi Join）：IN / EXISTS
1.1 原理：找到匹配即返回，不会穷举子表
当优化器将其转换为 Semi Join（半连接）时，IN / EXISTS 找到匹配即返回，不会穷举子表。
半连接的核心特性是**“一旦发现匹配就停止对内表的当前扫描”**。它只关心"是否存在"，而不关心"有多少条"。这与普通 JOIN 有本质区别：普通 JOIN 会穷举所有匹配组合，可能导致结果集膨胀。
| 特性 | Semi Join（IN/EXISTS） | 普通 INNER JOIN |
|---|---|---|
| 匹配行为 | 找到第一条匹配即返回 | 穷举所有匹配 |
| 结果集 | 外表行不膨胀 | 可能笛卡尔积膨胀 |
| 适用场景 | 判断"是否存在" | 需要获取子表详细数据 |
1.2 案例：IN 与 INNER JOIN 的差异
业务场景：查询有供应记录的供应商。
方案 A：IN（半连接）
EXPLAIN ANALYZE
SELECT * FROM supplier
WHERE s_comment LIKE '%Customer%Complaints%'
AND s_suppkey IN (SELECT ps_suppkey FROM partsupp);
执行计划：
QUERY PLAN
--------------------------------------------------------------------------------
+ Nested Loop Semi Join (cost=0.00..2027.26 rows=5 width=144)
(actual time=0.351..18.784 rows=26 loops=1)
-> Seq Scan on supplier
Filter: ((s_comment)::text ~~ '%Customer%Complaints%'::text)
Rows Removed by Filter: 49974
- -> Index Only Scan using partsupp_n2 on partsupp (actual time=0.213..0.213 rows=26 loops=26)
Index Cond: (ps_suppkey = supplier.s_suppkey)
Heap Fetches: 0
Total runtime: 18.858 ms
方案 B：EXISTS（半连接）
EXPLAIN ANALYZE
SELECT * FROM supplier a
WHERE s_comment LIKE '%Customer%Complaints%'
AND EXISTS (SELECT b.ps_suppkey FROM partsupp b WHERE b.ps_suppkey = a.s_suppkey);
执行计划：
QUERY PLAN
--------------------------------------------------------------------------------
+ Nested Loop Semi Join (cost=0.00..2027.26 rows=5 width=144)
(actual time=0.526..18.643 rows=26 loops=1)
-> Seq Scan on supplier a
Filter: ((s_comment)::text ~~ '%Customer%Complaints%'::text)
Rows Removed by Filter: 49974
- -> Index Only Scan using partsupp_n2 on partsupp b (actual time=0.315..0.315 rows=26 loops=26)
Index Cond: (ps_suppkey = a.s_suppkey)
Heap Fetches: 0
Total runtime: 18.763 ms
方案 C：INNER JOIN + DISTINCT（普通连接）
EXPLAIN ANALYZE
SELECT DISTINCT a.* FROM supplier a
INNER JOIN partsupp b ON b.ps_suppkey = a.s_suppkey
WHERE a.s_comment LIKE '%Customer%Complaints%';
执行计划：
QUERY PLAN
--------------------------------------------------------------------------------
+ HashAggregate (cost=2060.22..2060.27 rows=5 width=144)
(actual time=20.920..20.922 rows=26 loops=1)
Group By Key: a.s_suppkey, a.s_name, a.s_address, ...
-> Nested Loop (cost=0.00..2053.25 rows=398 width=144)
(actual time=0.473..19.097 rows=2080 loops=1)
-> Seq Scan on supplier a
Filter: ((s_comment)::text ~~ '%Customer%Complaints%'::text)
Rows Removed by Filter: 49974
- -> Index Only Scan using partsupp_n2 on partsupp b (actual time=0.276..0.523 rows=2080 loops=26)
Index Cond: (ps_suppkey = a.s_suppkey)
Heap Fetches: 0
Total runtime: 21.020 ms
对比分析：
| 方案 | 连接类型 | 耗时 | 特点 |
|---|---|---|---|
| IN | Semi Join | 18.8 ms | 找到匹配即返回，不膨胀 |
| EXISTS | Semi Join | 18.7 ms | 与 IN 效果一致 |
| JOIN + DISTINCT | 普通 JOIN | 21.0 ms | 需先产生 2080 行再聚合去重 |
结论：在此案例中，IN 与 EXISTS 都被优化为 Semi Join，性能相当；而改写为 JOIN + DISTINCT 会导致子表被完全扫描（返回 2080 行再 DISTINCT），性能略差。
二、反连接（Anti Join）：NOT IN / NOT EXISTS
2.1 原理：NOT IN 的 NULL 陷阱与性能灾难
反连接（Anti Join）用于查找"不存在于子表"的数据。NOT IN 和 NOT EXISTS 看似等价，实则天差地别：
| 特性 | NOT IN | NOT EXISTS |
|---|---|---|
| NULL 处理 | 子表含 NULL 则返回空结果集 | 不受 NULL 影响 |
| 优化空间 | 难以优化为 Hash Anti Join | 可优化为 Hash Right Anti Join |
| 典型性能 | 极慢（NestLoop Anti Join） | 快（Hash Anti Join） |
NOT IN 的致命问题：当子查询结果中存在 NULL 时，NOT IN 的语义要求对 NULL 进行三值逻辑判断，导致优化器无法使用 Hash Join，只能退化为 NestLoop Anti Join。
2.2 案例：NOT IN 的性能灾难
业务场景：找出 t_tables 中有但 t_objects 中没有的记录。
SET try_vector_engine_strategy = off;
EXPLAIN ANALYZE
SELECT t.owner, t.table_name, t.last_analyzed
FROM t_tables t
WHERE (t.owner, t.table_name) NOT IN (
SELECT o.owner, o.object_name
FROM t_objects o
WHERE o.owner IS NOT NULL AND o.object_name IS NOT NULL
);
执行计划：
QUERY PLAN
--------------------------------------------------------------------------------
+ Nested Loop Anti Join (cost=0.00..147180.85 rows=413 width=164)
(actual time=68.896..33153.430 rows=1 loops=1)
Join Filter: ((((t.owner)::text = (o.owner)::text) OR (t.owner IS NULL) OR (o.owner IS NULL))
AND (((t.table_name)::text = (o.object_name)::text) OR (t.table_name IS NULL) OR (o.object_name IS NULL)))
Rows Removed by Join Filter: 120748743
-> Seq Scan on t_tables t (cost=0.00..136.54 rows=554 width=164)
(actual time=0.012..7.616 rows=2859 loops=1)
-> Materialize (cost=0.00..2197.43 rows=18902 width=352)
- (actual time=0.664..7399.987 rows=120751601 loops=2859)
-> Seq Scan on t_objects o (cost=0.00..2102.92 rows=18902 width=352)
(actual time=0.008..19.701 rows=86657 loops=1)
Filter: ((owner IS NOT NULL) AND (object_name IS NOT NULL))
Total runtime: 33153.983 ms
问题剖析：
- 优化器选择了
Nested Loop Anti Join - 内表
t_objects被 Materialize（物化） 后循环扫描 loops=2859×rows=86657≈ 2.4 亿次比较Rows Removed by Join Filter: 120748743（超过 1.2 亿行被过滤）- 耗时：33153 ms（33 秒）
2.3 改写：NOT EXISTS 实现 Hash Anti Join
EXPLAIN ANALYZE
SELECT t.owner, t.table_name, t.last_analyzed
FROM t_tables t
WHERE NOT EXISTS (
SELECT 1 FROM t_objects o
WHERE o.owner = t.owner AND o.object_name = t.table_name
);
执行计划：
QUERY PLAN
--------------------------------------------------------------------------------
+ Hash Right Anti Join (cost=144.85..2395.75 rows=416 width=164)
(actual time=34.108..34.119 rows=1 loops=1)
Hash Cond: (((o.owner)::text = (t.owner)::text) AND ((o.object_name)::text = (t.table_name)::text))
-> Seq Scan on t_objects o (cost=0.00..2102.92 rows=19092 width=352)
(actual time=0.005..11.439 rows=86657 loops=1)
-> Hash (cost=136.54..136.54 rows=554 width=164)
(actual time=1.979..1.979 rows=2859 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 448kB
-> Seq Scan on t_tables t (cost=0.00..136.54 rows=554 width=164)
(actual time=0.007..1.043 rows=2859 loops=1)
Total runtime: 34.231 ms
优化效果：
| 指标 | NOT IN | NOT EXISTS | 提升倍数 |
|---|---|---|---|
| 连接方式 | NestLoop Anti Join | Hash Right Anti Join | - |
| 耗时 | 33153 ms | 34 ms | 约 1000 倍 |
| 内存使用 | Materialize 膨胀 | Hash 448 KB | - |
核心差异：
NOT EXISTS的等值条件让优化器可以使用 Hash Anti Join- 小表
t_tables（2859 行）构建哈希表，大表t_objects（86657 行）探测 - 一次性哈希匹配，无需嵌套循环
2.4 小结：反连接改写原则
需要反连接时：
├── 优先使用 NOT EXISTS
├── 确保关联条件为等值（=）
└── 避免 NOT IN，尤其子查询可能返回 NULL 时
三、标量子查询优化与分析函数
3.1 标量子查询的问题：逐行执行
标量子查询（SELECT 出现在 SELECT 列表中）是最隐蔽的性能杀手。它会对外表的每一行执行一次子查询。
案例：查询客户及其所属国家名称。
EXPLAIN ANALYZE
SELECT c.c_custkey, c.c_name,
(SELECT n.n_name FROM tpch.nation n WHERE n.n_nationkey = c.c_nationkey)
FROM tpch.customer c;
执行计划：
QUERY PLAN
--------------------------------------------------------------------------------
Seq Scan on customer c (cost=0.00..6230678.00 rows=750000 width=27)
(actual time=2.434..2104.629 rows=750000 loops=1)
+ SubPlan 1
-> Index Scan using nation_pkey on nation n
(cost=0.00..8.27 rows=1 width=26)
- (actual time=948.574..1059.447 rows=750000 loops=750000)
Index Cond: (n_nationkey = c.c_nationkey)
Total runtime: 2171.243 ms
问题剖析：
customer表 75 万行SubPlan 1被执行了 750000 次（loops=750000）- 虽然
nation表只有 25 行，但索引扫描被重复调用了 75 万次 - 耗时：2171 ms
3.2 改写为 LEFT JOIN
EXPLAIN ANALYZE
SELECT c.c_custkey, c.c_name, n.n_name
FROM tpch.customer c
LEFT JOIN (SELECT n_nationkey, n_name FROM tpch.nation) n
ON n.n_nationkey = c.c_nationkey;
执行计划：
QUERY PLAN
--------------------------------------------------------------------------------
+ Hash Left Join (cost=1.56..39546.75 rows=750000 width=49)
(actual time=0.243..251.428 rows=750000 loops=1)
Hash Cond: (c.c_nationkey = n.n_nationkey)
-> Seq Scan on customer c (cost=0.00..30053.00 rows=750000 width=27)
(actual time=0.004..49.451 rows=750000 loops=1)
+ -> Hash (cost=1.25..1.25 rows=25 width=30)
(actual time=0.015..0.015 rows=25 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 258kB
-> Seq Scan on nation n (cost=0.00..1.25 rows=25 width=30)
- (actual time=0.005..0.007 rows=25 loops=1)
Total runtime: 286.984 ms
效果：从 2171 ms 降至 287 ms，提升约 7.5 倍。
3.3 分析函数（Window Function）是什么？
分析函数（窗口函数）是 SQL 中一种特殊的计算函数，它可以在不折叠数据行的前提下，对一组相关的数据行（称为"窗口"）进行聚合或排序计算。
与普通聚合函数的核心区别：
| 特性 | GROUP BY 聚合 | 分析函数（Window Function） |
|---|---|---|
| 输出行数 | N 行聚合为 1 行 | 输入 N 行，输出 N 行 |
| 是否保留原始行 | 否 | 是 |
| 语法标识 | 无特殊语法 | 必须带 OVER() 子句 |
| 典型用途 | 汇总统计 | 排名、累计、同组比较 |
基本语法：
函数名(列) OVER (
[PARTITION BY 分区列] -- 定义窗口分组
[ORDER BY 排序列] -- 定义窗口内排序
[ROWS/RANGE 子句] -- 定义窗口范围
)
常见分析函数：
ROW_NUMBER() OVER()：行号RANK() / DENSE_RANK() OVER()：排名SUM() OVER()：累计求和AVG() OVER()：移动平均MAX() / MIN() OVER()：窗口内最大/最小值LAG() / LEAD() OVER()：取前/后 N 行数据
在子查询优化中的核心价值：
分析函数可以在单次表扫描中完成"分组聚合 + 全局聚合"的双重计算，避免子查询对同一张表的二次访问。
3.4 案例：TPCH Q15 —— 用分析函数消除重复扫描
业务场景：revenue0 是视图，原 SQL 中两次访问该视图（一次关联，一次取 MAX）。
视图定义：
CREATE VIEW revenue0 (supplier_no, total_revenue) AS
SELECT l_suppkey, SUM(l_extendedprice * (1 - l_discount))
FROM lineitem
WHERE l_shipdate >= DATE '1996-01-01'
AND l_shipdate < DATE '1996-01-01' + INTERVAL '3' MONTH
GROUP BY l_suppkey;
原 SQL：
EXPLAIN ANALYZE
SELECT s_suppkey, s_name, s_address, s_phone, total_revenue
FROM supplier, revenue0
WHERE s_suppkey = supplier_no
AND total_revenue = (SELECT MAX(total_revenue) FROM revenue0)
ORDER BY s_suppkey;
执行计划（优化前）：
QUERY PLAN
--------------------------------------------------------------------------------
Sort (cost=2494746.17..2494750.63 rows=1785 width=103)
(actual time=20528.264..20528.264 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Aggregate (cost=1242030.74..1242030.75 rows=1 width=64)
(actual time=9717.251..9717.251 rows=1 loops=1)
-> HashAggregate (cost=1241990.58..1242008.43 rows=1785 width=48)
(actual time=9695.706..9711.610 rows=50000 loops=1)
Group By Key: lineitem.l_suppkey
-> Seq Scan on lineitem
Filter: ((l_shipdate >= '1996-01-01') AND (l_shipdate < '1996-04-01'))
Rows Removed by Filter: 28866784
-> Hash Join (cost=1250018.66..1252619.01 rows=1785 width=103)
(actual time=20517.474..20528.208 rows=1 loops=1)
Hash Cond: (supplier.s_suppkey = revenue0.supplier_no)
-> Seq Scan on supplier
-> Hash
-> Subquery Scan on revenue0
-> HashAggregate
Group By Key: lineitem.l_suppkey
Filter: (sum(...) = $0) -- 使用 InitPlan 的结果过滤
Rows Removed by Filter: 49999
-> Seq Scan on lineitem -- 第二次扫描 lineitem！
Filter: ((l_shipdate >= '1996-01-01') AND (l_shipdate < '1996-04-01'))
Rows Removed by Filter: 28866784
Total runtime: 20528.750 ms
问题剖析：
InitPlan 1中第一次全表扫描lineitem（扫描 2886 万行）求 MAX- 外层 Hash Join 中第二次全表扫描
lineitem生成 revenue0，再过滤出等于 MAX 的行 lineitem被扫描了两次，耗时 20.5 秒
优化方案：使用分析函数 MAX() OVER()，单次扫描完成。
EXPLAIN ANALYZE
SELECT s_suppkey, s_name, s_address, s_phone, total_revenue
FROM supplier,
(
SELECT t.*, MAX(total_revenue) OVER () AS max_total_revenue
FROM revenue0 t
) revenue0
WHERE s_suppkey = supplier_no
AND total_revenue = max_total_revenue
ORDER BY s_suppkey;
执行计划（优化后）：
QUERY PLAN
--------------------------------------------------------------------------------
Sort (cost=1242145.57..1242145.59 rows=9 width=103)
(actual time=10655.513..10655.513 rows=1 loops=1)
-> Nested Loop (cost=1241990.58..1242145.43 rows=9 width=103)
(actual time=10654.330..10655.501 rows=1 loops=1)
-> Subquery Scan on revenue0
Filter: (revenue0.total_revenue = revenue0.max_total_revenue)
Rows Removed by Filter: 2858
-> WindowAgg (cost=1241990.58..1242048.59 rows=1785 width=36)
(actual time=10633.825..10640.556 rows=50000 loops=1)
-> HashAggregate (cost=1241990.58..1242008.43 rows=1785 width=48)
(actual time=10599.406..10617.520 rows=50000 loops=1)
Group By Key: lineitem.l_suppkey
-> Seq Scan on lineitem
Filter: ((l_shipdate >= '1996-01-01') AND (l_shipdate < '1996-04-01'))
Rows Removed by Filter: 28866784
-> Index Scan using supplier_pkey on supplier
Index Cond: (s_suppkey = revenue0.supplier_no)
(actual time=6.568..6.569 rows=1 loops=1)
Total runtime: 10655.762 ms
优化效果：
| 指标 | 优化前 | 优化后 | 提升 |
|---|---|---|---|
| lineitem 扫描次数 | 2 次 | 1 次 | 减半 |
| 耗时 | 20528 ms | 10655 ms | 约 1 倍 |
| 关键算子 | InitPlan + 两次 Seq Scan | WindowAgg + 单次 Seq Scan | - |
核心变化：WindowAgg 算子在同一趟扫描中，既计算了每个 l_suppkey 的收入，又通过 MAX() OVER() 计算了全局最大值，避免了第二次扫描。
3.5 案例：TPCH Q11 —— 分析函数替代相关子查询
业务场景：查询在德国供应商中，库存价值超过德国总库存价值 0.01% 的零件。
**原 SQL **：子查询内外查询条件完全相同，区别只是子查询内全表汇总，子查询外按 ps_partkey 分组汇总。
SET try_vector_engine_strategy = off;
EXPLAIN ANALYZE
SELECT ps_partkey,
SUM(ps_supplycost * ps_availqty) AS value
FROM partsupp, supplier, nation
WHERE ps_suppkey = s_suppkey
AND s_nationkey = n_nationkey
AND n_name = 'GERMANY'
GROUP BY ps_partkey
HAVING SUM(ps_supplycost * ps_availqty) > (
SELECT SUM(ps_supplycost * ps_availqty) * 0.0001000000
FROM partsupp, supplier, nation
WHERE ps_suppkey = s_suppkey
AND s_nationkey = n_nationkey
AND n_name = 'GERMANY'
)
ORDER BY value DESC;
执行计划（优化前）：
QUERY PLAN
--------------------------------------------------------------------------------
Sort (cost=355408.59..355806.15 rows=159023 width=78)
(actual time=4251.443..4251.443 rows=0 loops=1)
Sort Key: (sum((tpch.partsupp.ps_supplycost * (tpch.partsupp.ps_availqty)::numeric))) DESC
Sort Method: quicksort Memory: 25kB
InitPlan 1 (returns $1)
-> Aggregate (cost=169045.93..169045.95 rows=1 width=42)
(actual time=1838.337..1838.337 rows=1 loops=1)
-> Hash Join (cost=1577.15..167853.26 rows=159023 width=10)
(actual time=2.463..1687.844 rows=160240 loops=1)
Hash Cond: (tpch.partsupp.ps_suppkey = tpch.supplier.s_suppkey)
-> Seq Scan on partsupp (cost=0.00..149704.46 rows=3995046 width=14)
(actual time=0.294..1138.281 rows=4000000 loops=1)
-> Hash (cost=1552.15..1552.15 rows=2000 width=4)
(actual time=2.037..2.037 rows=2003 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 327kB
-> Nested Loop (cost=39.75..1552.15 rows=2000 width=4)
(actual time=0.336..1.710 rows=2003 loops=1)
-> Seq Scan on nation (cost=0.00..1.31 rows=1 width=4)
(actual time=0.011..0.016 rows=1 loops=1)
Filter: (n_name = 'GERMANY'::bpchar)
Rows Removed by Filter: 24
-> Bitmap Heap Scan on supplier (cost=39.75..1530.84 rows=2000 width=8)
(actual time=0.312..1.464 rows=2003 loops=1)
Recheck Cond: (s_nationkey = tpch.nation.n_nationkey)
Heap Blocks: exact=1066
-> Bitmap Index Scan on supplier_n1 (cost=0.00..39.25 rows=2000 width=0)
(actual time=0.196..0.196 rows=2003 loops=1)
Index Cond: (s_nationkey = tpch.nation.n_nationkey)
-> HashAggregate (cost=170636.16..172623.95 rows=159023 width=78)
(actual time=4250.294..4250.294 rows=0 loops=1)
Group By Key: tpch.partsupp.ps_partkey
Filter: (sum((tpch.partsupp.ps_supplycost * (tpch.partsupp.ps_availqty)::numeric)) > $1)
Rows Removed by Filter: 151215
-> Hash Join (cost=1577.15..167853.26 rows=159023 width=14)
(actual time=8.013..2099.102 rows=160240 loops=1)
Hash Cond: (tpch.partsupp.ps_suppkey = tpch.supplier.s_suppkey)
-> Seq Scan on partsupp (cost=0.00..149704.46 rows=3995046 width=18)
(actual time=0.671..1450.813 rows=4000000 loops=1)
-> Hash (cost=1552.15..1552.15 rows=2000 width=4)
(actual time=7.207..7.207 rows=2003 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 327kB
-> Nested Loop (cost=39.75..1552.15 rows=2000 width=4)
(actual time=4.938..6.785 rows=2003 loops=1)
-> Seq Scan on nation (cost=0.00..1.31 rows=1 width=4)
(actual time=1.832..1.839 rows=1 loops=1)
Filter: (n_name = 'GERMANY'::bpchar)
Rows Removed by Filter: 24
-> Bitmap Heap Scan on supplier (cost=39.75..1530.84 rows=2000 width=8)
(actual time=3.096..4.628 rows=2003 loops=1)
Recheck Cond: (s_nationkey = tpch.nation.n_nationkey)
Heap Blocks: exact=1066
-> Bitmap Index Scan on supplier_n1 (cost=0.00..39.25 rows=2000 width=0)
(actual time=2.892..2.892 rows=2003 loops=1)
Index Cond: (s_nationkey = tpch.nation.n_nationkey)
Total runtime: 4260.507 ms
问题剖析：
InitPlan 1中第一次执行：扫描partsupp400 万行 +supplier+nation，求德国总库存价值- 外层
HashAggregate中第二次执行：完全相同的关联和过滤条件，只是多了GROUP BY ps_partkey partsupp被扫描了两次，supplier和nation也被访问了两次- 总耗时：4260 ms
优化方案：外层查询使用分析函数 SUM(SUM(...)) OVER() 替代子查询。
EXPLAIN ANALYZE
SELECT /*+ SET(try_vector_engine_strategy force) */
ps_partkey, value
FROM (
SELECT ps_partkey,
SUM(ps_supplycost * ps_availqty) AS value,
(SUM(SUM(ps_supplycost * ps_availqty)) OVER ()) * 0.0001 AS value_cmp
FROM partsupp, supplier, nation
WHERE ps_suppkey = s_suppkey
AND s_nationkey = n_nationkey
AND n_name = 'GERMANY'
GROUP BY ps_partkey
) WHERE value > value_cmp
ORDER BY value DESC;
执行计划（优化后）：
QUERY PLAN
--------------------------------------------------------------------------------
Row Adapter (cost=181289.12..181289.12 rows=53008 width=36)
(actual time=1182.352..1182.352 rows=0 loops=1)
-> Vector Sort (cost=181156.60..181289.12 rows=53008 width=36)
(actual time=1182.350..1182.350 rows=0 loops=1)
Sort Key: "1476207542__unnamed_subquery__".value DESC
-> Vector Subquery Scan on "1476207542__unnamed_subquery__"
Filter: ("1476207542__unnamed_subquery__".value > "1476207542__unnamed_subquery__".value_cmp)
-> Vector WindowAgg (cost=170636.16..175009.30 rows=159023 width=78)
(actual time=1164.717..1177.627 rows=151215 loops=1)
-> Vector Hash Aggregate (cost=170636.16..172623.95 rows=159023 width=78)
(actual time=1111.094..1119.057 rows=151215 loops=1)
Group By Key: partsupp.ps_partkey
-> Vector Sonic Hash Join (cost=1577.15..167853.26 rows=159023 width=14)
(actual time=3.114..1043.281 rows=160240 loops=1)
Hash Cond: (partsupp.ps_suppkey = supplier.s_suppkey)
-> Vector Adapter(type: BATCH MODE)
-> Seq Scan on partsupp
-> Vector Nest Loop
-> Vector Adapter(type: BATCH MODE)
-> Seq Scan on nation
Filter: (n_name = 'GERMANY'::bpchar)
Rows Removed by Filter: 24
-> Bitmap Heap Scan on supplier
Recheck Cond: (s_nationkey = $0)
-> Bitmap Index Scan on supplier_n1
Index Cond: (s_nationkey = $0)
Total runtime: 1188.300 ms
优化效果：
| 指标 | 优化前 | 优化后 | 提升 |
|---|---|---|---|
| partsupp 扫描次数 | 2 次 | 1 次 | 减半 |
| 耗时 | 4260 ms | 1188 ms | 约 3.5 倍 |
| 关键算子 | InitPlan + 两次 Hash Join | Vector WindowAgg + 单次聚合 | - |
核心变化：Vector WindowAgg 算子在同一趟 Vector Hash Aggregate 分组过程中，通过 SUM(...) OVER() 计算了全局总库存价值，彻底消除了 InitPlan 子查询对同一张表的二次访问。
四、通过 Hint 禁止子查询展开（no_expand）
优化器默认会将子查询自动展开（Unnest）为 JOIN，但某些场景下展开后反而性能下降。可以通过 no_expand Hint 禁止展开。
原 SQL（子查询已自动展开）：
EXPLAIN ANALYZE
SELECT t.owner, t.table_name, t.last_analyzed
FROM t_tables t
WHERE NOT EXISTS (
SELECT o.owner, o.object_name FROM t_objects o
WHERE o.owner = t.owner AND o.object_name = t.table_name
);
执行计划：
+ Hash Right Anti Join (cost=144.85..2395.75 rows=416 width=164)
(actual time=34.108..34.119 rows=1 loops=1)
Hash Cond: ...
-> Seq Scan on t_objects o
-> Hash
-> Seq Scan on t_tables t
- 子查询已被展开为 Hash Right Anti Join，看不到 SubPlan
使用 no_expand 禁止展开：
EXPLAIN ANALYZE
SELECT t.owner, t.table_name, t.last_analyzed
FROM t_tables t
WHERE NOT EXISTS (
SELECT /*+ no_expand */ o.owner, o.object_name FROM t_objects o
WHERE o.owner = t.owner AND o.object_name = t.table_name
);
执行计划：
QUERY PLAN
--------------------------------------------------------------------------------
Seq Scan on t_tables t (cost=0.00..34756.91 rows=1430 width=33)
(actual time=45.837..46.991 rows=1 loops=1)
Filter: (NOT (alternatives: SubPlan 1 or hashed SubPlan 2))
Rows Removed by Filter: 2858
+ SubPlan 1
-> Bitmap Heap Scan on t_objects o
Recheck Cond: ((object_name)::text = (t.table_name)::text)
Filter: ((owner)::text = (t.owner)::text)
-> Bitmap Index Scan on t_objects_n10
Index Cond: ((object_name)::text = (t.table_name)::text)
+ SubPlan 2
-> Seq Scan on t_objects o (cost=0.00..2778.57 rows=86657 width=29)
(actual time=0.004..13.772 rows=86657 loops=1)
Total runtime: 47.467 ms
分析：
- 计划中出现了
SubPlan 1和SubPlan 2，说明子查询未被展开 - 优化器自动选择了
hashed SubPlan 2作为替代方案 - 此功能主要用于特殊场景下的执行计划干预
五、总结
| 优化场景 | 原写法 | 推荐写法 | 核心原理 |
|---|---|---|---|
| 判断是否存在 | JOIN + DISTINCT | IN / EXISTS | Semi Join 找到匹配即返回，不膨胀 |
| 判断不存在 | NOT IN | NOT EXISTS | Hash Anti Join 替代 NestLoop Anti Join |
| 逐行查字典表 | 标量子查询 | LEFT JOIN | 单次哈希关联替代 N 次索引扫描 |
| 二次聚合比较 | 子查询求 MAX/SUM | 分析函数 OVER() | 单次扫描完成分组+全局聚合 |
| 子查询展开失控 | 默认展开 | /*+ no_expand */ |
保留 SubPlan，由优化器选择替代方案 |
分析函数的本质：在不减少输出行数的前提下，对数据窗口进行透视计算。它是将"多趟扫描"改写为"单趟扫描"的神器，尤其适用于 TPCH 类分析查询中的子查询消除。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
