[译] 关于 Postgres 计划缓存模式管理

我参加了 PGConf.DE’2025 会议，并讨论了在大型数据库上使用 Postgres 的实践。我惊讶地发现，查询规划时间是一个重要问题。作为一名开发人员，我惊讶地发现，这个因素可能会减缓迁移到分区模式的决策，而一旦表中的记录数超过 1 亿，迁移到分区模式似乎是一个合乎逻辑的步骤。好吧，让我们来弄清楚。

解决这种情况的明显方法是使用准备好的语句，这些语句最初旨在重用劳动密集型的部分，例如解析树和查询计划。有关更多细节，让我们看一个包含大量分区的简单表扫描（参见初始化脚本）：

EXPLAIN (ANALYZE, COSTS OFF, MEMORY, TIMING OFF)
SELECT * FROM test WHERE y = 127;

/*
...
   ->  Seq Scan on l256 test_256
         Filter: (y = 127)
 Planning:
   Buffers: shared hit=1536
   Memory: used=3787kB  allocated=4104kB
 Planning Time: 61.272 ms
 Execution Time: 4.929 ms
*/

在这个涉及从包含 256 个分区的表中进行选择的场景中，我的笔记本电脑的 PostgreSQL 在规划阶段大约花费了 60 毫秒，而执行阶段仅花费了 5 毫秒。在规划过程中，它分配了 4 MB 的 RAM 并访问了 1,500 个数据页。对于生产环境来说，这开销相当大！在这种情况下，PostgreSQL 生成了一个自定义计划，该计划在每次执行查询时都会重新编译，并在优化期间根据查询参数值选择执行策略。为了提高效率，让我们通过执行 PREPARE 将此查询参数化并将其存储在后端的“计划缓存”中：

PREPARE tst (integer) AS SELECT * FROM test WHERE y = $1;
EXPLAIN (ANALYZE, COSTS OFF, MEMORY, TIMING OFF) EXECUTE tst(127);

/*
...
   ->  Seq Scan on l256 test_256
         Filter: (y = $1)
 Planning:
   Buffers: shared hit=1536
   Memory: used=3772kB  allocated=4120kB
 Planning Time: 59.525 ms
 Execution Time: 5.184 ms
*/

由于使用了自定义计划，因此规划工作负载保持不变。让我们强制后端生成并使用一个“通用”计划：

SET plan_cache_mode = 'force_generic_plan';
EXPLAIN (ANALYZE, COSTS OFF, MEMORY, TIMING OFF) EXECUTE tst(127);

/*
...
  ->  Seq Scan on l256 test_256
         Filter: (y = $1)
 Planning:
   Memory: used=4kB  allocated=24kB
 Planning Time: 0.272 ms
 Execution Time: 2.810 ms
*/

首次执行查询时，会创建一个通用执行计划（为了使示例更直观，我们在此使用强制模式）。此过程所需的资源几乎与构建自定义计划所需的资源相当。但是，当再次执行查询时，可以快速从缓存中检索通用计划。因此，准备查询计划所花费的时间降至仅 0.2 毫秒，内存使用量仅为 24 KB，并且无需读取任何数据页。看来我们获得了明显的优势！

然而，我提出的使用该PREPARE命令的建议经常遭到拒绝和质疑。这主要是因为通用计划在实践中存在一些问题，尤其是在更新（重新规划）和切换到自定义计划类型方面。为了更清楚地理解通用计划机制的构建方式并探究这些问题的根源，我决定调查该项目的历史。此外，我还计划尝试一种新的发布格式，例如邮件列表评论。

通用计划有什么问题？

查阅 PostgreSQL 的 Git 历史记录后发现，计划缓存的概念似乎是在 2007 年的提交 b9527e9 中引入的。当时，PostgreSQL 决定每个准备好的查询都应使用通用计划单独执行，从而避免在重建计划上花费不必要的时间。与 Oracle、SQL Server、DB2 以及其他同行不同（参见链接、链接、链接和链接），PostgreSQL 使用“完全不确定性”概念构建通用计划，而不使用任何特定的“引用”参数值。例如，在提到的示例中，常量“127”在通用计划创建期间被预留。

由于优化器估算扫描选择性的能力有限，它通常依赖于某些预定义常量的默认“魔法”值。因此，通用计划的质量通常低于自定义计划。让我再举一个例子来更清楚地说明这一点（参见复现脚本）：

