pg_hint_plan

pg_hint_plan 插件描述

pg_hint_plan 使得在 SQL 注释中使用所谓的“提示”来调整 AntDB 执行计划成为可能，比如/*+ SeqScan(a) */。

使用准备

• 修改配置文件后重启数据库

# 修改postgresql.conf文件，存在备机的情况下，备机也需要修改
shared_preload_libraries = 'pg_hint_plan'

• 登陆数据库，执行 SQL 命令，创建扩展

CREATE EXTENSION pg_hint_plan;
LOAD 'pg_hint_plan';

使用方法

pg_hint_plan 读取与目标 SQL 语句一起给出的特殊形式的注释中的提示短语。特殊形式以字符序列“/*+”开头，以“*/”结尾。提示短语由提示名称和后面的参数组成，这些参数用括号括起来，用空格分隔。为了便于阅读，每个提示短语都可以用新行分隔。

antdb=# /*+
antdb*#    <b>HashJoin(a b)</b>
antdb*#    <b>SeqScan(a)</b>
antdb*#  */
antdb-# EXPLAIN SELECT *
antdb-#    FROM pgbench_branches b
antdb-#    JOIN pgbench_accounts a ON b.bid = a.bid
antdb-#   ORDER BY a.aid;
                                        QUERY PLAN
---------------------------------------------------------------------------------------
    Sort  (cost=31465.84..31715.84 rows=100000 width=197)
    Sort Key: a.aid
    ->  <b>Hash Join</b>  (cost=1.02..4016.02 rows=100000 width=197)
            Hash Cond: (a.bid = b.bid)
            ->  <b>Seq Scan on pgbench_accounts a</b>  (cost=0.00..2640.00 rows=100000 width=97)
            ->  Hash  (cost=1.01..1.01 rows=1 width=100)
                ->  Seq Scan on pgbench_branches b  (cost=0.00..1.01 rows=1 width=100)
(7 rows)

antdb=#

上述需要编辑注释，在无法编辑的情况下，这是不方便的。在这种情况下，提示可以放在一个名为 “hint_plan.intsts” 的特殊表中。表格描述如下：

antdb=# \d+ hint_plan.hints
                                                         Table "hint_plan.hints"
      Column       |  Type   | Collation | Nullable |                   Default                   | Storage  | Stats target | Description
-------------------+---------+-----------+----------+---------------------------------------------+----------+--------------+-------------
 id                | integer |           | not null | nextval('hint_plan.hints_id_seq'::regclass) | plain    |              |
 norm_query_string | text    |           | not null |                                             | extended |              |
 application_name  | text    |           | not null |                                             | extended |              |
 hints             | text    |           | not null |                                             | extended |              |
Indexes:
    "hints_pkey" PRIMARY KEY, btree (id)
    "hints_norm_and_app" UNIQUE, btree (id, norm_query_string, application_name)
Access method: heap

其中：
id：用于标识提示行的唯一编号。此列按顺序自动填充。
norm_query_string：与hint的查询匹配的模式
application_name：要应用hint的会话的application_name值。
hints：hints短语，这必须是一系列不包括周围注释标记的提示。

例如：
antdb=# INSERT INTO hint_plan.hints(norm_query_string, application_name, hints)
antdb-#     VALUES (
antdb(#         'EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?;',
antdb(#         '',
antdb(#         'SeqScan(t1)'
antdb(#     );
INSERT 0 1
antdb=# UPDATE hint_plan.hints
antdb-#    SET hints = 'IndexScan(t1)'
antdb-#  WHERE id = 1;
UPDATE 1
antdb=# DELETE FROM hint_plan.hints
antdb-#  WHERE id = 1;
DELETE 1
antdb=#

使用示例

antdb=# CREATE EXTENSION pg_hint_plan;
CREATE EXTENSION
antdb=# LOAD 'pg_hint_plan';
LOAD
antdb=# CREATE TABLE a(id int primary key, info text, crt_time timestamp);
CREATE TABLE
antdb=# CREATE TABLE b(id int primary key, info text, crt_time timestamp);
CREATE TABLE
antdb=# INSERT INTO a SELECT generate_series (1,100000), 'a_'||md5 (random() ::text), now();
INSERT 0 100000
antdb=# INSERT INTO b SELECT generate_series (1,100000), 'b_'||md5 (random():: text), now();
INSERT 0 100000
antdb=# ANALYZE a;
ANALYZE
antdb=# ANALYZE b;
ANALYZE

--不使用 pg_hint_plan
antdb=# EXPLAIN SELECT a.*,b.* FROM a,b WHERE a.id=b.id AND a.id<10;
                              QUERY PLAN
-----------------------------------------------------------------------
 Nested Loop  (cost=0.58..83.24 rows=9 width=94)
   ->  Index Scan using a_pkey on a  (cost=0.29..8.45 rows=9 width=47)
         Index Cond: (id < 10)
   ->  Index Scan using b_pkey on b  (cost=0.29..8.31 rows=1 width=47)
         Index Cond: (id = a.id)
(5 rows)

--使用 pg_hint_plan
antdb=# /*+
antdb*# HashJoin(a b)
antdb*# SeqScan(b)
antdb*# */ EXPLAIN(verbose, analyse, costs, buffers) SELECT a.*, b.* FROM a, b WHERE a.id=b. id AND a. id<10;
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=8.56..2206.07 rows=9 width=94) (actual time=0.049..16.513 rows=9 loops=1)
   Output: a.id, a.info, a.crt_time, b.id, b.info, b.crt_time
   Inner Unique: true
   Hash Cond: (b.id = a.id)
   Buffers: shared hit=941
   ->  Seq Scan on public.b  (cost=0.00..1935.00 rows=100000 width=47) (actual time=0.006..5.277 rows=100000 loops=1)
         Output: b.id, b.info, b.crt_time
         Buffers: shared hit=935
   ->  Hash  (cost=8.45..8.45 rows=9 width=47) (actual time=0.012..0.012 rows=9 loops=1)
         Output: a.id, a.info, a.crt_time
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         Buffers: shared hit=3
         ->  Index Scan using a_pkey on public.a  (cost=0.29..8.45 rows=9 width=47) (actual time=0.004..0.008 rows=9 loops=1)
               Output: a.id, a.info, a.crt_time
               Index Cond: (a.id < 10)
               Buffers: shared hit=3
 Planning:
   Buffers: shared hit=3
 Planning Time: 0.248 ms
 Execution Time: 16.549 ms
(20 rows)

pg_hint_plan 的 GUC 参数