第 6 次执行后，PostgreSQL 执行计划为何突变？

引言

在 PostgreSQL 中，预处理语句通常用于提升性能并防止 SQL 注入。但一个不易察觉的行为是：查询规划器会在执行达到特定次数后自动改变执行计划。

这种变化往往令人困惑——SQL 本身未发生变化，执行计划却突然发生切换，有时甚至带来明显的性能波动。其根本原因在于规划器对自定义计划与通用计划的选择策略，而该策略由参数 plan_cache_mode 控制。

定制计划与通用计划的核心差异

预编译语句携带参数执行时，优化器会在两种计划模式中抉择：

1. 自定义计划（Custom Plan）:基于实际参数值生成执行计划。针对性强，但每次执行都需要重新规划。
2. 通用计划（Generic Plan）:在未知参数值的情况下生成一次执行计划，并在后续复用，从而减少规划开销。

plan_cache_mode 默认值为 auto，此模式下，优化器前 5 次执行均采用定制计划；第 6 次执行时，会对比前 5 次定制计划的平均代价与通用计划的预估代价，若通用计划代价更低或相等，则当前会话将永久切换为通用计划。

使用 pgbench 进行验证

为了观察该行为，构造一个具有明显数据倾斜的测试场景：

### In bash:
pgbench -i -s 10 -U postgres postgres

### In psql:
ALTER TABLE pgbench_accounts ADD COLUMN flag CHAR(1) NOT NULL DEFAULT 'Y';
UPDATE pgbench_accounts SET flag = 'N' WHERE aid <= 1000;
CREATE INDEX idx_accounts_flag ON pgbench_accounts(flag);
ANALYZE pgbench_accounts;

SELECT flag, count(*) FROM pgbench_accounts GROUP BY flag;

 flag | count
------+--------
 N    |   1000
 Y    | 999000

在切换发生之前，分别强制两种模式，观察同一 SQL 的执行计划。

-- Custom plan: planner sees the literal value 'Y', looks it up in column
-- statistics (MCV frequency ≈ 0.999), and picks Seq Scan for 999,033 rows.
SET plan_cache_mode = force_custom_plan;
PREPARE flag_lookup(char) AS
  SELECT aid, abalance FROM pgbench_accounts WHERE flag = $1;

EXPLAIN EXECUTE flag_lookup('Y');

QUERY PLAN
-------------------------------------------------------------------------
Seq Scan on pgbench_accounts  (cost=0.00..28910.00 rows=999033 width=8)
Filter: (flag = 'Y'::bpchar)   <-- literal value 'Y' indicates custom plan

DEALLOCATE flag_lookup;

-- Generic plan: the planner has no value to look up. With ndistinct = 2
-- (only 'Y' and 'N' exist), it estimates 1/ndistinct = 50% selectivity,
-- or 500,000 rows. At that estimate, the cheaper path is Index Scan.
SET plan_cache_mode = force_generic_plan;
PREPARE flag_lookup(char) AS
  SELECT aid, abalance FROM pgbench_accounts WHERE flag = $1;

EXPLAIN EXECUTE flag_lookup('Y');

QUERY PLAN
--------------------------------------------------------------------------------------------
 Index Scan using idx_accounts_flag on pgbench_accounts  (cost=0.42..19322.07 rows=500000)
   Index Cond: (flag = $1)   <-- Note the placeholder $1 instead of literal 'Y'/'N'

执行后数据分布为：N 对应 1000 行（0.1%），Y 对应 999000 行（99.9%）。

自动切换行为的触发过程

将 plan_cache_mode 恢复为默认的 auto 模式后，使用高频取值 Y 对预编译语句连续执行五次。每次执行均生成代价约为 28910 的定制化全表扫描计划。

五次执行完成后，优化器会对两类计划代价进行对比：定制计划平均代价约为 28910，通用计划预估代价约为 19322。

由于通用计划代价小于等于定制计划平均代价，从第六次执行开始，优化器将固定选用通用计划。

DEALLOCATE flag_lookup;
SET plan_cache_mode = auto;
PREPARE flag_lookup(char) AS
  SELECT aid, abalance FROM pgbench_accounts WHERE flag = $1;

-- Executions 1–5: custom plans, each resolving 'Y' literally
EXPLAIN (COSTS OFF) EXECUTE flag_lookup('Y');
EXPLAIN (COSTS OFF) EXECUTE flag_lookup('Y');
EXPLAIN (COSTS OFF) EXECUTE flag_lookup('Y');
EXPLAIN (COSTS OFF) EXECUTE flag_lookup('Y');
EXPLAIN (COSTS OFF) EXECUTE flag_lookup('Y');

五次执行均输出相同计划：

QUERY PLAN
--------------------------------
 Seq Scan on pgbench_accounts
   Filter: (flag = 'Y'::bpchar)

第六次执行时执行计划发生变化：

EXPLAIN (COSTS OFF) EXECUTE flag_lookup('Y');
                       QUERY PLAN
--------------------------------------------------------
 Index Scan using idx_accounts_flag on pgbench_accounts
   Index Cond: (flag = $1)

第六次调用时，执行计划由全表扫描切换为索引扫描，查询语句与数据均未发生变化。计划中出现的 $1 占位符，表明当前已启用通用计划。

计划切换后的不可逆性

自第六次执行起，无论传入何种参数值，查询均会沿用已生成的通用索引扫描计划。参数值 N 对应 1000 行数据，索引扫描恰好保持高效；而参数值 Y 对应 999000 行数据，通过索引随机访问接近全表的数据，效率远低于顺序扫描。

-- 第七次及后续执行，无论参数值均使用通用计划
EXPLAIN (COSTS OFF) EXECUTE flag_lookup('Y');
EXPLAIN (COSTS OFF) EXECUTE flag_lookup('N');

两次执行均输出相同计划：

QUERY PLAN
--------------------------------------------------------
 Index Scan using idx_accounts_flag on pgbench_accounts
   Index Cond: (flag = $1)

通用计划一旦启用，会持续生效直至执行 DEALLOCATE flag_lookup 释放预编译语句或当前会话终止。该机制在高频执行的预编译语句场景中需重点关注，在实际业务场景中已对系统可用性产生过显著影响。

底层源码逻辑解析

切换阈值固定为 5 次的逻辑，可在 PostgreSQL 源码 src/backend/utils/cache/plancache.c 文件的 choose_custom_plan 函数中找到明确定义：

static bool
choose_custom_plan(CachedPlanSource *plansource)
{
    /* ... settings check for force_custom / force_generic ... */

    /* If we haven't done 5 custom plans yet, keep doing them */
    if (plansource->num_custom_plans < 5)
        return true;

    /* * Otherwise, compare generic_cost against the average custom_cost.
     * If the generic plan is cheaper (or equal), we switch!
     */
    if (plansource->generic_cost <= plansource->total_custom_cost / plansource->num_custom_plans)
        return false;

    return true;
}

实际应用优化建议

PostgreSQL 查询优化器的自动计划缓存机制通常可节省 CPU 资源，但在数据分布高度倾斜、临时对象频繁变动的场景下，第 6 次执行的计划切换可能导致业务性能异常。

若预编译语句出现无明确诱因的性能退化，可核查执行次数是否超过 5 次，或通过设置 plan_cache_mode = force_custom_plan 进行故障排查，强制每次执行生成新的定制计划，确保优化器基于实际参数选择最优执行策略。

原文链接：

richyen.com/postgres/20…

作者：Richard Yen