**适用人群:**遇到过本该很快却很慢的 Postgres 查询,并且 EXPLAIN 显示了糟糕的计划却没说明原因的人。你不需要了解规划器内部机制。本文从零开始解释,并给出实际代码行。
**简而言之:**在 Postgres 15 和迄今发布的所有版本中,规划器会先把你的连接拆分成多个组,然后才移除不需要的连接。如果需要通过索引查找的两张表落在不同组里,就根本无法构建快速计划。join_collapse_limit设定组的大小,而“安全”的值并非一个连续范围:其中有些值会失效。Postgres 16 及之后的版本有一项改动能有所帮助,但问题并未彻底消失。
我所在平台上的一份报告开始超时。一个带几个筛选条件运行的查询耗时 137,779 ms:超过两分钟。
这个查询读取一个视图。该视图建立在其他视图之上,而那些视图又建立在更多视图之上(我们称之为分层视图)。看起来就像一个简单的SELECT ... FROM report_view WHERE ...。但底层一点也不简单。
EXPLAIN ANALYZE 清楚地显示了耗时的部分:
Nested Loop (rows=1) (actual time=1112..139689 rows=1005)
Join Filter: (enrollment.course_id = course.id)
Rows Removed by Join Filter: 8,944,739
-> Bitmap Heap Scan on course (estimated rows=1, actual rows=181)
-> Hash Left Join ... (loops=181)
-> Hash (rows=895194) (actual time=538.801) loops=181 ← 538.8 ms × 181 = 97.5 s
有两个问题同时存在:
计划形态。Postgres 把连接两侧都完整构建出来,之后才逐行比较(Join Filter )并丢弃 890 万行。更快的做法应该是:对 181 门课程逐一通过索引查找其选课记录。计划看起来会像Index Cond: (course_id = course.id) 。
2.
行数估算。Postgres 预期有1门课程,实际却有181门。它以为一个昂贵的内部步骤只会运行一次,因此制定了相应计划。实际上该步骤运行了 181 次。
仅修正计划形态就足够了:内部步骤耗时降至约 0.06 ms,因此运行 181 次也不再要紧。本文其余部分将讨论计划形态,因为这是 EXPLAIN 无法解释的部分。
我收到的第一个建议(来自 AI 助手)是调高一个规划器设置:join_collapse_limit。这个办法奏效了。报告变快了。
但我不知道它为什么奏效,所以我在真实数据的副本上,把 8 到 140 的每个值都测了三次:
8 10 12 14 16 18 20 22 24 26 28 30 32 34 ............ 140
✗ ✗ ✓ ✗ ✓ ✓ ✓ ✓ ✓ ✗ ✗ ✓ ✗ ✓ ✓ ✓ ✓ ✓ ✓ ✓ ✓
| 数值 | |
|---|---|
| 慢(糟糕计划) | 8, 10, 14, 26, 28, 32 |
| 快(索引查找) | 12, 16–24, 30, 34 及以上 |
设为 32 时,查询耗时 140,611 ms。设为 34 时,耗时 23.9 ms。只把一个数字改了两点,速度就快了近 6,000 倍。
**这不是一个阈值,而是一个有缺口的范围。**如果这个设置只是“越大越好”,14 就不可能比 12 慢。没人能告诉我原因,所以我去看了 Postgres 源代码。
视图是保存下来的 SQL。查询视图时,Postgres 会在规划之前把视图的 SQL 粘贴到你的查询中。视图之上再套视图,就会递归地粘贴。我们那条单行查询最终变成了约 435 个表引用:仅一个内层视图就贡献了 121 个,而且它出现了三次。
这 435 个表里,大多数对这份报告都没用。满足以下所有条件时,Postgres 可以移除一个 LEFT JOIN(join_is_removable、src/backend/optimizer/plan/analyzejoins.c:159,适用于 15.8):
- 这是一个 LEFT 连接。
- 右侧是一张单独的表。
- 该表至多只能匹配一行,这一点由连接列上的主键或唯一索引证明。
该表的任何列都没有在连接上方的任何地方使用:SELECT、WHERE、ORDER BY,或另一个连接的 ON 子句。
如果四项条件都成立,移除该表不会改变结果,因此 Postgres 会将它删掉。在我们的查询中,移除操作把约 435 个引用减少到了约 11 个。
第 4 条说明了为什么你选择的列很重要。只选 2 列时,最终只剩 3 张表,使用默认设置时查询很快。报告选了 27 列,最终剩下 10–11 张表,查询就很慢。每多选一列,都可能让一张原本会被移除的表重新留下来。
要连接 N 张表,规划器会尝试不同的顺序。可能的顺序数量按 N! 增长:
| 表数 | 可能的顺序数(约 N!) |
|---|---|
| 4 | 24 |
| 8 | 40,320 |
| 11 | 约 4000 万 |
因此 Postgres 用两个设置来保护自己:
join_collapse_limit(默认值为 8):显式使用JOIN语法书写的表会被分组成列表,每个列表最多包含这么多项。规划器会搜索每个列表内部的连接顺序,并把每个已完成的列表视为一个固定整体。
geqo_threshold(默认值为 12):如果一个列表包含 12 项或更多,Postgres 就会停止穷举搜索,转而使用GEQO,也就是一种遗传(随机化)优化器。此时计划可能每次运行都不同。
以下是 Postgres 15.8 中规划器主函数query_planner()内部的执行顺序(src/backend/optimizer/plan/planmain.c):
| 行号 | 调用 | 作用 |
|---|---|---|
| 184 | joinlist = deconstruct_jointree(root); | 遍历整个连接树一次,在这次遍历中做两件事:(A) 收集规划器需要的信息(哪些列在哪里使用、每个连接条件是什么样),以及 (B)构建连接列表,并应用 join_collapse_limit |
| 221 | joinlist = remove_useless_joins(root, joinlist); | 然后移除无用的 LEFT JOIN |
**连接列表在第 184 行构建,计算了约 435 张表。无用表在第 221 行移除,而连接列表从未重建。**问题的全部症结就在这里。
deconstruct_recurse会自底向上遍历连接树。在每个JOIN处,它都会取得左侧列表和右侧列表,并应用一条规则(src/backend/optimizer/plan/initsplan.c:1037-1058):
else if (list_length(leftjoinlist) + list_length(rightjoinlist) <=
join_collapse_limit)
{
/* OK to combine subproblems */
joinlist = list_concat(leftjoinlist, rightjoinlist);
}
else
{
/* can't combine, but needn't force join order above here */
...
joinlist = list_make2(leftpart, rightpart);
}
通俗地说:
左侧项 + 右侧项 ≤ 上限 → 合并成一个扁平列表(会尝试它们之间的所有顺序);
否则 → 不合并;恰好返回两项:左侧作为一个子列表,右侧作为另一项。
举个简单例子。A JOIN B JOIN C,上限设为 2(Postgres 会将其读取为(A JOIN B) JOIN C):
| JOIN 节点 | 左侧列表 | 右侧列表 | 总数 | ≤ 2? | 结果 |
|---|---|---|---|---|---|
A JOIN B | [A] | [B] | 2 | 是 | [A, B] |
(…) JOIN C | [A, B] | [C] | 3 | 否 | [[A, B], C] |
结果[[A, B], C]表示:先单独规划 A+B,然后把这个已完成的整体与 C 连接。它甚至不会考虑“A 先与 C 连接”这个顺序。
规划器遇到子列表时,会先完全独立地规划它(make_rel_from_joinlist、src/backend/optimizer/path/allpaths.c:3325)。上一层拿到的是一个已完成的结果,无法查看它的内部结构。
我们想要的快速计划是“对每门课程,用索引查找其选课记录”。Postgres 把这种计划称为参数化嵌套循环。只有当两张表位于同一个列表时,这种计划才可行。如果enrollment位于子列表内部,而course位于它之外,就无法构建这种查找。Postgres 并不是因为认为慢计划更便宜才选择它;快速计划根本不是一个可选项。
当remove_useless_joins移除一张表时,它会从连接列表中删除该表的条目(remove_rel_from_joinlist、analyzejoins.c:462)。子列表仍然是子列表:
sublist = remove_rel_from_joinlist((List *) jlnode, relid, nremoved);
/* Avoid including empty sub-lists in the result */
if (sublist)
result = lappend(result, sublist);
因此,虽然只剩约 11 张表,但在计算约 435 张表时划出的边界仍然保留着。在默认上限 8 下,course和enrollment落在边界的两侧。
Postgres 自己的代码承认,这个顺序是一种折中。移除调用正上方就有这样一条注释:
/*
* Remove any useless outer joins. Ideally this would be done during
* jointree preprocessing, but the necessary information isn't available
* until we've built baserel data structures and classified qual clauses.
*/
换句话说:判断“这个连接是无用的”所需的信息,是在构建连接列表的同一次遍历中收集的。所以等 Postgres 确认哪些连接无用时,分组早已划定。
读代码让我知道了可能发生什么。为了看看实际发生了什么,我从官方源码编译了 PostgreSQL 15.8,并在deconstruct_recurse(每个 JOIN 决策)和make_rel_from_joinlist(每个最终列表)中添加了日志行:
elog(NOTICE, "JOIN|right=%s|L=%d|R=%d|sum=%d|jcl=%d|act=%s", ...);
我加载了数据库的仅含架构副本(包含表、视图以及全部 270 个主键和唯一约束,没有数据行),并运行了报告查询。
**提示:**约束很重要。我最初的副本没有主键,因此 Postgres 无法证明任何连接是无用的,也就没有移除任何内容;跟踪日志显示连接列表有 766 个条目,而不是 45 个。没有唯一约束的实验测量的是另一棵树。
每次运行得到的结果:
| 上限 = 8 | 上限 = 64 | |
|---|---|---|
| 评估过的 JOIN 决策数 | 739 | 739 |
| 合并 | 656 | 730 |
| 拒绝(切分) | 83 | 9 |
| 独立的连接问题 | 75 | 45 |
enrollment这张表最终所在组的大小 | 1(单独一组) | 5 |
| 执行时间(真实数据副本) | 137,779 ms | 91 ms |
两种设置下都是同样的 739 次决策。上限让切分次数从 83 次降到 9 次,这就是全部差别。
上限为 8 时,树的顶端出现了三次切分,每次都恰好超过上限:
L + R = sum vs 8 -> decision
7 + 4 = 11 vs 8 -> refuse <-- cut
8 + 2 = 10 vs 8 -> refuse <-- cut
6 + 3 = 9 vs 8 -> refuse <-- cut
上限为 64 时,起决定作用的 JOIN 合并了查询的两部分,分别有 34 个和 30 个表引用:
34 + 30 = 64 vs 64 -> MERGE <-- the merge that puts course and enrollment together
34 + 30 = 64,正好等于上限。它以零的余量通过了检查。
看看每次决策会返回什么:
- 一次合并会返回长度为 L + R 的列表(列表变长);
- 一次拒绝会返回长度为2的列表(列表变短)。
所以,在树的较低层成功合并会让列表变长,而这个更长的列表随后可能在更高层检查时失败。较低层拒绝合并会让列表变短,随后就可能通过更高层的检查。改变上限,底层的一些决策就会翻转,进而让更高层的其他决策也翻转。随着上限变化,course和enrollment是否最终落在一起会反复变化。这就是那些缺口:12 时快,14 时慢,16 时又快。
BEGIN;
SET LOCAL join_collapse_limit = 64;
-- the report query (and its count query)
COMMIT;
137,779 ms → 91 ms。
我自己最初建议的值是 100。跟踪结果显示,设为 100 时树会折叠得太厉害,最大单个列表达到 12 张表,恰好达到geqo_threshold,因此 Postgres 会切换到遗传优化器,计划也就不再确定。通过修改geqo_seed,我在真实数据副本上确认了这一点(如果计划会随种子变化,说明 GEQO 正在运行):
jcl=48 : no GEQO jcl=64 : no GEQO jcl=72 : no GEQO
jcl=80 : no GEQO jcl=100 : GEQO FIRES
这个查询可用的窗口是 48–80:计划快,而且结果确定。64 正好处于窗口中间。
同时调高from_collapse_limit(用于逗号连接和子查询的配套设置)也没有帮助。它反而让几个原本快速的值变慢了。这是另一个轴,也有它自己的缺口。
对其他查询全局设置为 64 是危险的。在另一项测试中,一个无法移除任何表的 12 表连接查询,join_collapse_limit = 64会让 Postgres 切换到 GEQO,而默认值 8 不会。因此:
- 只在运行此查询的事务中,通过**
SET LOCAL**设置;
在事务之外使用SET LOCAL不起作用:Postgres 会打印警告并保留旧值。确保你的代码确实开启了事务;
SET LOCAL也会在 COMMIT 时重置,因此该设置不会通过连接池泄漏到下一个请求。
这个设置只是权宜之计。以下做法能修复根本原因:
**在热点查询中少选几列。**先获取 25 个 ID,再只查询这 25 条记录的详细信息,在默认上限下耗时 5.5 ms,完全不需要任何规划器设置。
**添加连接移除所需的唯一索引。**如果 Postgres 无法证明连接唯一,任何设置都无法移除它。
在热点路径中减少视图层数。
再补充一个实在的数字:由于视图展开,这个查询光规划就耗时约 440 ms。这是另一项开销,该设置对此没有影响。
2026 年 8 月 28 日,Tom Lane 提交了 2ebf25e7d7:“通过编辑查询的 jointree 来执行连接移除”。
改动前(15.8 到最新发布版本):
- 构建信息和连接列表(计算所有表);
- 移除无用连接,手动修补规划器的记录;
- 使用旧的连接列表进行规划。
改动后:
- 构建信息和连接列表;
- 通过从查询树本身中切除无用连接来移除它们(LEFT JOIN 节点替换为其左侧,
analyzejoins.c:399在 master 中:return j->larg;);
丢弃所有记录和连接列表,然后重新开始(goto restart、planmain.c:286在 master 中);
4. 重复以上步骤,直到无法再移除任何内容,然后进行规划。
最终连接列表只根据保留下来的表构建。实际上就是:先移除,再分组。
他这样做不是为了提升速度,而是因为手动修补记录不断引发错误。Bug #19560 就是一个很好的例子(在 19beta2 中报告,是从 16 开始的回归问题;15.x 不受影响):
CREATE TABLE items (id text, owner text);
CREATE TABLE follows (item_id text, user_id text, UNIQUE (user_id, item_id));
INSERT INTO items VALUES ('item1', 'alice');
WITH viewer AS (SELECT 'bob' AS id)
SELECT count(*) FROM items
LEFT JOIN follows ON follows.item_id = items.id AND follows.user_id = 'bob'
LEFT JOIN viewer ON TRUE
WHERE items.owner = viewer.id;
唯一的一条记录属于 alice,而查看者是 bob,因此结果应该是 0。Postgres 却返回了 1。计划最终只剩Aggregate -> Seq Scan on items:WHERE条件消失了。follows可以被移除(有唯一键,且没有使用其列);移除它改变了规划器的内部记录,因此本应创建一个新的筛选条件。但筛选条件此前已经创建,之后没有再检查。
Tom 在提交说明中写道,旧代码“通过原地更新这些结构来实现移除……没有任何机制让它与规划器的其他部分保持同步”,并且“这些年来我们也看到许多相关错误,所以是时候采取一些大刀阔斧的措施了”。
| 版本 | 是否包含修复? |
|---|---|
| 15.x | **从未包含。**该修复只回移到了 16 |
| 16.15、17.11、18.6(最新发布版) | 尚未包含:修复已进入这些分支,但还没有进入任何发行标签 |
| 下一批次版本,2026 年 11 月 12 日 | 预计会包含(稳定分支上的修复通常会随下一个批次版本发布) |
| 19(RC1;最终版计划于 2026 年 11 月 19 日发布) | 是 |
**保留下来的表多于上限。**我们的查询约有 11 张表。11 > 8,因此即使使用新代码,这些表仍会再次分组。在一项合成测试中,上限为 8 时,6 张保留表的耗时从 213–270 ms 降至 33–36 ms;**上限为 8 时,11 张保留表仍然采用同一个糟糕计划。**在 16 及之后版本中,我们仍需要把上限设为至少 11。
**连接链会增加规划时间。**每次重新开始都会重复规划准备工作。独立的无用连接会在一轮中全部移除,但只有在另一项连接移除后才变得可移除的连接,需要等到下一轮。层层嵌套的视图恰好会形成这样的链。Richard Guo 在讨论这项改动时给出了以下测量结果:
| 形态 | 改动前 | 改动后 |
|---|---|---|
| 64 个互相独立的无用连接 | 1.11 ms | 0.57 ms |
| 由 64 次依赖移除构成的链 | 1.98 ms | 20.9 ms |
据我所知,真实的分层视图架构中连接链有多长、会带来多少开销,目前还没有公开测量。
**运行EXPLAIN (ANALYZE, BUFFERS)。**查找你认为应该使用索引的连接上是否出现Join Filter,并确认是否有很大的Rows Removed by Join Filter。与你的预期进行比较:Index Cond: (child.parent_id = parent.id)。
2.
**移除前后都数一下表。**如果查询读取视图,就数数视图展开后包含多少张表(或者查看执行计划)。如果视图中的表远多于计划里显示的表,就说明发生了移除,而分组是在更大的表数基础上完成的。
3.
在事务中试几个上限值:
BEGIN; SET LOCAL join_collapse_limit = 12; EXPLAIN ANALYZE <query>; ROLLBACK;
测试多个值,而不只是一个。要预期会有缺口。
**检查 GEQO。**用两个不同的geqo_seed值运行计划。如果计划发生变化,说明在该上限下遗传优化器正在运行。
2.
**限定作用范围后再部署。**在该查询自己的事务中使用SET LOCAL,绝不要全局设置。视图发生变化时要重新测试:我们成功合并时的余量为零。
3.
**尽可能修复根本原因:**热点查询少选列、补上缺失的唯一索引、减少视图层数。
当工具告诉你哪里慢,却没告诉你为什么时,源代码能解释原因。Postgres 是开源的,规划器中的注释也坦率地说明了它自身的折中。修复一个问题能解决一个查询;弄明白它为什么奏效,才能解决接下来的十个问题。
你会在 Postgres 中层层建立视图吗?在规划器开始跟你较劲之前,你最多套了多少层?
参考资料(公开)
- Postgres 提交
2ebf25e7d7:https://github.com/postgres/postgres/commit/2ebf25e7d70a8fce31ace78d723fa9271ab8af72 - Bug #19560 报告:https://www.postgresql.org/message-id/19560-54cd7ede78d5e355%40postgresql.org
- 讨论串:https://www.postgresql.org/message-id/flat/1186816.1784573544%40sss.pgh.pa.us
- PostgreSQL 文档,显式 JOIN 控制和
join_collapse_limit:https://www.postgresql.org/docs/current/explicit-joins.html - PostgreSQL 路线图(发布日期):https://www.postgresql.org/developer/roadmap/
- 源文件:
src/backend/optimizer/plan/planmain.c、initsplan.c、analyzejoins.c、src/backend/optimizer/path/allpaths.c(行号来自 REL_15_8 和截至 2026-11 的 master)