查询规划的两半：SQL 重写与连接顺序搜索

2026-06-11 21 预计阅读时间: 1 分钟
来源: postgr.es AI 摘要 Original link

Disclaimer: This article is an AI-assisted summary. Read it together with the original source when precision matters. The summary may omit context, version differences, or edge cases and is not official documentation.

预计阅读时间：11 分钟

你写了一条 SQL，数据库引擎拿到它之后，并不是直接按你写的样子执行。在真正跑起来之前，引擎要做一件极其复杂的事——规划。Christophe Pettus 在演讲中把这件事拆成了两个截然不同的工种：先把你的 SQL 改写成更好的形状，再在数十亿种可能的连接顺序里搜出最便宜的那一条。

理解这两个工种的分工和局限，是写出高性能 SQL、读懂 EXPLAIN 输出的前提。

第一工种：把 SQL 改写成更好的形状

你写的 SQL 是给人看的，不是给引擎看的。规划器的第一步是逻辑重写——在不改变语义的前提下，把语句变换成执行代价更低的等价形式。

常见的重写手段包括：

  • 子查询扁平化：把 IN (SELECT …) 改成 EXISTS 或半连接（semi-join），避免先物化整个子查询结果。
  • 谓词下推：把 WHERE 条件尽可能推到扫描阶段，减少中间结果集大小。
  • 冗余消除：去掉重复的条件、无用的列引用、永远为真或永远为假的过滤。

举个具体例子。下面这条查询，先查出所有订单再过滤，逻辑上没问题，但引擎可能选择物化子查询：

-- 你写的版本
SELECT c.name
FROM customers c
WHERE c.id IN (
    SELECT o.customer_id
    FROM orders o
    WHERE o.status = 'shipped'
);

规划器可能将它重写为半连接，语义不变，但执行路径完全不同：

-- 规划器内部可能重写为的等价形式
SELECT c.name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
      AND o.status = 'shipped'
);

半连接不需要收集子查询的全部行，找到第一个匹配就可以停止扫描当前行——这在数据量大时差别显著。

再看谓词下推。假设你写了一个 JOIN 后再 WHERE：

SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.region = 'APAC';

规划器会把 c.region = 'APAC' 下推到扫描 customers 表的阶段，先过滤再连接，而不是把两张表全量连接后再砍掉大部分行。你可以用 EXPLAIN 验证这一点：

EXPLAIN (COSTS OFF, FORMAT TEXT)
SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.region = 'APAC';

在输出中，你会看到 Filter 或 Index Cond 出现在扫描节点上，而不是在 Join 节点上——这就是谓词下推的证据。

第二工种：在数十亿种连接顺序里搜索最便宜的

重写完成之后，规划器面对的是一个逻辑上已经最优的查询图。但怎么连接这些表——先连谁、后连谁、用什么连接方法（hash join、merge join、nested loop）——仍然有天文数字的组合。

三张表的连接顺序就有 12 种排列（3! × 2²），再加连接方法选择，搜索空间迅速膨胀。Pettus 指出，这个空间可以达到数十亿。规划器不可能穷举，它依赖启发式和动态规划来剪枝。

PostgreSQL 的策略大致是：

  • 对少量表（≤12 左右）：用动态规划精确搜索，保证找到代价最低的计划。
  • 对更多表：退化为遗传算法（GEQO），在有限时间内找到一个"还不错"的计划，但不保证最优。

这意味着：当你的查询涉及大量表连接时，规划器可能错过最优顺序。这不是 bug，是搜索空间太大带来的必然妥协。

你可以用下面这个实验感受连接顺序的影响。准备两张有索引的表：

-- 建表与索引
CREATE TABLE small_table (id INT PRIMARY KEY, val TEXT);
CREATE TABLE big_table   (id INT PRIMARY KEY, small_id INT, val TEXT);
CREATE INDEX ON big_table (small_id);

-- 插入样本数据
INSERT INTO small_table SELECT generate_series(1, 100), 's_' || generate_series;
INSERT INTO big_table   SELECT generate_series(1, 1000000),
                              (generate_series % 100) + 1,
                              'b_' || generate_series;

然后对比两种连接方向：

