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。原文使用以下安装命令:
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,应当在独立测试库中运行。
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,让后面的查询计划基于经过维护、已有统计信息的表。
$ 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 会这样做:
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE user_id = 100 + 100;
Index Scan using users_pkey on users
Index Cond: (user_id = 200)
就像直接传入 200 一样:
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE user_id = 200;
Index Scan using users_pkey on users
Index Cond: (user_id = 200)
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE 200 = user_id;
Index Scan using users_pkey on users
Index Cond: (user_id = 200)
列写在等号的哪一侧也没有关系:
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE 100 + 100 = user_id;
Index Scan using users_pkey on users
Index Cond: (user_id = 200)
不过,加法需要发生在常量这一侧。否则,常量折叠和这里的普通索引扫描都不会出现:
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE user_id + 0 = 100;
Gather
Workers Planned: 1
-> Parallel Seq Scan on users
Filter: ((user_id + 0) = 100)
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE 100 = user_id + 0;
Gather
Workers Planned: 1
-> Parallel Seq Scan on users
Filter: (100 = (user_id + 0))
当然,消去“加零”算是一个比较特殊的例子。至少我们知道,可以为表达式建立索引,让规划器有相应的索引路径可选。试试看:
CREATE INDEX ON users ((user_id + 0));
VACUUM ANALYZE users;
这次能用上了:
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE user_id + 0 = 100;
Index Scan using users_expr_idx on users
Index Cond: ((user_id + 0) = 100)
但交换加法的操作数之后,就又不走这个索引了,尽管加法满足交换律:
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE 0 + user_id = 100;
Gather
Workers Planned: 1
-> Parallel Seq Scan on users
Filter: ((0 + user_id) = 100)
对于函数,如果标记为 IMMUTABLE,并且参数也是常量或可折叠的不变表达式,PostgreSQL 就可以在规划时计算它:
CREATE FUNCTION double(int) RETURNS int AS $$
BEGIN RETURN $1 * 2;
END $$ LANGUAGE plpgsql IMMUTABLE;
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE user_id = double(100);
Index Scan using users_pkey on users
Index Cond: (user_id = 200)
足够简单的 SQL 函数也可以:
CREATE FUNCTION triple(int) RETURNS int AS 'SELECT $1 * 3' LANGUAGE sql;
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE user_id = triple(100);
Index Scan using users_pkey on users
Index Cond: (user_id = 300)
译注:
triple没有声明易变性类别,默认是VOLATILE。这里先将符合条件的简单 SQL 函数内联为乘法表达式,再折叠常量;不能据此认为任意VOLATILE函数都能在规划时求值。
如果函数不是 IMMUTABLE,而是 STABLE,仍然可以用于索引条件,但不会这样折叠成常量。例如 now() 的值由事务开始时间确定:
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE 100 + EXTRACT(DAY FROM NOW())::INT = user_id;
Index Scan using users_pkey on users
Index Cond: (user_id = (100 + (EXTRACT(day FROM now()))::integer))
这又不同于表达式包含 VOLATILE 调用,或依赖正在扫描的行时的情况。下面的表达式依赖该行的 user_name:
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE length(user_name) = user_id;
Gather
Workers Planned: 1
-> Parallel Seq Scan on users
Filter: (length(user_name) = user_id)
子查询展开与条件下推
子查询足够简单时,PostgreSQL 会把内外两层查询合并起来:
EXPLAIN (COSTS OFF) SELECT * FROM (SELECT user_id, length(user_name) AS n FROM users) x WHERE x.user_id = 42;
Index Scan using users_pkey on users
Index Cond: (user_id = 42)
但如果变换会改变查询含义,就不能这样处理。例如,子查询带有 LIMIT:
EXPLAIN (COSTS OFF) SELECT * FROM (SELECT user_id, length(user_name) AS l FROM users LIMIT 3) u WHERE u.user_id = 100;
Subquery Scan on u
Filter: (u.user_id = 100)
-> Limit
-> Seq Scan on users
对比原查询与错误改写后的结果,就容易理解了:
SELECT count(1) FROM (SELECT user_id, length(user_name) AS l FROM users LIMIT 3) u WHERE u.user_id = 100;
count
-----
0
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;
count
-----
1
译注:没有
ORDER BY的LIMIT 3不保证选中哪三行,0是原文这次执行的结果。关键差别是先取三行再筛选,还是先筛选再取三行;不能依赖无序查询总会返回同样的计数。
对于更复杂的子查询,PostgreSQL 会尝试把过滤条件向下推:
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;
Finalize GroupAggregate
-> Gather
Workers Planned: 1
-> Partial GroupAggregate
-> Parallel Seq Scan on orders
Filter: (order_user_id = 100)
不只等值条件可以下推:
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;
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 可以利用连接条件,把一个等值过滤条件传播到另一侧:
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;
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)
但下面的不等值条件还不会得到同样的传播:
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;
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,查询没有使用右侧列,并且可以证明每个左侧行在右侧至多匹配一行:
EXPLAIN (COSTS OFF) SELECT o.order_id FROM orders o LEFT JOIN users u ON u.user_id = o.order_user_id;
Seq Scan on orders o
但这并不适用于所有连接。假设给两张表添加外键关系,再执行内连接:右侧的连接列是主键,保证唯一;左侧连接列有外键约束且为 NOT NULL,因此每个左侧行恰好有一个匹配,但连接仍没有被消除。
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;
EXPLAIN (COSTS OFF) SELECT o.order_id FROM orders o JOIN users u ON u.user_id = o.order_user_id;
Hash Join
Hash Cond: (o.order_user_id = u.user_id)
-> Seq Scan on orders o
-> Hash
-> Seq Scan on users u
不过,PostgreSQL 18 新增了对一类内连接的消除:同一张表在唯一列上的自连接。
EXPLAIN (COSTS OFF) SELECT a.order_status, b.order_user_id FROM orders a JOIN orders b ON a.order_id = b.order_id;
Seq Scan on orders b
IN 与 EXISTS
简单的 IN 和 EXISTS 可以被展开,并进行常量折叠:
EXPLAIN (COSTS OFF) SELECT * FROM users u WHERE u.user_id IN (SELECT 100 + 100);
Index Scan using users_pkey on users u
Index Cond: (user_id = 200)
EXPLAIN (COSTS OFF) SELECT * FROM users u WHERE EXISTS (SELECT 1 WHERE u.user_id = 100 + 100);
Index Scan using users_pkey on users u
Index Cond: (user_id = 200)
下面两种更复杂的写法,也会转换为连接,而不是为外侧的每一行分别执行一次子查询:
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');
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
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);
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 也可以转换为反连接:
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);
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,计划仍是下面这样:
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');
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都有相同计划。
前面介绍的等值条件传播,在这里仍然有效:
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;
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 的普通聚合,变成只从索引中获取一个所需值的操作。
EXPLAIN (COSTS OFF) SELECT min(order_id) FROM orders o;
Result
InitPlan 1
-> Limit
-> Index Only Scan using orders_pkey on orders o
这很合理。sum 和 count 无法用同样的方式,只看一个值就完成计算,也同样合理:
EXPLAIN (COSTS OFF) SELECT count(order_id) FROM orders o;
Finalize Aggregate
-> Gather
Workers Planned: 1
-> Partial Aggregate
-> Parallel Seq Scan on orders o
对于带 GROUP BY 的查询,PostgreSQL 也不会进行同样的变换:
EXPLAIN (COSTS OFF) SELECT order_user_id, min(order_id) FROM orders GROUP BY order_user_id;
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 至今没有采用某些变换,也可能有充分的理由。
下一篇再讨论依赖统计信息的查询优化。
原文资料与技术核对
- Phil Eaton / The Consensus:Query plan rewriting in PostgreSQL。
- 查询处理阶段:The Path of a Query;函数属性:Function Volatility Categories。
- 子查询语义:Subquery Expressions;表达式索引:Indexes on Expressions。
- PostgreSQL 18 实现:clauses.c、prepjointree.c、analyzejoins.c。
- 自连接消除与计划选项:Query Planning;扫描方式:Loose indexscan、Multicolumn Indexes。