连接算法（Join Algorithms）

只要查询里出现两张以上的表，规划器就必须从三种连接算法中挑一个：嵌套循环连接（Nested Loop Join）、哈希连接（Hash Join）、归并连接（Merge Join）。三者的适用场景几乎是互斥的——选错算法，性能可能差上几个数量级。

本节会先给出三种算法的心智模型与成本公式，再通过真实计划输出解释「规划器为什么这么选」，最后讨论外连接、半连接/反连接、LATERAL 与多表连接顺序这些容易被忽略的细节。

前置知识

阅读本节前，建议先了解：扫描类型（Scan Types）

基础概念

PostgreSQL 的连接算法本质上是在回答同一个问题的三种不同策略：给定外表 A 的每一行，如何找到内表 B 中匹配的行？

算法	计划节点名	前置条件	复杂度直觉	典型场景
嵌套循环	`Nested Loop`	内表可通过索引快速定位	O(外表行数 × 内表单次查找成本)	外表很小（几十~几千行），内表有可用索引
哈希连接	`Hash Join`	等值连接条件	O(外表行数 + 内表行数)	两侧都是大表，等值连接，无可用索引
归并连接	`Merge Join`	等值连接，两侧有序（或可排序）	O(两侧排序成本 + 一次线性扫描)	两侧已有有序输入（索引扫描或上一步的排序结果）

用一个 ASCII 图直观对比三者的数据流动方式：

text

嵌套循环（Nested Loop）
    outer (外表，逐行驱动)
      │
      ├── row1 ──► inner scan (内表，每次重新查找)
      ├── row2 ──► inner scan
      └── row3 ──► inner scan
    循环次数 = 外表行数

哈希连接（Hash Join）
    outer (Probe side，探测侧)
      │ 逐行计算哈希 → 探测
      ▼
    Hash (Build side，构建侧)  ← 一次性建表
      │ 内表全部行 → 哈希桶
      ▼
    输出匹配行

归并连接（Merge Join）
    outer 已排序 ─┐
                  ├─► 双指针同步推进，遇到相等就输出
    inner 已排序 ─┘

用一张表概括「规划器会怎么选」：

条件	倾向算法	理由
外表 < 100 行，内表连接列有索引	`Nested Loop`	循环次数少，每次索引查找成本极低
两侧都大，等值连接	`Hash Join`	只需各扫一遍，避免 O(n×m)
两侧都有序（索引或已排序）	`Merge Join`	无需额外排序，双指针一次线性扫描
连接条件是不等值（`<`、`>`、`BETWEEN`）	`Nested Loop`	哈希/归并只能处理等值条件
外表很小但循环次数巨大	应改 `Hash Join`	`loops` 过大时嵌套循环会被反噬

三者的成本公式（简化版，帮助建立直觉，非规划器源码）：

text

Nested Loop 成本 ≈ 外表成本 + (外表输出行数 × 内表单次扫描成本) + 连接比较 CPU 成本
Hash Join   成本 ≈ 外表成本 + 内表成本 + 建哈希表成本 + 探测成本
Merge Join  成本 ≈ 外表成本 + 内表成本 + 排序成本 + 归并比较成本

语法与用法

三种连接算法无法用 SQL 语法直接指定。规划器根据统计信息与代价参数自动选择；我们只能观察、诊断和间接引导。

观察真实连接计划（必须加 ANALYZE，否则看不到 loops 与真实行数）：

sql

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT o.id, o.amount, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= DATE '2026-01-01';

临时禁用某种连接算法以观察替代计划（仅用于诊断，不要写进应用代码）：

sql

BEGIN;
SET LOCAL enable_hashjoin = off;
SET LOCAL enable_mergejoin = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;   -- 强迫规划器使用 Nested Loop
ROLLBACK;

三个诊断开关的默认值都是 on，但注意它们「无法完全禁止」某类算法——即使全部关掉，规划器在必须时仍会使用（否则查询无法完成）：

sql

-- 全部关闭时观察到的信息：某些连接形式仍然只能靠嵌套循环完成
SET enable_nestloop = off;   -- 只是「不鼓励使用」，不是「禁止使用」
SET enable_hashjoin = off;
SET enable_mergejoin = off;

让哈希连接把内存使用情况打印出来，用于判断是否发生磁盘落盘：

sql

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM big_a a JOIN big_b b ON a.k = b.k;

text

 Hash Join  (cost=123456.78..234567.89 rows=1000000 width=32) (actual time=850.12..2100.33 rows=999812 loops=1)
   Hash Cond: (a.k = b.k)
   Buffers: shared hit=12345 read=6789, temp read=4096 written=8192
   ->  Seq Scan on big_a a  (cost=0.00..45000.00 rows=2000000 width=16) (actual time=0.02..600.10 rows=2000000 loops=1)
   ->  Hash  (cost=80000.00..80000.00 rows=1000000 width=16) (actual time=700.44..700.44 rows=1000000 loops=1)
         Buckets: 1048576  Batches: 4  Memory Usage: 65536kB
         ->  Seq Scan on big_b b  (cost=0.00..80000.00 rows=1000000 width=16) (actual time=0.03..420.77 rows=1000000 loops=1)

