PostgreSQL 中的查询计划重写

本页目录

原作者:Phil Eaton,The Consensus。原文发表于 2026 年 9 月 13 日:Query plan rewriting in PostgreSQL。

以下为原文翻译,示例与执行计划来自原作者。终端中的 SQL 提取为可直接执行的代码块;必要的技术说明以“译注”标出。中文翻译与校订:xiongcc。

查询计划抽象地描述了对数据执行的操作,例如扫描磁盘中的行、扫描索引、应用过滤条件、选择部分列、连接两张表,以及对列进行运算。为了得到查询计划,数据库会将 SQL 解析为抽象语法树(AST),再解析其中的标识符,最终生成查询计划。

在数据库内部,查询规划器负责重写和优化查询计划:折叠常量、决定是否使用索引、先扫描哪张表、采用哪种连接方式等。本文用“重写”指代静态可知的变换,例如 i+0 与 i 相同;“优化”则指需要检查统计信息或其他数据相关信息才能作出的选择。

译注:这里沿用作者对 rewriting 的宽泛用法。PostgreSQL 正式的 rewrite 阶段主要应用规则、展开视图,之后规划器才生成执行计划。本文的常量折叠、子查询拉起和连接消除等,大多属于规划器内部的变换;解析并绑定标识符之后得到的查询树,也还不是最终执行计划。

各数据库虽然都有查询规划器,也会执行类似工作,但对一个数据库规划器的了解,未必能直接套用到另一个数据库。本文看看 PostgreSQL 会做、以及不会做哪些查询计划重写,为另一篇讨论依赖统计信息的优化文章作准备。

先准备 PostgreSQL 18。原文使用以下安装命令:

Shell
sudo apt-get update -y
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt update -y
sudo apt install -y postgresql-18

接着建立两张测试表。以下初始化语句包含 DROP TABLE,应当在独立测试库中运行。

SQL
DROP TABLE IF EXISTS users;
CREATE TABLE users (user_id INT PRIMARY KEY, user_name TEXT);
INSERT INTO users
  SELECT i, substring(md5(random()::text), 1, 12)
  FROM generate_series(1, 200_000) as i;

DROP TABLE IF EXISTS orders;
CREATE TABLE orders (order_id INT PRIMARY KEY, order_user_id INT, order_status TEXT);
INSERT INTO orders
  SELECT i, 1 + (i % 200_000), (ARRAY['paid', 'shipped', 'delivered'])[1+i%3]
  FROM generate_series(1,500_000) i;
VACUUM ANALYZE users;
VACUUM ANALYZE orders;

将上述内容保存为 /tmp/init.sql。执行 VACUUM ANALYZE,让后面的查询计划基于经过维护、已有统计信息的表。

TEXT
$ sudo -u postgres psql -f /tmp/init.sql
DROP TABLE
CREATE TABLE
INSERT 0 200000
DROP TABLE
CREATE TABLE
INSERT 0 500000
VACUUM
VACUUM

常量折叠

常量折叠是编译原理中的基础概念。假设 AST 中有一个 + 3 4 子树,至少包含三个节点,就可以将它改写成单个节点 7。在编译阶段提前计算这部分表达式,便减少了运行时的工作。

PostgreSQL 会这样做:

SQL
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE user_id = 100 + 100;
TEXT
Index Scan using users_pkey on users
  Index Cond: (user_id = 200)

就像直接传入 200 一样:

SQL
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE user_id = 200;
TEXT
Index Scan using users_pkey on users
  Index Cond: (user_id = 200)
SQL
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE 200 = user_id;
TEXT
Index Scan using users_pkey on users
  Index Cond: (user_id = 200)

列写在等号的哪一侧也没有关系:

SQL
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE 100 + 100 = user_id;
TEXT
Index Scan using users_pkey on users
  Index Cond: (user_id = 200)