EXPLAIN
SELECT * FROM test_2
WHERE
  start_date > '2025-06-30'::timestamp - '7 days'::interval;
/*
 Index Scan using test_2_start_date_idx on test_2  (rows=739)
   Index Cond: (start_date > '2025-06-23 00:00:00'::timestamp)
*/

PREPARE tst3(timestamp) AS SELECT * FROM test_2
  WHERE start_date > $1 - '7 days'::interval;

EXPLAIN EXECUTE tst3('2025-06-30'::timestamp);
/*
 Seq Scan on test_2  (rows=333333)
   Filter: (start_date > ($1 - '7 days'::interval))
*/

简单来说，以下是一些需要考虑的关键原因：不等式运算符中缺少常量，导致过滤器估算值为 33%；对于范围过滤器，默认值设置为表总行数的 0.5%；使用等式运算符时，无法使用 MCV 统计信息，因此我们必须完全依赖 ndistinct 值。此外，在某些情况下，使用部分索引是不可行的。

让我们回顾起源

由于缺乏替代方案，性能显著下降，并且通常实用的PREPARE/EXECUTE语句结构也很少使用。2011年，一场讨论开始了，最终促成了e6faf91提交，该提交引入了一种简单的自动切换技术，用于在自定义计划变体和通用计划变体之间切换。

这场讨论始于一个紧迫的问题：预处理语句的执行完全依赖于通用计划（Mark Mielke，链接）。虽然这些计划在每次收到无效信号时（例如执行ANALYZE或ALTER TABLE命令后）都会重建，但计划的质量明显较差。

为了解决这个问题，提出了几种想法：

1. 定期重新制定通用计划（Jeroen Vermeulen，链接）。
2. 为“计划/执行时间”比率引入阈值 - 如果标准值大于 100，则仅使用通用计划；如果小于 0.01，则仅使用自定义计划。（Bart Samwel链接。Yeb Havinga 反对（链接）此想法 - 客观标准不应包含“时间”参数）。然而，Jeroen Vermeulen 和 Greg Stark（链接）支持此想法，并指出计划和执行时间之间的差异应该显著，达到数量级。
3. 跟踪执行特定查询计划的各种参数的标准偏差（stddev）值，这将有助于估计查询下次计划和执行所花费时间的概率（Greg Stark，链接）。
4. 制定几个自定义和通用计划，并根据成本比率做出选择（Tom Lane，链接）。
5. 完全放弃通用计划，同时通过保留 PlannerInfo 优化器“缓存”并仅重新规划实际使用参数的连接树/子查询部分来降低重新规划的成本（Yeb Havinga，链接）。
6. 使用通用计划，但引入重新计划标准 - 参数值是否在 MCV 范围内（Robert Haas（链接，链接），Jeff Davis 支持）。

有趣的是，重新优化的想法当时就已经有人讨论过了（Richard Huxton，链接）。当时，这更像是一个梦想，但到了 2020 年代，代码基础设施已经足够成熟，使我们能够在短时间内实现类似的概念（参见replan ）。我们在sr_plan中实现了通过简单协议检测、泛化和缓存频繁到达语句的方法，这种方法也在这里得到了明确的描述（Robert Haas，链接），同时 Yeb Havinga 也提出了通过类似于当时尚不存在的 queryId 的方法来实现这一点的想法（链接）。

与此同时，Simon Riggs 在 2011 年提出了一次性计划的概念。这种计划的主要思想是告知 DBMS，查询计划将被创建、立即执行，并在完成后销毁。这种方法允许应用额外的优化，这些优化在计划阶段和执行阶段之间没有联系时是无关的。

为了支持这一想法，Simon 提供了一个涉及稳定函数计算的示例，这将使分区修剪的执行更加高效。此外，Bruce Momjian 还强调了另一个可以在一次性计划中实现的潜在优化：分析缓冲区缓存以评估使用特定索引的有效性。

与此同时，Tom Lane 也在开发类似的功能，其动机是人们抱怨动态 SQL查询中的回归问题（链接，链接）。然而，他的方法与 Simon Riggs 的原始概念不同。Tom Lane 的想法侧重于通过使用计划缓存来统一SPI、PREPARE和扩展协议的机制。因此，Riggs 的原始想法并没有得到进一步的开发，尽管后来对其进行了讨论（链接，链接）。