Batches: 4 与 temp read/written 同时出现，说明哈希表放不进内存，被分成了 4 批落盘——这通常是 work_mem 或 hash_mem_multiplier 需要调大的信号。

详细说明

Nested Loop Join：嵌套循环连接

嵌套循环是最朴素的算法：拿出外表的每一行，去内表里找匹配的行。SQL 里 FROM a, b WHERE ... 的「笛卡尔积再过滤」是它的直观形式，但规划器使用的嵌套循环几乎总是「参数化索引扫描」——把外表当前行的列值当作常量，代入内表的索引条件。

text

 Nested Loop  (cost=4.65..118.50 rows=10 width=488)
   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244)
         Recheck Cond: (unique1 < 10)
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)
               Index Cond: (unique1 < 10)
   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.90 rows=1 width=244)
         Index Cond: (unique2 = t1.unique2)

关键读法：

Nested Loop 有两个子节点，第一个是外表（outer），第二个是内表（inner），缩进层级表示这层父子关系。
内表的 Index Cond: (unique2 = t1.unique2) 中出现了外表的列 t1.unique2，这叫参数化（parameterized）索引扫描，是嵌套循环高效的前提。
内表节点的 loops 值等于外表输出的行数。在 EXPLAIN ANALYZE 输出里，内表的 actual time 与 rows 都是单次循环的平均值，真实总耗时 ≈ actual time × loops。
rows=1（内表估算行数）乘以 rows=10（外表估算行数）= 10，即连接输出的估算行数。

嵌套循环的成本由「循环次数」主导。当外表输出 10 行、内表单次查找 7.90 单位，循环总成本约 79 单位；但如果外表输出 100 万行，即使内表单次只要 0.01 单位，总成本也会飙到 1 万单位以上。

嵌套循环是最省内存的算法：它不需要为内表建哈希表，也不需要排序缓冲区，只依赖一个索引查找。因此在内存紧张、work_mem 很小、或者外表已经极度收敛（LIMIT 场景）时，它常常是最优解。

Hash Join：哈希连接

哈希连接分为两个阶段：构建阶段（build phase） 和 探测阶段（probe phase）。规划器选择较小的一侧作为构建侧，扫描它并在内存中建立哈希表；然后逐行扫描另一侧（探测侧），用同样的哈希函数定位桶并比对连接条件。

text

 Hash Join  (cost=1750.00..4200.00 rows=100000 width=64)
   Hash Cond: (o.customer_id = c.id)
   ->  Seq Scan on orders o  (cost=0.00..2450.00 rows=100000 width=32)
   ->  Hash  (cost=1250.00..1250.00 rows=50000 width=32)
         Buckets: 65536  Batches: 1  Memory Usage: 3200kB
         ->  Seq Scan on customers c  (cost=0.00..1250.00 rows=50000 width=32)

读法与关键属性：

Hash Cond 是连接条件，必须包含等值比较（=）；>、<、!= 等不等值条件无法用哈希连接。
Hash 节点是 Hash Join 的一个子节点，它不产出数据行（除了在 EXPLAIN ANALYZE 里会显示它消费了多少行），只负责建表。它下面挂的才是构建侧的真实扫描。
Buckets：哈希桶数量。规划器会按估算行数选择 2 的幂次，通常约等于估算行数的 1~2 倍。
Batches: 1：一批完成，说明哈希表完全放在内存里，这是理想状态。
Batches > 1：哈希表放不下，被分成多批，每一批都需要把探测侧重扫一遍。例如 Batches: 4 意味着探测侧要被扫描 4 次（或等价的分批处理），代价急剧上升。
Memory Usage：哈希表实际使用的内存，受 work_mem × hash_mem_multiplier 限制。

哈希连接的哈希表内存上限由两个参数决定：

ini

work_mem = 64MB
hash_mem_multiplier = 2.0

即哈希表最多可用 64MB × 2.0 = 128MB。注意 hash_mem_multiplier 只影响哈希类操作（哈希连接、HashAggregate、Memoize），不影响排序。日志里出现 temporary file 大小增长、Batches 远大于 1 时，可以考虑：

提高 work_mem（注意并发会话数会成倍放大内存占用）；
提高 hash_mem_multiplier（PostgreSQL 13 引入，默认 2.0，在 work_mem ≥ 40MB 的环境下可考虑 4.0 甚至更高）；
在连接列上建索引，让规划器改用嵌套循环。