不过,加法需要发生在常量这一侧。否则,常量折叠和这里的普通索引扫描都不会出现:

SQL
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE user_id + 0 = 100;
TEXT
Gather
  Workers Planned: 1
  ->  Parallel Seq Scan on users
        Filter: ((user_id + 0) = 100)
SQL
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE 100 = user_id + 0;
TEXT
Gather
  Workers Planned: 1
  ->  Parallel Seq Scan on users
        Filter: (100 = (user_id + 0))

当然,消去“加零”算是一个比较特殊的例子。至少我们知道,可以为表达式建立索引,让规划器有相应的索引路径可选。试试看:

SQL
CREATE INDEX ON users ((user_id + 0));
VACUUM ANALYZE users;

这次能用上了:

SQL
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE user_id + 0 = 100;
TEXT
Index Scan using users_expr_idx on users
  Index Cond: ((user_id + 0) = 100)

但交换加法的操作数之后,就又不走这个索引了,尽管加法满足交换律:

SQL
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE 0 + user_id = 100;
TEXT
Gather
  Workers Planned: 1
  ->  Parallel Seq Scan on users
        Filter: ((0 + user_id) = 100)

对于函数,如果标记为 IMMUTABLE,并且参数也是常量或可折叠的不变表达式,PostgreSQL 就可以在规划时计算它:

SQL
CREATE FUNCTION double(int) RETURNS int AS $$
  BEGIN RETURN $1 * 2;
END $$ LANGUAGE plpgsql IMMUTABLE;
SQL
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE user_id = double(100);
TEXT
Index Scan using users_pkey on users
  Index Cond: (user_id = 200)

足够简单的 SQL 函数也可以:

SQL
CREATE FUNCTION triple(int) RETURNS int AS 'SELECT $1 * 3' LANGUAGE sql;
SQL
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE user_id = triple(100);
TEXT
Index Scan using users_pkey on users
  Index Cond: (user_id = 300)

译注:triple 没有声明易变性类别,默认是 VOLATILE。这里先将符合条件的简单 SQL 函数内联为乘法表达式,再折叠常量;不能据此认为任意 VOLATILE 函数都能在规划时求值。

如果函数不是 IMMUTABLE,而是 STABLE,仍然可以用于索引条件,但不会这样折叠成常量。例如 now() 的值由事务开始时间确定:

SQL
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE 100 + EXTRACT(DAY FROM NOW())::INT = user_id;
TEXT
Index Scan using users_pkey on users
  Index Cond: (user_id = (100 + (EXTRACT(day FROM now()))::integer))

这又不同于表达式包含 VOLATILE 调用,或依赖正在扫描的行时的情况。下面的表达式依赖该行的 user_name:

SQL
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE length(user_name) = user_id;
TEXT
Gather
  Workers Planned: 1
  ->  Parallel Seq Scan on users
        Filter: (length(user_name) = user_id)

子查询展开与条件下推

子查询足够简单时,PostgreSQL 会把内外两层查询合并起来:

SQL
EXPLAIN (COSTS OFF) SELECT * FROM (SELECT user_id, length(user_name) AS n FROM users) x WHERE x.user_id = 42;
TEXT
Index Scan using users_pkey on users
  Index Cond: (user_id = 42)

但如果变换会改变查询含义,就不能这样处理。例如,子查询带有 LIMIT:

SQL
EXPLAIN (COSTS OFF) SELECT * FROM (SELECT user_id, length(user_name) AS l FROM users LIMIT 3) u WHERE u.user_id = 100;
TEXT
Subquery Scan on u
  Filter: (u.user_id = 100)
  ->  Limit
        ->  Seq Scan on users

对比原查询与错误改写后的结果,就容易理解了:

SQL
SELECT count(1) FROM (SELECT user_id, length(user_name) AS l FROM users LIMIT 3) u WHERE u.user_id = 100;
TEXT
count
-----
    0
