AI 助理
备案控制台
官方文档
输入文档关键字查找
首页 云数据库 RDS RDS PostgreSQL数据库 插件 定制执行计划（pg_hint_plan）

定制执行计划（pg_hint_plan）

更新时间：
产品详情
我的收藏

RDS PostgreSQL提供pg_hint_plan插件，可以通过特殊的注释语句提示，使PostgreSQL改变其既定的执行计划。

您可以加入RDS PostgreSQL插件交流钉钉群（103525002795），进行咨询、交流和反馈，获取更多关于插件的信息。

前提条件

  • 实例大版本为RDS PostgreSQL 10或以上版本。

    说明

    如果无法创建该插件，请先升级内核小版本。例如，对于RDS PostgreSQL 17版本的实例，其内核的小版本应为20241030或以上。

  • 已创建RDS PostgreSQL高权限账号，如何创建高权限账号请参见创建账号。

背景信息

PostgreSQL使用基于代价的优化器，优化路线使用统计数据而非固定的规则。对于一条SQL语句，优化器会评估每一种可能的代价并最终选择代价最低的去执行，优化器会尽力选择最好的执行计划，但是由于其并不了解数据中可能存在的一些内在连接关系，这些执行计划可能并不完美。使用pg_hint_plan插件以特殊的注释形式来提示SQL语句应该如何执行，可以优化执行计划。

安装和卸载插件

通过控制台管理插件

  • 安装插件

    1. 访问RDS实例列表，在上方选择地域，然后单击目标实例ID。

    2. 在左侧导航栏单击插件管理。

    3. 在管理插件页面的未安装插件页签，搜索pg_hint_plan插件，并单击操作列的安装。

      image

    4. 在弹出的窗口中选择目标数据库和高权限账号后，单击安装，将插件安装至目标数据库。

      当实例的状态由维护实例中变为运行中时，表示插件已成功安装。

  • 更新和卸载插件

    • 在管理插件页面的在已安装插件页签，单击目标插件操作列的升级版本，将插件升级到最新版本。

      说明

      如果操作列没有升级版本按钮，表示该插件的版本已是最新。

    • 在管理插件页面的已安装插件页签，单击目标插件操作列的卸载，卸载目标插件。

通过SQL命令管理插件

  1. 设置实例参数，在shared_preload_libraries的运行参数值中添加pg_hint_plan。例如，将运行参数值改为'pg_stat_statements,auto_explain,pg_hint_plan'。

  2. 使用高权限账号连接需要安装插件的数据库，执行以下SQL管理插件。

    • 安装插件

      CREATE EXTENSION pg_hint_plan;

    • 卸载插件

      DROP EXTENSION pg_hint_plan;

注释提示

pg_hint_plan的注释提示以/*+开头，以*/结束。

  • 提示语句由提示名及其括号内的参数构成，参数之间以空格分隔。为提升可读性，每个提示语句均可另起一行。

    示例

    HashJoin作为连接方法，并且使用序列扫描SeqScan来扫描表test_table01。

     /*+
    HashJoin(a b)
    SeqScan(a)
  */
 EXPLAIN SELECT *
    FROM test_table02 b
    JOIN test_table01 a ON b.bid = a.bid
   ORDER BY a.aid;

    返回如下结果：

                                          QUERY PLAN
---------------------------------------------------------------------------------------
 Sort  (cost=31465.84..31715.84 rows=100000 width=197)
   Sort Key: a.aid
   ->  Hash Join  (cost=1.02..4016.02 rows=100000 width=197)
         Hash Cond: (a.bid = b.bid)
         ->  Seq Scan on test_table01 a  (cost=0.00..2640.00 rows=100000 width=97)
         ->  Hash  (cost=1.01..1.01 rows=1 width=100)
               ->  Seq Scan on test_table02 b  (cost=0.00..1.01 rows=1 width=100)
(7 rows)

  • 在SQL查询中，如果对表名使用了别名，则在pg_hint_plan的提示语句中也应使用该别名。

    示例

    • 提示语句中使用了别名：

      /*+ IndexScan(t) */ 