哈希连接最怕内存不足，但最不怕行数多——两级各扫一遍就是它的下限。所以「两个大表做等值连接」几乎必然走哈希连接。

Merge Join：归并连接

归并连接要求两侧输入都按连接键有序。它使用双指针（类似归并排序的合并步骤）同步推进两路输入，遇到相等就输出。

text

 Merge Join  (cost=0.85..3200.00 rows=50000 width=64)
   Merge Cond: (a.k = b.k)
   ->  Index Scan using idx_a_k on a  (cost=0.29..1500.00 rows=50000 width=16)
   ->  Sort  (cost=1200.00..1250.00 rows=50000 width=16)
         Sort Key: b.k
         ->  Seq Scan on b  (cost=0.00..900.00 rows=50000 width=16)

关键点：

Merge Cond 是连接条件，同样是等值比较。
如果某一侧已经有序（例如走索引扫描），Merge Join 可以直接利用该顺序，省掉一次排序。
如果某一侧无序，规划器会插入一个 Sort 节点——这时排序成本会被计入 Merge Join 的总成本，Merge Join 的优势就大打折扣了。
归并连接同样可以处理非等值的「合并」形式（例如 a.k1 < b.k2、范围连接），但这类计划在实践里远不如等值场景常见，且实现上属于 Merge Join 的一种特殊的「标记式（mark/restore）」扫描。

归并连接在两种场景下最优：

两侧都已有序——例如 orders 按 created_at 索引扫描、order_items 也按 order_id 有序，此时归并连接不需要额外排序，内存消耗几乎为零。
结果需要有序输出——归并连接天然保持排序，能省掉上层的一个 Sort（例如 ORDER BY k）。哈希连接会打乱顺序，嵌套循环则取决外表顺序。

归并连接对数据倾斜（data skew）比较敏感：如果连接键上有大量重复值（例如某列的 n_distinct 很低），归并连接在重复区段的比对次数会激增。

Join Filter 与 Join Cond

这两个字段极易混淆，但含义完全不同：

字段	出现位置	含义	能否下推
`Join Cond` / `Hash Cond` / `Merge Cond`	连接节点自身	用于定位匹配行的条件（等值或排序键）	属于连接算法的输入条件
`Join Filter`	连接节点自身	已定位候选行后再做的额外过滤	无法作为索引/哈希/归并条件

text

 Nested Loop  (cost=4.65..49.36 rows=33 width=488)
   Join Filter: (t1.hundred < t2.hundred)
   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244)
   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.90 rows=1 width=244)
         Index Cond: (unique2 = t1.unique2)

这里的 Join Filter: (t1.hundred < t2.hundred) 是一个不等值条件，无法内化成索引条件，只能作为连接产生候选对之后的二次过滤。Join Filter 出现时往往意味着大量候选对被丢弃，如果同时看到 Rows Removed by Join Filter 数字巨大，说明连接条件写得不够「可索引化」。

在 EXPLAIN ANALYZE 里，这两个统计值配合看：

text

 Nested Loop  (cost=... rows=100 width=488) (actual time=0.05..120.44 rows=100 loops=1)
   Join Filter: (t1.hundred < t2.hundred)
   Rows Removed by Join Filter: 49900

「输出 100 行、丢弃 49900 行」意味着 50000 次配对几乎全是白工。可以考虑给条件补上等值连接键，或改写业务逻辑。

外连接（Outer Join）的实现差异

外连接（LEFT JOIN / RIGHT JOIN / FULL JOIN）与内连接的本质区别在于顺序不可随意重排：

A LEFT JOIN B 要求保留 A 的所有行，B 缺失时补 NULL。因此 A 必须作为连接的驱动侧，规划器不能把 A 换到内表去（否则会丢掉 A 中不匹配的行）。
因此 LEFT JOIN 对算法选择施加了额外约束：外表固定为 A，只有「A 与 B 之间用哪种算法」是可选的。

当连接一侧必须保留全部行时，计划里还会出现 Join Filter 与「null 填充」的标志：

text

 Hash Left Join  (cost=1750.00..4400.00 rows=100000 width=64)
   Hash Cond: (o.customer_id = c.id)
   ->  Seq Scan on orders o  (cost=0.00..2450.00 rows=100000 width=32)
   ->  Hash  (cost=1250.00..1250.00 rows=50000 width=32)
         ->  Seq Scan on customers c  (cost=0.00..1250.00 rows=50000 width=32)

注意节点名变成了 Hash Left Join、Hash Right Join、Hash Full Join、Merge Left Join、Nested Loop Left Join 等——算法名 + 连接类型的组合。

外连接还有两个常见陷阱：

sql

-- 陷阱一：把右表的过滤条件写在 WHERE 里，等价于把 LEFT JOIN 退化成 INNER JOIN
SELECT * FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.amount > 100;        -- 错误！o.amount 为 NULL 的行（无订单客户）会被过滤掉