SQL
SELECT count(1) FROM (SELECT user_id, length(user_name) AS l FROM users WHERE user_id = 100 LIMIT 3) u WHERE u.user_id = 100;
TEXT
count
-----
    1

译注:没有 ORDER BY 的 LIMIT 3 不保证选中哪三行,0 是原文这次执行的结果。关键差别是先取三行再筛选,还是先筛选再取三行;不能依赖无序查询总会返回同样的计数。

对于更复杂的子查询,PostgreSQL 会尝试把过滤条件向下推:

SQL
EXPLAIN (COSTS OFF)
SELECT * FROM (
  SELECT order_user_id, count(*) AS orders
  FROM orders GROUP BY order_user_id) s
WHERE s.order_user_id = 100;
TEXT
Finalize GroupAggregate
  ->  Gather
        Workers Planned: 1
        ->  Partial GroupAggregate
              ->  Parallel Seq Scan on orders
                    Filter: (order_user_id = 100)

不只等值条件可以下推:

SQL
EXPLAIN (COSTS OFF)
SELECT * FROM (
  SELECT order_user_id, count(*) AS orders
  FROM orders GROUP BY order_user_id) s
WHERE s.order_user_id > 100;
TEXT
HashAggregate
  Group Key: orders.order_user_id
  ->  Seq Scan on orders
        Filter: (order_user_id > 100)

接下来先回顾两种基本连接,以及 PostgreSQL 执行连接的几种策略。

连接

JOIN 是内连接,要求两侧的行满足连接条件。LEFT JOIN 是外连接,无论是否匹配,都会保留左侧的每一行;如果右侧没有匹配行,就用 NULL 补齐右侧列。

PostgreSQL 有三种主要的连接策略。官方文档的描述很清楚,下面直接引用其含义。

嵌套循环连接(Nested Loop Join):

对于左侧关系中的每一行,都扫描一次右侧关系。这种策略容易实现,但可能非常耗时。不过,如果可以通过索引扫描右侧关系,它就可能是一个不错的选择:可以将当前左侧行中的值作为右侧索引扫描的键。

归并连接(Merge Join):

连接开始前,先按连接属性对两侧关系排序,再沿两侧有序数据推进,合并匹配行。这种策略的吸引力在于,每侧关系都只需扫描一次。所需顺序可以通过显式排序获得,也可以通过按适当顺序扫描索引获得。

哈希连接(Hash Join):

先扫描右侧关系,以连接属性为哈希键,将其装入哈希表;然后扫描左侧关系,根据每行的连接属性值,在哈希表中查找匹配行。

因此,EXPLAIN 中的 Hash Left Join,就是采用哈希算法执行的左外连接。

译注:这里的左右侧用于解释物理算法。规划器可以调整内连接的顺序,哈希表的构建侧也不由 SQL 中表名的书写顺序固定;应结合实际计划的子节点判断。

执行计划中还会看到半连接和反连接。半连接(Semi Join)判断是否至少有一个匹配,反连接(Anti Join)则判断是否完全没有匹配。

将条件下推到连接两侧

与子查询中的条件下推类似,PostgreSQL 可以利用连接条件,把一个等值过滤条件传播到另一侧:

SQL
EXPLAIN (COSTS OFF)
SELECT o.order_id, u.user_name
FROM orders o JOIN users u ON u.user_id = o.order_user_id
WHERE o.order_user_id = 100;
TEXT
Gather
  Workers Planned: 1
  ->  Nested Loop
        ->  Parallel Seq Scan on orders o
              Filter: (order_user_id = 100)
        ->  Materialize
              ->  Index Scan using users_pkey on users u
                    Index Cond: (user_id = 100)

但下面的不等值条件还不会得到同样的传播:

