我在调试一个在使用针对PostgreSQL 15的预处理语句的应用中的性能回归。

我的表的数据分布极不均衡：

CREATE TABLE invoice_events 
(
    id BIGSERIAL PRIMARY KEY,
    account_id BIGINT NOT NULL,
    event_type SMALLINT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    amount NUMERIC(12,2) NOT NULL
);
CREATE INDEX idx_invoice_events_account_type_created
    ON invoice_events (account_id, event_type, created_at DESC);

样本数据：

INSERT INTO invoice_events (account_id, event_type, created_at, amount)
SELECT
    CASE
        WHEN g <= 800000 THEN 1
        ELSE (random() * 50000)::bigint
    END,
    (random() * 4)::int,
    now() - (random() * interval '365 days'),
    (random() * 1000)::numeric(12,2)
FROM generate_series(1, 1000000) AS g;

预处理语句：

PREPARE get_events (bigint, smallint) AS
SELECT id, created_at, amount
FROM invoice_events
WHERE account_id = $1
  AND event_type = $2
ORDER BY created_at DESC
LIMIT 20;

当我用低吞吐量账户执行时，它很快：

EXECUTE get_events(45001, 1);

但在对不同值进行多次执行后，同样的预处理语句在某些参数上变得慢得多：

EXECUTE get_events(1, 1);

EXPLAIN (ANALYZE, BUFFERS) EXECUTE get_events(1, 1); 显示的执行计划与等效的非预处理查询不同。

我的预期：

• 预处理语句应该提升性能，或至少保持稳定。

实际情况：

• 经历几次执行后，PostgreSQL似乎在某些参数值上选取了一个效率较低的执行计划。

我尝试了以下方法：

• 在不使用 PREPARE 的情况下运行同样的查询
• SET plan_cache_mode = force_custom_plan
• ANALYZE invoice_events

我的问题是：PostgreSQL如何决定何时将预处理语句从自定义计划切换到通用计划？这样的切换为何会在数据分布偏斜时导致性能变差？

解决方案

PostgreSQL如何决定何时将预处理语句从自定义计划切换到通用计划

请查看 PREPARE 文档中的 [Notes] 小节，示例如下链接中的注释（注释处可以看到具体的实现细节）：

现行规则是：前五次执行使用自定义计划，并计算这些计划的平均估算成本。然后创建一个通用计划，并将其估算成本与平均自定义计划成本进行比较。若后续执行使用通用计划的成本没有明显高于平均自定义计划成本，以致重复重规划看起来更可取，则后续执行将使用该通用计划。

这一启发式可以被覆盖，通过将 [plan_cache_mode] 设置为 force_generic_plan 或 force_custom_plan，分别强制服务器使用通用计划或自定义计划。

除了检查你的 [plan_cache_mode] 设置外，你还可以查看 [pg_prepared_statements 系统视图] 中的 .generic_plans 和 .custom_plans 这两个计数器，以了解你正在遇到的情况。

至多只有一个“切换”會发生，从自定义计划切换到通用计划：如果你看到 pg_prepared_statements.generic_plans 上升，而 explain 显示 $ 参数，你就需要 [deallocate] 并 prepare 它来改变这一点（例如架构变更，如修改列类型也可能触发重新规划）。一旦Postgres认定复用已经成型的通用计划比自定义计划更便宜，再加上它们的规划成本，它就不再尝试这些选项，因此它们将没有机会降成本并重新夺回地位。

在 [src/backend/utils/cache/plancache.c:1152] 中负责这件事的要点是，num_custom_plans 和 total_custom_cost 会在 generic_cost 获胜的一刻冻结，因此从那时起 generic_cost 将继续保持胜利。
（为简洁起见进行了编辑）