-- 正确写法：把右表条件放进 ON 子句，保留无订单客户
SELECT * FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.amount > 100;

sql

-- 陷阱二：ON 子句里对左表加条件，无法真正减少左表行数
SELECT * FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND c.status = 'active';
-- 这不等于「只保留 active 客户」，inactive 客户依然会以 o.* 全 NULL 的形式出现

半连接与反连接（Semi / Anti Join）

当需要判断「存在匹配」而不需要匹配的具体列时，SQL 写法有 IN、EXISTS、NOT IN、NOT EXISTS 四种。规划器会把它们改写成半连接（Semi Join） 或 反连接（Anti Join）——后者可以提前终止对每个外表行的内表扫描。

sql

-- 半连接：语义是「有订单的客户」，只关心存在性
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

text

 Hash Semi Join  (cost=1750.00..3200.00 rows=30000 width=32)
   Hash Cond: (c.id = o.customer_id)
   ->  Seq Scan on customers c  (cost=0.00..1250.00 rows=50000 width=32)
   ->  Hash  (cost=1250.00..1250.00 rows=50000 width=16)
         ->  Seq Scan on orders o  (cost=0.00..1250.00 rows=50000 width=16)

sql

-- 反连接：语义是「没有订单的客户」
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

text

 Hash Anti Join  (cost=1750.00..3400.00 rows=20000 width=32)
   Hash Cond: (c.id = o.customer_id)
   ->  Seq Scan on customers c  (cost=0.00..1250.00 rows=50000 width=32)
   ->  Hash  (cost=1250.00..1250.00 rows=50000 width=16)
         ->  Seq Scan on orders o  (cost=0.00..1250.00 rows=50000 width=16)

节点名中的 Semi / Anti 是关键标识。半连接/反连接的价值在于短路：一旦找到一行匹配（半连接）或确认没有任何匹配（反连接），就可以停止对内表的扫描。所以 EXISTS 常常比 IN 快，尤其在子查询结果集很大时。

NOT IN 是这里最危险的一个，因为它与 NOT EXISTS 在 NULL 语义上不等价：

sql

-- 只要 orders.customer_id 里有任何一个 NULL，整个结果集就会是空
SELECT * FROM customers c
WHERE c.id NOT IN (SELECT o.customer_id FROM orders o);   -- 错误！NULL 会导致全部不匹配

-- 安全写法：NOT EXISTS 不受 NULL 影响
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- 或者给 NOT IN 的子查询加 NOT NULL 过滤
SELECT * FROM customers c
WHERE c.id NOT IN (
    SELECT o.customer_id FROM orders o WHERE o.customer_id IS NOT NULL
);

原因是 SQL 三值逻辑：x NOT IN (1, NULL) 等价于 x <> 1 AND x <> NULL，而 x <> NULL 结果是 UNKNOWN，整个 AND 结果为 UNKNOWN（不成立），因此没有任何行能通过。

LATERAL 与嵌套循环

LATERAL 允许右侧子查询引用左侧表的列，这就是显式的「参数化子查询」。它与嵌套循环是天然搭档：规划器可以把它实现为内表带参数的索引扫描，也可以实现为每个外表行执行一次子查询。

sql

-- 每个客户取最近 3 笔订单
SELECT c.id, c.name, o.created_at, o.amount
FROM customers c
LEFT JOIN LATERAL (
    SELECT o.created_at, o.amount
    FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY o.created_at DESC
    LIMIT 3
) o ON TRUE;

对应的计划结构：

text

 Nested Loop Left Join  (cost=0.85..125000.00 rows=150000 width=48)
   ->  Seq Scan on customers c  (cost=0.00..1250.00 rows=50000 width=32)
   ->  Limit  (cost=0.29..2.45 rows=3 width=16) (actual time=0.02..0.05 rows=3 loops=50000)
         ->  Index Scan using idx_orders_cust_created on orders o  (cost=0.29..8.50 rows=10 width=16)
               Index Cond: (customer_id = c.id)

Limit 节点的 loops=50000 正是外表行数——每行执行一次「取前 3 条」。这是 LATERAL + LIMIT 的经典形态，也是嵌套循环最能发挥优势的场景：如果把这个查询写成窗口函数 ROW_NUMBER() OVER (PARTITION BY customer_id ...)，通常需要扫描并排序所有订单，代价更高。

LATERAL 的风险同样明显：如果内表没有合适的索引，loops 会等于外表行数，每次都是一次全表扫描，性能会崩塌成 O(n×m)。

多表连接顺序与 join_collapse_limit

三张以上表的连接顺序组合数是阶乘级的。规划器使用动态规划（DP）枚举连接顺序，但为避免组合爆炸，会有两个上限参数：

