PostgreSQL 19 查询计划 Advice：别把提示当常规优化，把它当计划熔断器

2026-10-01 14 预计阅读时间: 1 分钟
来源: postgr.es AI 摘要 Original link

Disclaimer: This article is an AI-assisted summary. Read it together with the original source when precision matters. The summary may omit context, version differences, or edge cases and is not official documentation.

预计阅读时间：11 分钟

PostgreSQL 社区长期不愿引入查询提示：只要统计信息准确、代价参数合理，优化器通常比人工指定计划更可靠。但生产环境里总有例外——数据分布突然变化、参数化查询选错通用计划，或者版本升级后连接顺序发生改变，都可能让一条原本稳定的 SQL 突然变慢。

PostgreSQL 19 计划通过两个 contrib 模块提供这种干预能力。官方术语是 plan advice，业界仍习惯称之为 query hint。它们不是默认开启的核心语法，而是需要显式安装的扩展。这一区别很重要：Advice 应当是可观测、可撤销的运维工具，而不是散落在业务 SQL 里的永久补丁。

两个模块解决不同层次的问题

根据目前公布的设计，两者的职责可以这样理解：

  • pg_plan_advice：为当前查询指定计划建议，例如连接顺序和扫描类型；同时为 EXPLAIN 增加获取计划 advice 的能力。
  • pg_stash_advice：按 query ID 保存 advice。相同查询再次运行时，可以复用已经保存的建议，而不再完全采用优化器的默认选择。

前者适合分析和实验，后者更接近生产环境中的计划固定机制。例如，一条高频参数化查询偶尔选择错误扫描方式时，可以先用 pg_plan_advice 验证干预是否有效，再考虑通过 pg_stash_advice 持久应用。

这也意味着 Advice 并不只是 SQL 注释里的一个提示词。它涉及三个需要分别管理的对象：

  1. 查询及其 query ID；
  2. Advice 字符串；
  3. Advice 生效时所对应的数据分布、索引和 PostgreSQL 版本。

其中任何一项变化，原来的建议都可能失效，甚至从优化手段变成性能负担。

先确认当前构建真正提供了什么

PostgreSQL 19 及相关模块仍可能随具体发行版本调整接口。来源摘要没有给出最终 SQL 语法，因此不应根据其他数据库的 hint 格式猜测。可以先在测试实例中检查模块是否存在，并查看该构建实际暴露的对象。

将 DATABASE_URL 改成测试数据库连接串，然后运行：

export DATABASE_URL='postgresql://postgres:postgres@localhost:5432/postgres'

psql -X "$DATABASE_URL" <<'SQL'
SELECT name,
       default_version,
       installed_version,
       comment
FROM pg_available_extensions
WHERE name IN ('pg_plan_advice', 'pg_stash_advice')
ORDER BY name;
SQL

只有查询结果中出现对应模块后，再安装扩展：

psql -X "$DATABASE_URL" <<'SQL'
CREATE EXTENSION IF NOT EXISTS pg_plan_advice;
CREATE EXTENSION IF NOT EXISTS pg_stash_advice;

SELECT extname, extversion
FROM pg_extension
WHERE extname IN ('pg_plan_advice', 'pg_stash_advice')
ORDER BY extname;
SQL

如果第一段查询没有结果，说明当前服务器包或 PostgreSQL 构建尚未提供这些 contrib 模块；CREATE EXTENSION 并不会自动从网络下载安装它们。

安装后，可用 psql 查看扩展内容，并搜索与 advice 有关的函数：

psql -X "$DATABASE_URL" <<'SQL'
\dx+ pg_plan_advice
\dx+ pg_stash_advice

SELECT n.nspname AS schema_name,
       p.proname AS function_name,
       pg_get_function_identity_arguments(p.oid) AS arguments
FROM pg_proc AS p
JOIN pg_namespace AS n ON n.oid = p.pronamespace
WHERE p.proname ILIKE '%advice%'
ORDER BY 1, 2, 3;
SQL

这组命令是比复制预览版语法更稳妥的起点：它能反映当前安装版本的真实接口。具体设置 advice 以及新增 EXPLAIN 选项的拼写，应以该构建附带的文档和 \dx+ 输出为准。

建立一个可重复的计划实验

在应用 Advice 之前，必须先保存没有干预时的执行计划。下面的脚本会创建一张带倾斜数据的测试表，并比较普通 SQL 与参数化 SQL 的计划。脚本会删除同名测试表，只应在开发数据库运行。