-- 方向 A：小表驱动大表（hash join，小表建 hash 表）
EXPLAIN ANALYZE
SELECT b.val
FROM small_table s
JOIN big_table b ON b.small_id = s.id
WHERE s.id < 10;

-- 方向 B：强制大表驱动（通过结构调整暗示）
EXPLAIN ANALYZE
SELECT b.val
FROM big_table b
JOIN small_table s ON b.small_id = s.id
WHERE s.id < 10;

在 PostgreSQL 中，规划器通常会自动选择小表在内侧建 hash 表的方向。但如果统计信息不准，它可能选错。EXPLAIN ANALYZE 输出里的 Hash Join 节点会明确告诉你哪张表做了 hash 表、哪张表做了探测侧。

实战：帮规划器做出更好的决定

规划器的两个工种都有盲区。重写阶段受限于等价变换的规则覆盖面；搜索阶段受限于时间和统计信息的精度。你能做的，是减少它犯错的机会。

1. 保持统计信息准确

规划器的代价估算依赖 pg_statistic。如果统计信息过期，搜索阶段就是在错误地图上导航。

-- 手动更新统计信息（在生产环境低峰期执行）
ANALYZE customers;
ANALYZE orders;

-- 或者对关键表加大采样粒度
ANALYZE customers (sample_percent = 50);

还可以调大统计目标，让规划器看到更多细节：

-- 对高频过滤列加大统计目标
ALTER TABLE customers ALTER COLUMN region SET STATISTICS 100;
ANALYZE customers;

2. 用 SQL 结构引导规划器

PostgreSQL 没有官方的"优化器提示"语法，但你可以通过改写 SQL 结构来影响规划器的选择。

-- 如果你确信子查询应该先物化，可以用 CTE 强制物化（PG12 及之前）
WITH shipped_orders AS (
    SELECT customer_id
    FROM orders
    WHERE status = 'shipped'
)
SELECT c.name
FROM customers c
JOIN shipped_orders so ON c.id = so.customer_id;

-- PG12+ 默认不物化 CTE，如需强制物化加上 MATERIALIZED
WITH shipped_orders AS MATERIALIZED (
    SELECT customer_id
    FROM orders
    WHERE status = 'shipped'
)
SELECT c.name
FROM customers c
JOIN shipped_orders so ON c.id = so.customer_id;

物化 CTE 的代价是：子查询结果会写入临时表，占用内存或磁盘，但好处是规划器不再把 CTE 内容混入外层查询的搜索空间——对非常复杂的查询，这反而能缩小搜索范围，让规划器更快找到合理计划。

3. 限制连接表的数量

每多一张表，搜索空间就指数级增长。如果一条 SQL 连接了 15 张表，规划器很可能在有限时间内找不到好计划。

-- 不好的做法：一条巨型查询连接大量表
SELECT ...
FROM a JOIN b JOIN c JOIN d JOIN e JOIN f JOIN g ...;

-- 更好的做法：拆成多步，每步连接少量表
-- 步骤一：先聚合中间结果
CREATE TEMP TABLE step1 AS
SELECT a.id, a.val, b.extra
FROM a JOIN b ON a.b_id = b.id
WHERE a.status = 'active';

-- 步骤二：再连接下一层
SELECT s1.val, c.detail
FROM step1 s1 JOIN c ON s1.id = c.s1_id;

拆步的另一个好处：每步的 EXPLAIN 输出更短、更可读，出问题时更容易定位。

代价与边界

规划器的两个工种各有局限，你需要知道边界在哪里：

工种 能做什么 做不到什么
重写 子查询扁平化、谓词下推、冗余消除 无法改变查询语义；某些复杂嵌套可能无法扁平化
搜索 在有限时间内找最低代价计划 表太多时退化为近似搜索；统计信息不准时代价估算偏

Pettus 的核心观点是：这两件事是完全不同的工种，但它们必须协作。重写给了搜索一个更好的起点，搜索决定了最终的执行路径。如果你只关注其中一个——比如只盯着 EXPLAIN 里的连接方法而忽略了重写阶段是否把谓词推下去了——你就会漏掉另一半的问题。

下次面对一条慢查询，先看 EXPLAIN 输出里扫描节点上的过滤条件是否已经下推，再看连接顺序是否合理。两个方向都检查，才算看到了完整的规划过程。

# PostgreSQL # 数据库

相关推荐