参数	默认值	作用
`from_collapse_limit`	8	`FROM` 列表里超过这个数量的项目，不再「扁平化」参与重排
`join_collapse_limit`	8	显式 `JOIN` 的项数超过此值时，不再尝试重排连接顺序
`geqo_threshold`	12	参与连接的表数达到此值时，改用遗传算法（GEQO）搜索
`geqo`	on	是否启用遗传查询优化器

当连接表数超过 geqo_threshold（默认 12）时，规划器不再做穷举 DP，而是启用 GEQO（Genetic Query Optimizer，遗传查询优化器）。GEQO 用启发式搜索代替穷举，规划时间从指数级降到可接受，代价是可能选到次优计划。

sql

-- 观察是否使用了 GEQO
EXPLAIN (COSTS off) SELECT ... ;   -- GEQO 计划在文本输出里没有专门标记
SHOW geqo_threshold;
SHOW join_collapse_limit;

实践中的两条经验：

不要为了「控制连接顺序」而调低 join_collapse_limit。 降低它会让规划器不再重排，等于用 SQL 书写顺序强制连接顺序，这在大多数情况下会选到更差的计划。只有在规划时间严重影响吞吐（连接表极多、简单查询却耗时几十毫秒）时才考虑。
LEFT JOIN 混在多层连接中时，重排受限。 外连接的存在会限制可重排的空间，因此把「必须保留全部行的表」尽量放在连接链的起点，有助于规划器找到更好的顺序。

三种算法在并行计划下的表现也不相同：

并行嵌套循环：外表（驱动侧）并行扫描，内表在每个 worker 里完整执行。这是高效的，因为索引查找被分摊到各 worker。
并行归并连接：内表在每个 worker 里完整执行，如果内表需要排序，排序工作会在每个 worker 里重复做一遍，效率可能很差。
并行哈希连接（Parallel Hash Join）：内表侧是一个 Parallel Hash 节点，多个 worker 协作构建一个共享哈希表，而不是各建一份。这是大表连接的首选。

text

 Gather  (cost=101000.00..152000.00 rows=1000000 width=32)
   Workers Planned: 2
   ->  Parallel Hash Join  (cost=100000.00..141000.00 rows=416667 width=32)
         Hash Cond: (a.k = b.k)
         ->  Parallel Seq Scan on big_a a  (cost=0.00..25000.00 rows=833333 width=16)
         ->  Parallel Hash  (cost=80000.00..80000.00 rows=1000000 width=16)
               ->  Parallel Seq Scan on big_b b  (cost=0.00..80000.00 rows=416667 width=16)

节点名前缀 Parallel 表示该算子本身做了并行化，Workers Planned: 2 表示规划器计划使用 2 个 worker。

enable_* 开关的正确用法

enable_nestloop、enable_hashjoin、enable_mergejoin 是诊断工具，不是调优手段。它们的正确用法是：

sql

-- 步骤 1：确认当前计划
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

-- 步骤 2：临时禁用当前算法，看是否存在明显更优的计划
BEGIN;
SET LOCAL enable_nestloop = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
ROLLBACK;

-- 步骤 3：如果替代计划确实快很多，找出根因（统计信息？代价参数？索引？），修复根因

常见的根因有三类：

统计信息不准：实际行数与估算行数差几个数量级，导致规划器低估/高估嵌套循环成本。对策是 ANALYZE、提高 default_statistics_target，或用 ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 针对单列提高精度。
代价参数不符合硬件：在 SSD 上 random_page_cost = 4.0 会让规划器低估索引查找的成本，从而倾向于哈希连接。对策是把 random_page_cost 降到 1.1~2.0。
缺少合适索引：内表连接列没有索引，嵌套循环只能用 Seq Scan 做内表，loops 大了就崩。

绝对不要在应用代码或全局配置里写 enable_nestloop = off 这类设置——它会影响所有查询，造成难以排查的次生问题。

rows 估算错误导致算法选错

连接算法选错，最常见的原因不是代价参数，而是行数估算错误。规划器会为每个节点估算输出行数（rows=），当估算严重偏离实际，代价计算就会整体偏斜。

典型信号：EXPLAIN ANALYZE 里某个节点的 rows=100 而 actual rows=450000。这通常源于：

多列相关性：WHERE city = 'Beijing' AND district = 'Haidian' 两个条件彼此相关，规划器假设独立相乘，严重低估。对策：建多列扩展统计信息。

sql

CREATE STATISTICS st_city_district (dependencies, ndistinct)
ON city, district FROM address;
ANALYZE address;

数据倾斜：连接键上某个值出现频率极高（如 status = 'active' 占 95%），而规划器只用了平均值。对策：提高该列的统计目标。

sql

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