由于汤姆·莱恩 (Tom Lane) 的反对，跟踪查询的计划和执行时间的概念并未获得关注，他反对使用这个时间特性，因为它本质上是不可预测的，并且在不同的系统之间可能表现不一致。

2017 年，Pavel Stehule提出，需要明确控制调用计划缓存时所选计划的类型。此次讨论促成了plan_cache_mode参数的引入，该参数包含两个选项：force_generic_plan和force_custom_plan。这两个选项分别专门用于使用通用计划类型和自定义计划类型。

作为一名开发人员，我最关注的是这些讨论中出现的 Postgres 核心的几个关键概念的强调。

1. Tom Lane指出，在没有通用解决方案的情况下，我们应该开发启发式方法。通过额外的 GUC 为用户提供此类解决方案是一个糟糕的主意，最终也是一种妥协。
2. Greg Stark 和 Pavel Stehule强调，执行的可预测性比速度更重要。
3. Tom Lane 还指出，在不同查询计划类型之间切换的能力很有价值，前提是它是基于每个查询进行控制的。

结论

通过分析功能创建的历史、社区内表达的意见以及通用计划使用经验的当前知识库，我得出结论，当前的许多问题源于以下问题：

1. 性能不稳定。通用计划的性能可能会根据不同的输入参数值集而产生显著差异。这表明需要切换到自定义计划类型。然而，PostgreSQL 无法自动检测和切换计划，因为它缺乏查询执行的任何统计信息。内核代码的当前状态允许直接实现跟踪各种执行参数，包括平均值和标准差。但是，在我们继续讨论社区提案之前，我们必须解决一个基本问题：PostgreSQL 内核是否应该有一个从执行器到优化器的反馈系统？
2. 过时的自定义/通用成本比例。例如，当某个计划由于表统计信息更新而失效时，通用计划会重建，并重新计算其成本。然而，自定义计划不会发生这种情况。由于自定义计划的成本不会重新计算，因此由于表内容的逐渐变化，存储在计划缓存中的值可能与实际情况存在显著差异。这种差异通常会导致使用通用计划的情况，即使自定义计划的效率显而易见，并且可以由规划器在重新规划期间确定。
3. 计划成本不足。一个常见问题是，错误的估算导致查询计划成本与实际工作负载无关。因此，在自定义计划和通用计划之间做出选择很大程度上取决于运气。

我们能提出什么建议？

经过多年的代码开发和测试，我们能产生什么新的想法吗？像往常一样，有两种独立的解决方案设计：一种是社区的核心部分，另一种是可扩展的代码，甚至可能包含可以合并到 Postgres 分支中的核心补丁。

对于核心版本，我们可以考虑重置缓存计划上的自定义计划统计信息，类似于在发生计划无效调用时对通用计划执行的操作。这将触发一个新的计划选择周期。这种方法很容易证明其合理性，因为统计信息构成了计算计划成本的基础。当统计信息发生变化时，相当于切换到不同的坐标系，因此需要重新计算所有成本。

第二种方案争议更大：我们可以为通用计划创建过程引入一种新的“引用”模式。此模式将使用当前常量作为规划器的参考值。虽然它可能不会带来任何根本性的优势，但它将为用户提供一个熟悉的工具来影响查询计划，尤其是对于那些从 SQL Server 迁移过来的用户来说。

与往常一样，实现核心“计划切换钩子”以利用扩展中的计划切换方法是有意义的。

如果我们将编码选项扩展到企业领域，我们就可以探索更复杂的计划切换技术。例如，我们可以跟踪每个计划的计划和执行时间的统计数据，比较它们的相对权重与成本值，并决定是否重新计划，甚至强制执行特定类型的计划。更好的选择可能是使用更稳定的参数，例如读取的页数。

为了更加客观，您可以查看该项目，其中包括用于管理计划类型的自动化系统草案，以及另一个概述强制模式之间切换草案的分支。

您在使用通用计划时遇到过问题吗？开发一套全面的计划切换系统是否有意义？还是只需实现一个扩展，让每个特定的预处理语句都能监控其状态，并使用 SQL 工具手动更新就足够了pg_stat_statements？

原文地址：https://danolivo.substack.com/p/on-postgres-plan-cache-mode-management
原文作者：Andrei Lepikhov