SQL
EXPLAIN (COSTS OFF)
SELECT o.order_id, u.user_name
FROM orders o JOIN users u ON u.user_id = o.order_user_id
WHERE o.order_user_id < 100;
TEXT
Gather
  Workers Planned: 1
  ->  Nested Loop
        ->  Parallel Seq Scan on orders o
              Filter: (order_user_id < 100)
        ->  Index Scan using users_pkey on users u
              Index Cond: (user_id = o.order_user_id)

连接消除

当 PostgreSQL 能证明一个连接不影响查询结果时,就可能将其消除。例如,执行 LEFT JOIN,查询没有使用右侧列,并且可以证明每个左侧行在右侧至多匹配一行:

SQL
EXPLAIN (COSTS OFF) SELECT o.order_id FROM orders o LEFT JOIN users u ON u.user_id = o.order_user_id;
TEXT
Seq Scan on orders o

但这并不适用于所有连接。假设给两张表添加外键关系,再执行内连接:右侧的连接列是主键,保证唯一;左侧连接列有外键约束且为 NOT NULL,因此每个左侧行恰好有一个匹配,但连接仍没有被消除。

SQL
ALTER TABLE orders ALTER COLUMN order_user_id SET NOT NULL;
ALTER TABLE orders ADD CONSTRAINT orders_user_fk FOREIGN KEY (order_user_id) REFERENCES users(user_id);
VACUUM ANALYZE orders;
SQL
EXPLAIN (COSTS OFF) SELECT o.order_id FROM orders o JOIN users u ON u.user_id = o.order_user_id;
TEXT
Hash Join
  Hash Cond: (o.order_user_id = u.user_id)
  ->  Seq Scan on orders o
  ->  Hash
        ->  Seq Scan on users u

不过,PostgreSQL 18 新增了对一类内连接的消除:同一张表在唯一列上的自连接。

SQL
EXPLAIN (COSTS OFF) SELECT a.order_status, b.order_user_id FROM orders a JOIN orders b ON a.order_id = b.order_id;
TEXT
Seq Scan on orders b

IN 与 EXISTS

简单的 IN 和 EXISTS 可以被展开,并进行常量折叠:

SQL
EXPLAIN (COSTS OFF) SELECT * FROM users u WHERE u.user_id IN (SELECT 100 + 100);
TEXT
Index Scan using users_pkey on users u
  Index Cond: (user_id = 200)
SQL
EXPLAIN (COSTS OFF) SELECT * FROM users u WHERE EXISTS (SELECT 1 WHERE u.user_id = 100 + 100);
TEXT
Index Scan using users_pkey on users u
  Index Cond: (user_id = 200)

下面两种更复杂的写法,也会转换为连接,而不是为外侧的每一行分别执行一次子查询:

SQL
EXPLAIN (COSTS OFF) SELECT * FROM users u WHERE u.user_id IN (SELECT o.order_user_id FROM orders o WHERE o.order_status = 'paid');
TEXT
Hash Right Semi Join
  Hash Cond: (o.order_user_id = u.user_id)
  ->  Seq Scan on orders o
        Filter: (order_status = 'paid'::text)
  ->  Hash
        ->  Seq Scan on users u
SQL
EXPLAIN (COSTS OFF) SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.order_status = 'paid' AND o.order_user_id = u.user_id);
TEXT
Hash Right Semi Join
  Hash Cond: (o.order_user_id = u.user_id)
  ->  Seq Scan on orders o
        Filter: (order_status = 'paid'::text)
  ->  Hash
        ->  Seq Scan on users u

连 NOT EXISTS 也可以转换为反连接:

SQL
EXPLAIN (COSTS OFF) SELECT * FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.order_status = 'paid' AND o.order_user_id = u.user_id);
TEXT
Gather
  Workers Planned: 1
  ->  Parallel Hash Anti Join
        Hash Cond: (u.user_id = o.order_user_id)
        ->  Parallel Seq Scan on users u
        ->  Parallel Hash
              ->  Parallel Seq Scan on orders o
                    Filter: (order_status = 'paid'::text)