统计信息过期：批量导入/删除后没 ANALYZE。对策：确保 autovacuum 正常工作，大变更后手动 ANALYZE。

sql

VACUUM (ANALYZE) orders;

当估算偏差被修正后，规划器往往会自动切换到正确的连接算法，无需动用任何 enable_* 开关。

实战示例

场景一：外表行数估算错误，嵌套循环被误选

先看一张有 500 万行订单的表，和一张 5 万行的客户表。业务查询是「某城市的活跃客户及其订单金额」。

sql

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.id, c.name, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Beijing';

改造前的计划：

text

 Nested Loop  (cost=0.85..285000.00 rows=50000 width=48) (actual time=0.12..9850.44 rows=4800000 loops=1)
   ->  Index Scan using idx_customers_city on customers c  (cost=0.29..850.00 rows=500 width=32) (actual time=0.05..8.20 rows=32000 loops=1)
         Index Cond: (city = 'Beijing'::text)
   ->  Index Scan using idx_orders_customer_id on orders o  (cost=0.29..420.00 rows=100 width=32) (actual time=0.15..0.29 rows=150 loops=1)
         Index Cond: (customer_id = c.id)
   Buffers: shared hit=4820000 read=125000
 Planning Time: 0.452 ms
 Execution Time: 9850.612 ms

逐行解读：

customers 的 Index Scan 估算 500 行，实际 32000 行——低估了 64 倍。原因是 city 列的统计信息没更新（或该城市占比异常高）。
外表实际输出 32000 行，所以内表被循环了 32000 次。内表单次 actual time=0.15..0.29，看起来很快，但 0.29ms × 32000 ≈ 9280ms，这就是总耗时的来源。
估算连接输出 rows=50000，实际 rows=4800000——低估了 96 倍。规划器以为嵌套循环只会处理 5 万行，才敢选它。
Buffers: shared hit=4820000——索引查找命中 482 万个缓冲区，说明大量重复回表。

修复：先更新统计信息并提高 city 列的统计精度。

sql

ALTER TABLE customers ALTER COLUMN city SET STATISTICS 500;
ANALYZE customers;

再次执行：

text

 Hash Join  (cost=2100.00..152000.00 rows=4800000 width=48) (actual time=15.30..1240.88 rows=4800000 loops=1)
   Hash Cond: (o.customer_id = c.id)
   ->  Seq Scan on orders o  (cost=0.00..121000.00 rows=5000000 width=16) (actual time=0.02..480.15 rows=5000000 loops=1)
   ->  Hash  (cost=1850.00..1850.00 rows=32000 width=40) (actual time=12.40..12.40 rows=32000 loops=1)
         Buckets: 32768  Batches: 1  Memory Usage: 2016kB
         ->  Index Scan using idx_customers_city on customers c  (cost=0.29..1850.00 rows=32000 width=40) (actual time=0.05..6.10 rows=32000 loops=1)
               Index Cond: (city = 'Beijing'::text)
   Buffers: shared hit=98000 read=62000
 Planning Time: 0.618 ms
 Execution Time: 1240.885 ms

规划器改用了 Hash Join：把 3.2 万行客户建成哈希表（只用了 2MB 内存、Batches: 1），然后顺序扫描 500 万行订单做探测。执行时间从 9850ms 降到 1240ms，缓冲区访问从 482 万降到 16 万。没有加任何索引，没有改一句 SQL——只是让统计信息说真话。

场景二：把 `NOT IN` 改写为 `NOT EXISTS`，从「空结果 + 全表扫」到反连接

业务需求是「找出从未下单的客户」。

sql

-- 改造前：NOT IN，且子查询未过滤 NULL
EXPLAIN (ANALYZE, BUFFERS)
SELECT c.id, c.name
FROM customers c
WHERE c.id NOT IN (SELECT customer_id FROM orders);

text

 Seq Scan on customers c  (cost=0.00..162500.00 rows=25000 width=40) (actual time=0.03..8420.55 rows=0 loops=1)
   Filter: (NOT (SubPlan 1))
   Rows Removed by Filter: 50000
   SubPlan 1
     ->  Materialize  (cost=0.00..121000.00 rows=5000000 width=8) (actual time=0.02..4200.10 rows=5000000 loops=50000)
           ->  Seq Scan on orders  (cost=0.00..121000.00 rows=5000000 width=8) (actual time=0.02..600.30 rows=5000000 loops=1)
   Buffers: shared hit=2000 read=240000
 Planning Time: 0.580 ms
 Execution Time: 8420.771 ms

逐行解读，这里有两个问题叠加：

