pg_hint_plan 介绍

pg_hint_plan 是 PostgreSQL 生态中一块“最后拼图”：当统计信息失效、优化器误判、复杂 SQL 迟迟跑不出理想计划时，它让你用一行注释即可“手动接管”执行路径。以下内容从架构、安装、语法、提示类型、使用场景到踩坑实践，带你系统认识这款“计划干预神器”。

## 一、产生背景：成本优化器的盲区

PostgreSQL 采用基于代价（Cost-Based Optimizer, CBO）的模型，依赖 pg_statistic 中的统计信息估算行数、成本，自动挑选“最便宜”的计划。然而：

1. 数据倾斜或多列相关时，单列直方图会严重失真；
2. 分区表 + 继承 + 并行 + CTE 并存时，估算误差被逐级放大；
3. 测试库数据量小，生产库膨胀后计划翻转，引发性能抖动；
4. 第三方软件发出的 SQL 无法改写，也无法创建新索引。

pg_hint_plan 通过“提示注释”把优化器重新拉回到可控制轨道，而无需改 SQL 逻辑或升级内核。

## 二、加载与安装

1. 源码安装（以 PG 15 为例）
```
git clone https://github.com/ossc-db/pg_hint_plan.git
cd pg_hint_plan
git checkout REL15_1_5_0 # 与主版本保持一致
make && sudo make install
```

2. 包管理器
Ubuntu: `sudo apt install postgresql-15-pg-hint-plan`
RHEL: `sudo yum install pg_hint_plan_15`

3. 预加载
在 `postgresql.conf` 添加：
```
shared_preload_libraries = 'pg_hint_plan' # 如已有扩展，用逗号分隔
```
重启集群后，可在需要的数据库执行：
```
CREATE EXTENSION pg_hint_plan;
```
若只想会话级生效，也可用 `LOAD 'pg_hint_plan';` 动态装载。

## 三、基本写法：一条注释即生效

提示以 `/*+ … */` 形式嵌入 SQL，放在 SELECT/INSERT/UPDATE/DELETE 之后即可。
```
/*+
SeqScan(emp)
HashJoin(dept emp)
Leading((dept emp))
Rows(dept emp #1000)
Set(random_page_cost 1.5)
*/
SELECT d.dname, count(*)
FROM dept d
JOIN emp e ON d.deptno = e.deptno
GROUP BY d.dname;
```
执行计划会强制走 dept→emp 的哈希连接，且把两表连接结果行数手动设为 1000，同时把 random_page_cost 临时调到 1.5。

## 四、提示类别与语法速查

pg_hint_plan 把提示分为六大族，每族可组合使用：

1. 扫描方法
`SeqScan(t)` | `IndexScan(t idx)` | `IndexOnlyScan(t idx)` | `BitmapScan(t idx)` | `TidScan(t)`
对普通表、继承父表、unlogged 表、临时表有效；外部表、VALUES、CTE、子查询、视图内表无效。

2. 连接方法
`NestLoop(t1 t2)` | `HashJoin(t1 t2)` | `MergeJoin(t1 t2)`
可一次性写多组，例如 `NestLoop(a b) MergeJoin(c d)`。

3. 连接顺序
`Leading(t1 t2 t3)` // 强制顺序
`Leading((t1 (t2 t3)))` // 同时强制方向，先 t2⋈t3，再与 t1 连接
括号层级决定 JOIN shape（left-deep/bushy）。

4. 行数修正
`Rows(a b #50)` // 把 a×b 结果行数直接设为 50
`Rows(a b +100)` // 在估算值基础上加 100
`Rows(a b *0.1)` // 估算值乘 0.1
常用于纠正多列相关或谓词复杂时的估算偏差。

5. 并行度
`Parallel(t 4 hard)` // 强制 t 表上并行度=4，并下调代价系数以确保一定生成分支计划
`Parallel(t 2 soft)` // 只调 max_parallel_workers_per_gather，其余留给优化器
支持对普通表、继承表、系统目录生效；视图、外部表、子查询无效。