EXPLAIN (COSTS OFF) 
SELECT * 
FROM tbl t 
WHERE a = 1;

      返回结果：

                   QUERY PLAN              
-------------------------------------
 Index Scan using tbl_a_idx on tbl t
   Index Cond: (a = 1)

    • 提示语句中未使用别名：

      /*+ IndexScan(tbl) */ 
EXPLAIN (COSTS OFF) 
SELECT * 
FROM tbl t 
WHERE a = 1;

      返回结果：

          QUERY PLAN     
-------------------
 Seq Scan on tbl t
   Filter: (a = 1)

提示表

虽然可以使用注释提示的方式对SQL语句进行提示，但是当SQL语句不可编辑时，这种提示方式就很不方便。对于这种情况，可以将提示放在一张特殊的表hint_plan.hints中。这个表包含了下列字段。

说明

创建pg_hint_plan插件的用户默认拥有提示表的权限，提示表的优先权高于注释提示。

字段

描述

id

提示ID号，唯一且自动填充。

norm_query_string

与要提示的查询匹配的模式。查询中的常量必须替换为?。空格在模式中很重要。

application_name

应用会话的名称，置空表示任意应用。

hints

提示语句，不需要注释标记。

开启提示表

SET pg_hint_plan.enable_hint_table = on;

向提示表中插入数据

INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
VALUES (
    'EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?;',
    '',
    'SeqScan(t1)'
);

更新提示表中数据

UPDATE hint_plan.hints
SET hints = 'IndexScan(t1)'
WHERE id = 1;

删除提示表中的数据

DELETE FROM hint_plan.hints
WHERE id = 1;

提示类型

根据提示短语影响执行计划的方式，可以分为如下六类：

  • 扫描方法提示

    扫描方法提示对目标表强制执行特定的扫描方法。pg_hint_plan通过表格的别名（如果存在的话）来识别目标表。扫描方法是SeqScan、IndexScan、NoSeqScan等。

    扫描提示对普通表、继承表、无日志表、临时表和系统表有效，对外部表、表函数、常量值语句、通用表达式、视图和子查询无效。

    示例

    /*+
    SeqScan(t1)
    IndexScan(t2 t2_pkey)
 */
SELECT * FROM table1 t1 JOIN table table2 t2 ON (t1.key = t2.key);

  • 连接方法提示

    连接方法提示强制指定相关表格聚合在一起的方法。

    连接方法提示对普通表 、继承表、无日志表、临时表、外部表、系统表、表函数、常量值命令和通用表达式有效，对视图和子查询无效。

  • 连接顺序提示

    连接顺序提示指定两个或多个表的连接顺序。这里有两种强制指定方法：

    • 强制执行特定的连接顺序，但不限制每个连接级别的方向。

    • 强制连接方向。

    示例

    -- 指定连接的顺序，不限制每个连接的方向
/*+
    Leading(t1 t2 t3)
 */
SELECT * FROM table1 t1
    JOIN table table2 t2 ON (t1.key = t2.key)
    JOIN table table3 t3 ON (t2.key = t3.key);

-- 指定连接的顺序和方向
/*+
    Leading((t1 t2) t3)
 */
SELECT * FROM table1 t1
    JOIN table table2 t2 ON (t1.key = t2.key)
    JOIN table table3 t3 ON (t2.key = t3.key);

  • 行号纠正提示

    行号纠正提示会纠正由于计划器限制而导致的行号错误。

    示例

    /*+ Rows(a b #10) */ SELECT... ;     //设置连接结果的行号为10。
/*+ Rows(a b +10) */ SELECT... ;     //行号增加10。
/*+ Rows(a b -10) */ SELECT... ;     //行号减去10。
/*+ Rows(a b *10) */ SELECT... ;     //将行号扩大至原来的10倍。

  • 并行执行提示

    并行提示会指定并行的执行计划。

    并行执行提示对普通表、继承表、无日志表和系统表有效，对外部表、常量从句、通用表达式、视图和子查询无效。视图的内部表可以通过其真实名称或别名指定目标对象。

    下面两个示例说明在每个表上执行查询的方式不同：

    示例

    • 方式一

      explain /*+ Parallel(c1 3 hard) Parallel(c2 5 hard) */
       SELECT c2.a FROM c1 JOIN c2 ON (c1.a = c2.a);

      返回如下结果：

                                        QUERY PLAN                                   