结果是 0 行（rows=0）——这是 NOT IN 的 NULL 陷阱。orders.customer_id 中存在 NULL（例如游客订单），x NOT IN (..., NULL, ...) 恒为 UNKNOWN，所以没有任何行能通过。表面看「查询很快返回」，实际是结果错误。
SubPlan 1 被 loop=50000 次重扫，尽管有 Materialize 缓存了子查询结果，仍需要每次在 500 万行里线性比对，actual time=4200ms（单次累计）× 50000 次 = 天文数字。当然因为 NULL 短路，实际提前返回了，但代价依然高达 8420ms。

改造：改用 NOT EXISTS，并获得反连接计划。

sql

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

text

 Hash Anti Join  (cost=146000.00..175000.00 rows=25000 width=40) (actual time=720.15..1180.42 rows=4820 loops=1)
   Hash Cond: (c.id = o.customer_id)
   ->  Seq Scan on customers c  (cost=0.00..1250.00 rows=50000 width=40) (actual time=0.02..12.30 rows=50000 loops=1)
   ->  Hash  (cost=121000.00..121000.00 rows=1000000 width=8) (actual time=680.40..680.40 rows=995180 loops=1)
         Buckets: 1048576  Batches: 1  Memory Usage: 48000kB
         ->  HashAggregate  (cost=109000.00..119000.00 rows=1000000 width=8) (actual time=310.20..540.60 rows=995180 loops=1)
               Group Key: o.customer_id
               ->  Seq Scan on orders o  (cost=0.00..121000.00 rows=5000000 width=8) (actual time=0.02..280.10 rows=5000000 loops=1)
   Buffers: shared hit=12500 read=98000
 Planning Time: 0.712 ms
 Execution Time: 1180.545 ms

关键变化：

计划变成 Hash Anti Join，语义明确、语义正确。orders.customer_id 为 NULL 的行在建哈希表时被自动忽略（HashAggregate 去重后是 995180 行，少于 500 万原始行），不再产生 NOT IN 的 NULL 陷阱。
结果从错误的 0 行变成正确的 4820 行。
执行时间从 8420ms 降到 1180ms，Buffers 从 24 万降到最后 11 万，且完全避免了 5 万次子计划重扫。

这个例子说明：改写所带来的收益，往往来自语义修正 + 让规划器看到可优化的连接结构，而不是来自某个神秘参数。

注意事项

不要把 enable_nestloop = off 写进全局配置或应用代码。 它只是诊断工具，会污染所有查询的计划选择。
sql
```
-- 错误！
ALTER SYSTEM SET enable_nestloop = off;
```
不要给内表加 LIMIT 来「骗」嵌套循环优化。 内表的 LIMIT 会改变结果语义，属于结果错误，不是优化。
sql
```
-- 错误！会漏掉匹配行
Nested Loop ... inner: SELECT * FROM orders WHERE customer_id = c.id LIMIT 100;
```
谨慎使用 NOT IN。 子查询列一旦含 NULL，整个条件恒为 UNKNOWN，查询静默返回空结果。
sql
```
-- 不推荐
WHERE c.id NOT IN (SELECT customer_id FROM orders);
```

外连接的过滤条件不要写错位置。 右表的过滤条件放到 WHERE，会把外连接退化成内连接。

sql

-- 错误！语义已变成内连接
SELECT * FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid';

注意 loops 与 actual time 的乘法关系。 忽略 loops 会把「每次 0.05ms」误读为「总共 0.05ms」。

text

-- 真实耗时 ≈ 0.05ms × 50000 = 2500ms，而不是 0.05ms
->  Index Scan ... (actual time=0.02..0.05 rows=3 loops=50000)

Batches > 1 的哈希连接要警惕。 分批落盘会让探测侧重复扫描，代价成倍上升。

text

-- 不推荐：哈希表放不进内存
Batches: 8  Memory Usage: 65536kB   -- 同时观察 temp read/written

不要盲目提高 work_mem。 它是「每个操作、每个会话」的上限，并发高时总内存会成倍放大。
ini
```
-- 不推荐：在 500 并发下等价于 500 × 256MB = 128GB 的潜在上限
work_mem = 256MB
```

归并连接不是「更高级的算法」。 当两侧无序时，它需要先排序，往往比哈希连接更慢。

text

-- 不推荐：两侧都要 Sort，排序成本已经超过哈希连接
Merge Join
  ->  Sort  (Sort Key: a.k)  ->  Seq Scan on a
  ->  Sort  (Sort Key: b.k)  ->  Seq Scan on b

Join Filter 大量丢弃候选行时，要检查连接条件能否索引化。

text

-- 不推荐：5 万次配对只产出 100 行
Join Filter: (t1.hundred < t2.hundred)
Rows Removed by Join Filter: 49900

不要用 OFFSET ... LIMIT 做深分页。 它会让规划器倾向于小 LIMIT 的计划，但实际要跳过大量行。应改用 keyset 分页。

sql

-- 不推荐：偏移 100 万行
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 1000000;

-- 推荐：keyset 分页，用上一页最后一行的键续读
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;