6. 运行时 GUC
`Set(param value)`
可临时改写本语句规划期参数，如 `Set(random_page_cost 1.0)`、`Set(enable_nestloop off)`、`Set(work_mem '256MB')`，语句级生效，不污染全局。

## 五、hint 表：不改 SQL 也能“锁计划”

若 SQL 来自 ORM 或第三方组件，无法插入注释，可把提示固化到专用表：
```
INSERT INTO hint_plan.hints(norm_query_string, application_name, hints)
VALUES ('SELECT d.dname, count(*) FROM dept d JOIN emp e ON d.deptno = e.deptno GROUP BY d.dname;',
'',
'HashJoin(dept emp) Leading((dept emp)) Rows(dept emp #1000)');
SET pg_hint_plan.enable_hint_table = on; -- 可写入 postgresql.conf 永久生效
```
系统会在规划前用归一化 SQL 匹配该表，自动追加对应 hint，实现“零源码干预”的计划固定，类似 Oracle 的 SQL Plan Baseline。

## 六、调试与监控

打开调试日志可观察 hint 是否生效：
```
SET pg_hint_plan.debug_print = on;
```
日志将输出 used hint、not used hint、error hint 等字段，方便排查拼写错误、别名不匹配或提示冲突。

## 七、典型应用场景

1. 紧急止血
凌晨批量作业因计划翻转跑崩，先通过 `/*+ SeqScan(foo) */` 强制回退到原路线，再从容收集统计信息。

2. 多列相关
列 country='CN' 且 city='Shanghai' 实际仅 0.3% 行，但单列直方图估算 10%，导致走哈希连接 → 广播大表。用 `Rows(cn sh #0.3%)` 纠正即可。

3. 分区裁剪+并行
优化器对分区表低估并行收益，对事实表加 `Parallel(fact 8 hard)`，CPU 利用率从 20% 提升到 80%，ETL 时间减半。

4. 测试/生产一致性
测试库数据少，优化器偏爱 nestloop；生产数据大，可提前在 SQL 固化 `HashJoin(a b)`，避免发布当天性能抖动。

5. 第三方软件
报表平台生成的 SQL 无法改源码，用 hint 表注入 `IndexScan(report_idx)`，解决缺失复合索引回表过慢问题。

## 八、风险与最佳实践

1. 提示是“双刃剑”
数据分布变化后，原强制的计划可能不再最优，需定期复盘。
2. 升级 PostgreSQL 大版本后，成本模型重写，必须重新验证 hint。
3. 尽量写“最小必要”提示：只纠正优化器明显误判的部分，而非全套写死，给未来留下自动优化的空间。
4. 对关键 hint 建立自动化测试：在 CI 中跑 `EXPLAIN (COSTS OFF)` 比对计划文本，防止被意外覆盖。
5. 结合 pg_stat_statements、auto_explain 慢查日志，持续观察提示后 CPU/IO/延迟变化，量化收益。
6. 优先修正统计信息、创建部分索引、调整 GUC 全局参数；hint 是“最后一道保险”，而非首选方案。

## 九、版本演进与社区动态

- 1.3 时代仅支持简单扫描、连接提示；
- 1.4 加入 Leading 括号语法、Rows 修正；
- 1.5 适配 PG 15 并行与 Memoize 节点，支持 `Parallel(t n soft/hard)`；
- 未来 1.6 计划引入 Incremental View Maintenance、自定义扫描接口提示，可干预 FDW 与 GPU 加速计划。

## 十、结语

pg_hint_plan 以极低的侵入成本，为 PostgreSQL 提供了“手术级”计划微调能力：
一条注释，即可让优化器按你的意志走索引、改连接、调并行；一张 hint 表，就能给改不了的 SQL 戴上“计划镣铐”。
在统计信息暂时无法完善、业务火烧眉毛、跨环境必须行为一致的场景下，它是最直接、最可审计、最可回滚的救命工具。
牢记：hint 不是银弹，统计信息、索引设计、参数调优才是根本；把 pg_hint_plan 当成性能工具箱里的“扳手”，而非“锤子”，方能在长期运维中游刃有余。