static bool
choose_custom_plan(CachedPlanSource *plansource, ParamListInfo boundParams)
{
    double      avg_custom_cost;
    if (plansource->is_oneshot)/* One-shot plans will always be considered custom */
        return true;
    if (boundParams == NULL)/* Otherwise, never any point in a custom plan if there's no parameters */
        return false;
    if (!StmtPlanRequiresRevalidation(plansource))/* ... nor when planning would be a no-op */
        return false;
    /* Let settings force the decision */
    if (plan_cache_mode == PLAN_CACHE_MODE_FORCE_GENERIC_PLAN)
        return false;
    if (plan_cache_mode == PLAN_CACHE_MODE_FORCE_CUSTOM_PLAN)
        return true;
    /* See if caller wants to force the decision */
    if (plansource->cursor_options & CURSOR_OPT_GENERIC_PLAN)
        return false;
    if (plansource->cursor_options & CURSOR_OPT_CUSTOM_PLAN)
        return true;
    if (plansource->num_custom_plans < 5)/* Generate custom plans until we have done at least 5 (arbitrary) */
        return true;
    avg_custom_cost = plansource->total_custom_cost / plansource->num_custom_plans;
    if (plansource->generic_cost < avg_custom_cost)
        return false;
    return true;
}

它们通常会随每一个新的自定义计划而增加，但随后就不再生成。

为什么这会让数据分布偏斜的场景下性能变差

先填充表、添加索引，考虑加入扩展统计信息，然后在分配预处理语句之前执行 [analyze]。数据存在且有索引并不意味着 [pg_stats] 已经具备足够的能力来帮助规划器完成工作，因此你可能会冻结一个次优的执行计划。

无论执行多少次 [vacuum]、[analyze]、[create index]、[reindex]、[cluster]、[create statistics]、或 [alter table..alter column..set statistics] 等通常有助于规划器的操作，都不会影响已存在且已选定了通用计划的预处理语句，除非你使用 force_custom_plan、auto 决定坚持进行自定义（重新）规划，或某些DDL触发了重新规划并把它推低到 avg_custom_cost。

你所看到的性能问题并非真正源自数据的偏斜，而更可能是在偏斜尚未被规划器明确识别时就制定并冻结了执行计划。毕竟，新产生的更快计划恰恰也是由同一个规划器创建，只是使用了更新的、改进的统计信息——这表明如果你现在重新分配该语句，较劣的通用计划很难走上风头。

如果内部规划器现在能够提出改进的自定义计划，它也可能提出改进的通用计划，或至少更有把握地决定坚持使用自定义计划。

关于把计划选择从数据库端移出的想法：

别忘了，Postgres已经内置了一个规划器，它的职责就是在信息充分时做出良好的计划。若你确信能够做到以下几点：

1. 清晰定义同一查询的不同变体，并确认它们确实受益于不同的执行计划。
2. 在规划器能快速生成全新计划（在你的用例中大约 ~70μs）之前，决定使用哪一个。
3. 定义支撑该选择的规则，并比内置的 [统计信息系统] 和 [集群配置] 更好地跟踪它们所依赖的输入。
4. 确认上述1-3点确实优于内置规划器。
5. 确认这里的所有5 点保持静态：规则、查询变体和数据画像不应发生变化。

那么这可能是值得尝试的解决方案。这仍然会是对内置规划器能力的极大简化实现，但如果你发现这个方案比让规划器为你完成它更容易实现，那么它仍然是一个可行的替代方案。在你的例子里，这意味着将语句分配为两条独立的预处理语句，其中“较少使用”的那条作为附加项：

PREPARE get_events_account_1 (smallint) AS
SELECT id, created_at, amount
FROM invoice_events
WHERE account_id = 1
  AND event_type = $1
ORDER BY created_at DESC
LIMIT 20;

你需要通过切换 [plan_cache_mode] 来明确你是在寻求通用计划还是自定义计划。如果你也使用，或计划使用 [例程]，它们也缓存计划（链接）。

你潜在的性能提升大多可能会被完全抵消，不仅因为一次重大版本升级，甚至因为一次小版本升级，或数据分布的微小变化，或业务逻辑中的微小改动。

开发和维护这类功能，可能会比迁移到更近期的版本、或短暂尝试替代的 [分区、索引和查询策略]、重新审视（normalisation）、[表空间]、[压缩与存储模式]、调优 [集群配置] 的工作量要大得多——显然，这要在确保前文提到并链接的辅助内部规划器的方法都尝试穷尽之后。

内置规划器对这些都能自然地做出反应。所谓的“DIY计划选择器”则需要不断重新评估和手动调整。