云数据库 PostgreSQL pg_hint_plan

这里说明 pg_hint_plan 插件的简介及使用方法。

一、概述

pg_hint_plan 是执行计划提示插件，通过在 SQL 语句中添加特殊注释控制优化器的执行计划，可强制指定扫描方式、连接方式、行数估计等，适用于优化器选择不当需要人工干预的场景。

二、支持版本

注意：说明：

注意：你可在控制台实例详情页查看当前实例的内核版本，或执行 SHOW tencentdb_version; 查询。

三、插件简介

pg_hint_plan 通过 SQL 注释形式的提示（hint）干预执行计划的生成，例如 /*+ SeqScan(t) */ 强制顺序扫描、/*+ NestLoop(a b) */ 强制嵌套循环连接。提示只影响当前语句，不改变数据库对象。

四、创建扩展

在目标数据库中执行以下语句创建扩展：

postgres=> CREATE EXTENSION pg_hint_plan;CREATE EXTENSION

注意：说明：

注意：pg_hint_plan 的扩展安装在 hint_plan 模式下，创建时需要当前数据库的 CREATE 权限，请使用具备建库权限的账号在对应数据库执行。

五、扫描方式提示

以下示例创建测试表并查看默认执行计划：

postgres=> CREATE TABLE hint_test(id int PRIMARY KEY, val int);CREATE TABLEpostgres=> INSERT INTO hint_test SELECT i, i FROM generate_series(1, 10000) AS i;INSERT 0 10000postgres=> ANALYZE hint_test;ANALYZEpostgres=> EXPLAIN (COSTS OFF) SELECT * FROM hint_test WHERE id = 5000; QUERY PLAN—————————— Index Scan using hint_test_pkey on hint_test Index Cond: (id = 5000)(2 rows)

默认情况下，等值条件查询走主键索引扫描。

六、强制顺序扫描

使用 SeqScan 提示强制全表顺序扫描：

postgres=> EXPLAIN (COSTS OFF) SELECT /*+ SeqScan(hint_test) */ * FROM hint_test WHERE id = 5000; QUERY PLAN——————— Seq Scan on hint_test Filter: (id = 5000)(2 rows)

注意：说明：

注意：执行计划由默认的索引扫描变为顺序扫描，提示生效。

七、指定索引扫描

使用 IndexScan 提示指定扫描方式与索引：

postgres=> EXPLAIN (COSTS OFF) SELECT /*+ IndexScan(t hint_test_pkey) */ * FROM hint_test t WHERE t.id = 5000; QUERY PLAN———————————————— Index Scan using hint_test_pkey on hint_test t Index Cond: (id = 5000)(2 rows)

八、连接方式提示

以下示例创建第二张表并查看默认连接计划：

postgres=> CREATE TABLE hint_dept(id int PRIMARY KEY, dname text);CREATE TABLEpostgres=> INSERT INTO hint_dept VALUES (1,'dev'), (2,'ops'), (3,'qa');INSERT 0 3postgres=> ANALYZE hint_dept;ANALYZEpostgres=> EXPLAIN (COSTS OFF) SELECT * FROM hint_test t JOIN hint_dept d ON t.val = d.id; QUERY PLAN——————————- Hash Join Hash Cond: (t.val = d.id) -> Seq Scan on hint_test t -> Hash -> Seq Scan on hint_dept d(5 rows)

默认连接方式为 Hash Join。使用 NestLoop 提示强制嵌套循环连接：

postgres=> EXPLAIN (COSTS OFF) SELECT /*+ NestLoop(t d) */ * FROM hint_test t JOIN hint_dept d ON t.val = d.id; QUERY PLAN——————————- Nested Loop Join Filter: (d.id = t.val) -> Seq Scan on hint_test t -> Materialize -> Seq Scan on hint_dept d(5 rows)

注意：说明：

注意：连接方式由 Hash Join 变为 Nested Loop，提示生效。

九、行数估计提示

使用 Rows 提示调整连接结果的行数估计，影响优化器的代价计算：

postgres=> EXPLAIN SELECT /*+ Rows(t d #10) */ * FROM hint_test t JOIN hint_dept d ON t.val = d.id; QUERY PLAN———————————————————————– Hash Join (cost=1.07..172.33 rows=10 width=15) Hash Cond: (t.val = d.id) -> Seq Scan on hint_test t (cost=0.00..145.00 rows=10000 width=8) -> Hash (cost=1.03..1.03 rows=3 width=7) -> Seq Scan on hint_dept d (cost=0.00..1.03 rows=3 width=7)(5 rows)

注意：说明：

注意：Rows(t d #10) 将连接结果行数估计指定为10（# 表示绝对值），实际估计值由默认的3行调整为10行。也可用 +n（加值）、-n（减值）、*n（倍数）方式调整。

十、常用提示速查

十一、Q：使用 Rows 提示时报 Rows hint requires at least two relations？

A：Rows 提示用于调整连接结果的行数估计，需要至少指定两个关系。单表的行数估计暂不支持通过该提示调整。

十二、Q：提示写在什么位置？

A：提示写在 SQL 语句的注释中，格式为 /*+ 提示内容 */，紧跟在 SELECT、UPDATE、DELETE 等关键字之后。提示语法错误时会输出 INFO 级别日志但不影响语句执行。

十三、相关参考

pg_hint_plan 产品文档