-------------------------------------------------------------------------------
 Hash Join  (cost=2.86..11406.38 rows=101 width=4)
   Hash Cond: (c1.a = c2.a)
   ->  Gather  (cost=0.00..7652.13 rows=1000101 width=4)
         Workers Planned: 3
         ->  Parallel Seq Scan on c1  (cost=0.00..7652.13 rows=322613 width=4)
   ->  Hash  (cost=1.59..1.59 rows=101 width=4)
         ->  Gather  (cost=0.00..1.59 rows=101 width=4)
               Workers Planned: 5
               ->  Parallel Seq Scan on c2  (cost=0.00..1.59 rows=59 width=4)

    • 方式二

      EXPLAIN /*+ Parallel(tl 5 hard) */ SELECT sum(a) FROM tl;

      返回如下结果：

                                          QUERY PLAN                                  
-----------------------------------------------------------------------------------
 Finalize Aggregate  (cost=693.02..693.03 rows=1 width=8)
   ->  Gather  (cost=693.00..693.01 rows=5 width=8)
         Workers Planned: 5
         ->  Partial Aggregate  (cost=693.00..693.01 rows=1 width=8)
               ->  Parallel Seq Scan on tl  (cost=0.00..643.00 rows=20000 width=4)

  • 设置临时GUC参数

    在计划的时候临时改变GUC参数。执行计划中的GUC参数会有预期的效果，除非提示语句与其他计划冲突。同样的GUC参数设置以最后一次为准。

    示例

    /*+ Set(random_page_cost 2.0) */
SELECT * FROM table1 t1 WHERE key = 'value';

提示支持的格式

类别

格式

说明

扫描方法提示

SeqScan(table)

强制序列扫描。

TidScan(table)

强制TID扫描。

IndexScan(table[ index...])

强制索引扫描，可以指定某个索引。

IndexOnlyScan(table[ index...])

强制仅使用索引扫描，可以指定某个索引。

BitmapScan(table[ index...])

强制使用Bitmap扫描。

NoSeqScan(table)

强制不使用序列扫描。

NoTidScan(table)

强制不使用TID扫描。

NoIndexScan(table)

强制不使用索引扫描。

NoIndexOnlyScan(table)

强制不使用索引扫描，仅扫描表。

NoBitmapScan(table)

强制不使用Bitmap扫描。

连接方法提示

NestLoop(table table[ table...])

强制使用嵌套循环连接。

HashJoin(table table[ table...])

强制使用散列连接。

MergeJoin(table table[ table...])

强势使用合并连接。

NoNestLoop(table table[ table...])

强制不使用嵌套循环连接。

NoHashJoin(table table[ table...])

强制不使用散列连接。

NoMergeJoin(table table[ table...])

强制不使用合并连接。

连接顺序提示

Leading(table table[ table...])

强制连接的顺序。

Leading(<join pair>)

强制连接的顺序和方向。

行号纠正提示

Rows(table table[ table...] correction)

纠正由指定表组成的联接结果的行号。可用的校正方法为绝对值#<n>、加法+ <n>、减法-<n>和乘法* <n>。<n>是strtod函数可以读取的数字。

并行执行提示

Parallel(table <# of workers> [soft|hard])

强制或禁止并行执行指定表。 <worker#>是所需的并行工作程序数量，0表示禁止并行执行。

第三个参数如果是soft（默认），表示仅更改max_parallel_workers_per_gather并将其他所有内容留给计划器选择； 如果是hard，表示所有相关参数都会被强制指定。

设置临时GUC参数

Set(GUC-param value)

规划器运行时，将GUC参数设置为该值。

更多pg_hint_plan的介绍请参见pg_hint_plan。

上一篇：字符串相似性判断（fuzzystrmatch）下一篇：清理表空间（pg_repack）