psql -X "$DATABASE_URL" <<'SQL'
DROP TABLE IF EXISTS advice_demo;

CREATE TABLE advice_demo (
    id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    category integer NOT NULL,
    payload  text NOT NULL
);

INSERT INTO advice_demo (category, payload)
SELECT CASE
           WHEN g <= 180000 THEN 1
           ELSE 2 + (g % 999)
       END,
       md5(g::text)
FROM generate_series(1, 200000) AS g;

CREATE INDEX advice_demo_category_idx
    ON advice_demo (category);

ANALYZE advice_demo;

-- 高频值可能匹配大量行。
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT id, payload
FROM advice_demo
WHERE category = 1;

-- 低频值通常更适合索引访问。
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT id, payload
FROM advice_demo
WHERE category = 777;

PREPARE find_by_category(integer) AS
SELECT id, payload
FROM advice_demo
WHERE category = $1;

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
EXECUTE find_by_category(1);

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
EXECUTE find_by_category(777);

DEALLOCATE find_by_category;
SQL

这个实验的重点不是强迫 PostgreSQL 选择索引，而是观察以下差异：

  • 高频值和低频值的估算行数是否接近实际行数；
  • 顺序扫描与索引扫描分别消耗多少缓冲区；
  • 参数化查询是否会在自定义计划与通用计划之间表现出明显差异；
  • Advice 改变的是实际瓶颈，还是只让计划看起来更符合直觉。

拿到基线后，可以这样实践 Advice 测试流程：

  1. 保存原始 EXPLAIN (ANALYZE, BUFFERS, SETTINGS) 输出；
  2. 使用当前 PG19 构建支持的接口生成或设置 advice；
  3. 在相同参数、相同缓存条件下重复执行；
  4. 比较执行时间、实际行数、缓冲区读写和临时文件；
  5. 清除 advice，确认计划和性能能够恢复；
  6. 只有在多组参数下都验证通过，才考虑存入 pg_stash_advice。

不要只比较一次执行时间。缓存预热、并发、检查点和磁盘抖动都可能制造一个看似显著的结果。

哪些场景值得用，哪些场景不值得

Advice 最有价值的场景通常具备两个特征：问题足够具体，而且默认计划的失误可以稳定复现。例如：

  • 版本升级前后，同一查询出现明确的计划回退；
  • 数据严重倾斜，少数参数值总是选错扫描方式；
  • 多表连接的基数估算错误，导致连接顺序明显不合理；
  • 关键业务需要临时稳定计划，为统计信息、索引或 SQL 改造争取时间；
  • 重复执行的参数化查询适合绑定一条经过验证的建议。

以下问题则不应优先用 Advice 掩盖：

  • 表长期没有 ANALYZE；
  • 缺少必要索引或索引定义不合适；
  • 查询返回的数据量本来就很大；
  • SQL 写法造成不必要的全表处理；
  • 代价参数与实际存储环境严重不符；
  • 行数估算错误可以通过提高统计目标或扩展统计信息解决。

换句话说，Advice 能改变优化器的选择，却不能让一个不存在的索引出现，也不能修复错误的数据模型。

上线前把 Advice 当成受控配置

如果决定在生产环境使用 pg_stash_advice，最好像管理配置和数据库变更一样管理它，而不是由某位 DBA 临时设置后永久遗忘。

建议至少建立以下清单：

  • 记录 query ID、SQL 样例、Advice 内容和创建原因；
  • 保存使用 Advice 前后的执行计划与性能数据；
  • 标注适用的 PostgreSQL 版本、索引版本和数据规模；
  • 为每条 Advice 设置负责人和复查日期；
  • 在大批量导入、索引变更和版本升级后重新验证；
  • 准备一条明确的撤销路径；
  • 确认主库、只读副本和故障切换节点均安装了所需扩展；
  • 监控 query ID 或查询归一化变化，避免已保存的 Advice 悄然失去匹配对象。

PostgreSQL 19 的计划 Advice 给了开发者和 DBA 一个过去缺少的选择，但最佳定位不是“让人类接管优化器”。更稳妥的用法是把它当成计划熔断器：当默认计划出现可复现的异常时，以最小范围介入，同时继续修复统计信息、索引或查询本身。这样既能获得短期稳定性，也不会把今天的应急方案变成明天的性能事故。

# AI # Go # PostgreSQL # 后端 # 数据库

相关推荐