但换成 NOT IN,似乎就无法进行同样的变换。一般情况下,如果子查询包含 NULL,NOT IN 可能返回 NULL 而不是 true;不过,在本例中,我们已经将 user_id 和 order_user_id 都定义为 NOT NULL,计划仍是下面这样:

SQL
EXPLAIN (COSTS OFF) SELECT * FROM users u WHERE u.user_id NOT IN (SELECT o.order_user_id FROM orders o WHERE o.order_status = 'paid');
TEXT
Seq Scan on users u
  Filter: (NOT (ANY (user_id = (hashed SubPlan 1).col1)))
  SubPlan 1
    ->  Seq Scan on orders o
          Filter: (order_status = 'paid'::text)

译注:准确地说,右侧含 NULL 且没有相等匹配时,NOT IN 的结果为 NULL;如果已经有相等匹配,结果仍为 false。这里的 hashed SubPlan 会先构建哈希表,不代表为每个外侧行完整重跑一次子查询。前面的连接转换也针对可安全拉起的子查询,并非所有 IN 或 EXISTS 都有相同计划。

前面介绍的等值条件传播,在这里仍然有效:

SQL
EXPLAIN (COSTS OFF) SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.order_status = 'paid' AND o.order_user_id = u.user_id) AND u.user_id = 100;
TEXT
Nested Loop Semi Join
  ->  Index Scan using users_pkey on users u
        Index Cond: (user_id = 100)
  ->  Gather
        Workers Planned: 1
        ->  Parallel Seq Scan on orders o
              Filter: ((order_user_id = 100) AND (order_status = 'paid'::text))

order_user_id 上没有索引,所以这里出现扫描并不意外,但过滤条件仍被下推到了这一侧。

聚合查询中的读取

最后看一类特殊情况:PostgreSQL 可以把某些不带 GROUP BY 的普通聚合,变成只从索引中获取一个所需值的操作。

SQL
EXPLAIN (COSTS OFF) SELECT min(order_id) FROM orders o;
TEXT
Result
  InitPlan 1
    ->  Limit
          ->  Index Only Scan using orders_pkey on orders o

这很合理。sum 和 count 无法用同样的方式,只看一个值就完成计算,也同样合理:

SQL
EXPLAIN (COSTS OFF) SELECT count(order_id) FROM orders o;
TEXT
Finalize Aggregate
  ->  Gather
        Workers Planned: 1
        ->  Partial Aggregate
              ->  Parallel Seq Scan on orders o

对于带 GROUP BY 的查询,PostgreSQL 也不会进行同样的变换:

SQL
EXPLAIN (COSTS OFF) SELECT order_user_id, min(order_id) FROM orders GROUP BY order_user_id;
TEXT
HashAggregate
  Group Key: order_user_id
  ->  Seq Scan on orders

要优化这种访问方式,需要类似 loose index scan 的索引扫描能力。

译注:loose index scan 可以跳过同一分组键的重复项,与 PostgreSQL 18 用于多列 B-tree 条件查找的 skip scan 是两种优化。此外,本例只有 order_id 的主键索引,没有与 order_user_id 分组顺序匹配的索引;不能仅凭这份计划,把所有分组 min 查询都归为同一种访问方式。

最后几点想法

PostgreSQL 一直在演进。其中一些改进,看起来并没有特别复杂或特别高的风险;相比依赖统计信息的优化,规划器中的这些重写或许也是较容易着手的部分。当然,PostgreSQL 至今没有采用某些变换,也可能有充分的理由。

下一篇再讨论依赖统计信息的查询优化。

原文资料与技术核对

讨论与补充

在 GitHub 查看讨论

支持作者

感谢支持 PostgreSQL Howto 的长期维护。

微信赞赏码

读者交流群

二维码若已过期,可通过公众号 PostgreSQL 学徒或邮件联系。

PostgreSQL 学徒读者交流群二维码