不要在连接列上做函数转换。 那会让索引条件退化为 Join Filter 或 Filter。
sql
```
-- 不推荐：内表索引失效
JOIN orders o ON o.customer_id = c.id::text;
```

LATERAL 必须配合索引。 否则 loops 会等于外表行数，每次都全表扫描。

sql

-- 前提：orders (customer_id, created_at DESC) 上有索引
CREATE INDEX idx_orders_cust_created ON orders (customer_id, created_at DESC);

最佳实践

先看 loops 和 rows 估算 vs 实际，再谈算法选型。 90% 的「算法选错」其实是「行数估算错了」。

sql

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
-- 对比每个节点的 rows= (估算) 与 rows= (实际)，偏差 > 10 倍就该怀疑统计信息

大表等值连接，优先让规划器选哈希连接。 要做的不是强制，而是保证内存够用。
ini
```
work_mem = 64MB
hash_mem_multiplier = 2.0
```
等值连接的列必须有索引，才能让嵌套循环成为可行选项。 这是给规划器更多选择，而不是强制它选。
sql
```
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
```
用 EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 观察并行连接的各 worker 统计。 特别适合排查 worker 之间负载不均。
sql
```
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ... ;
```
外连接尽量把「必须保留全部行」的表放在 FROM 的第一个位置，并谨慎混用多层外连接。 这让可重排空间最大化。
sql
```
SELECT * FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
LEFT JOIN order_items i ON i.order_id = o.id;
```
存在性判断一律用 EXISTS / NOT EXISTS，不用 IN / NOT IN。 除了避免 NULL 陷阱，还更容易得到半连接/反连接计划。
sql
```
-- 推荐
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
```

LATERAL 配合「排序 + LIMIT」解决 top-N per group。 通常优于窗口函数，因为可以走索引提前终止。

sql

SELECT c.id, o.* FROM customers c
LEFT JOIN LATERAL (
    SELECT * FROM orders o WHERE o.customer_id = c.id
    ORDER BY o.created_at DESC LIMIT 3
) o ON TRUE;

多列相关性明显时创建扩展统计信息。 这是修复「估算行数差几个数量级」最直接的手段。
sql
```
CREATE STATISTICS st_city_district (dependencies, ndistinct) ON city, district FROM address;
ANALYZE address;
```
连接键的数据类型必须一致。 类型不一致会触发隐式转换，导致索引失效、退化为 Filter 或 Join Filter。
sql
```
-- 正确：两侧都是 bigint
JOIN orders o ON o.customer_id = c.id
```

连接表数超过 geqo_threshold 时，主动检查计划质量。 GEQO 是启发式搜索，结果可能是次优的。

sql

SHOW geqo_threshold;          -- 默认 12
SET geqo_threshold = 15;      -- 若规划时间可接受，可适当放宽以启用穷举 DP

每次调优都保留基线数据（执行时间 + Buffers + 计划文本）。 用对比证明改动有效，而不是凭感觉。
sql
```
-- 改造前后各执行一次并保存 EXPLAIN (ANALYZE, BUFFERS) 输出
```
优先修复根因（统计信息、索引、代价参数），而不是用 enable_* 开关锁死计划。 锁死的计划在数据分布变化后往往成为新的性能故障源。

继续学习：SQL 改写优化（Query Rewriting）

连接算法（Join Algorithms） ​

基础概念 ​

语法与用法 ​

详细说明 ​

Nested Loop Join：嵌套循环连接 ​

Hash Join：哈希连接 ​

Merge Join：归并连接 ​

Join Filter 与 Join Cond ​

外连接（Outer Join）的实现差异 ​

半连接与反连接（Semi / Anti Join） ​

LATERAL 与嵌套循环 ​

多表连接顺序与 join_collapse_limit ​

enable_* 开关的正确用法 ​

rows 估算错误导致算法选错 ​

实战示例 ​

场景一：外表行数估算错误，嵌套循环被误选 ​

场景二：把 NOT IN 改写为 NOT EXISTS，从「空结果 + 全表扫」到反连接 ​

注意事项 ​

最佳实践 ​

参考链接 ​

连接算法（Join Algorithms）

基础概念

语法与用法

详细说明

Nested Loop Join：嵌套循环连接

Hash Join：哈希连接

Merge Join：归并连接

Join Filter 与 Join Cond

外连接（Outer Join）的实现差异

半连接与反连接（Semi / Anti Join）

LATERAL 与嵌套循环

多表连接顺序与 join_collapse_limit

enable_* 开关的正确用法

rows 估算错误导致算法选错

实战示例

场景一：外表行数估算错误，嵌套循环被误选

场景二：把 `NOT IN` 改写为 `NOT EXISTS`，从「空结果 + 全表扫」到反连接

注意事项

最佳实践

参考链接