返回日报条目

资料节译

Postgres 连接分组可能阻止索引查找计划生成

阅读原文

**适用人群:**遇到过本该很快却很慢的 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):

  1. 这是一个 LEFT 连接。
  2. 右侧是一张单独的表。
  3. 该表至多只能匹配一行,这一点由连接列上的主键或唯一索引证明。

该表的任何列都没有在连接上方的任何地方使用:SELECT、WHERE、ORDER BY,或另一个连接的 ON 子句。

如果四项条件都成立,移除该表不会改变结果,因此 Postgres 会将它删掉。在我们的查询中,移除操作把约 435 个引用减少到了约 11 个。

第 4 条说明了为什么你选择的列很重要。只选 2 列时,最终只剩 3 张表,使用默认设置时查询很快。报告选了 27 列,最终剩下 10–11 张表,查询就很慢。每多选一列,都可能让一张原本会被移除的表重新留下来。

要连接 N 张表,规划器会尝试不同的顺序。可能的顺序数量按 N! 增长:

表数可能的顺序数(约 N!)
424
840,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):

行号调用作用
184joinlist = deconstruct_jointree(root);遍历整个连接树一次,在这次遍历中做两件事:(A) 收集规划器需要的信息(哪些列在哪里使用、每个连接条件是什么样),以及 (B)构建连接列表,并应用 join_collapse_limit
221joinlist = 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 决策数739739
合并656730
拒绝(切分)839
独立的连接问题7545
enrollment这张表最终所在组的大小1(单独一组)5
执行时间(真实数据副本)137,779 ms91 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,正好等于上限。它以零的余量通过了检查。

看看每次决策会返回什么:

所以,在树的较低层成功合并会让列表变长,而这个更长的列表随后可能在更高层检查时失败。较低层拒绝合并会让列表变短,随后就可能通过更高层的检查。改变上限,底层的一些决策就会翻转,进而让更高层的其他决策也翻转。随着上限变化,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不起作用:Postgres 会打印警告并保留旧值。确保你的代码确实开启了事务;

SET LOCAL也会在 COMMIT 时重置,因此该设置不会通过连接池泄漏到下一个请求。

这个设置只是权宜之计。以下做法能修复根本原因:

**在热点查询中少选几列。**先获取 25 个 ID,再只查询这 25 条记录的详细信息,在默认上限下耗时 5.5 ms,完全不需要任何规划器设置。

**添加连接移除所需的唯一索引。**如果 Postgres 无法证明连接唯一,任何设置都无法移除它。

在热点路径中减少视图层数。

再补充一个实在的数字:由于视图展开,这个查询光规划就耗时约 440 ms。这是另一项开销,该设置对此没有影响。

2026 年 8 月 28 日,Tom Lane 提交了 2ebf25e7d7:“通过编辑查询的 jointree 来执行连接移除”。

改动前(15.8 到最新发布版本):

  1. 构建信息和连接列表(计算所有表);
  2. 移除无用连接,手动修补规划器的记录;
  3. 使用旧的连接列表进行规划。

改动后:

  1. 构建信息和连接列表;
  2. 通过从查询树本身中切除无用连接来移除它们(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 ms0.57 ms
由 64 次依赖移除构成的链1.98 ms20.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 中层层建立视图吗?在规划器开始跟你较劲之前,你最多套了多少层?

参考资料(公开)