如何使用定製執行計畫外掛程式 - ApsaraDB RDS

RDS PostgreSQL提供pg_hint_plan外掛程式，可以通過特殊的備註陳述式提示，使PostgreSQL改變其既定的執行計畫。

前提條件

執行個體大版本為RDS PostgreSQL 10或以上版本。
說明
如果無法建立該外掛程式，請先升級核心小版本。
使用該外掛程式前，需要將pg_hint_plan加入到shared_preload_libraries參數中。
您可以使用RDS PostgreSQL參數設定功能，為shared_preload_libraries參數添加pg_hint_plan。具體操作，請參見設定執行個體參數。
該外掛程式無法在DMS上使用，請使用其他用戶端串連RDS PostgreSQL執行個體使用。

背景資訊

PostgreSQL使用基於代價的最佳化器，最佳化路線使用統計資料而非固定的規則。對於一條SQL語句，最佳化器會評估每一種可能的代價並最終選擇代價最低的去執行，最佳化器會儘力選擇最好的執行計畫，但是由於其並不瞭解資料中可能存在的一些內在串連關係，這些執行計畫可能並不完美。使用pg_hint_plan外掛程式以特殊的注釋形式來提示SQL語句應該如何執行，可以最佳化執行計畫。

注釋提示

pg_hint_plan的注釋以/*+開頭，以*/結束。提示句包括提示名和接下來括弧包裹的參數，以空格作為分界。為了可讀性，每一個提示句都可以重新換行。

樣本

HashJoin作為串連方法，並且使用序列掃描SeqScan來掃描表pgbench_accounts。

 /*+
    HashJoin(a b)
    SeqScan(a)
  */
 EXPLAIN SELECT *
    FROM pgbench_branches b
    JOIN pgbench_accounts 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 pgbench_accounts a  (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)

提示表

雖然可以使用注釋提示的方式對SQL語句進行提示，但是當SQL語句不可編輯時，這種提示方式就很不方便。對於這種情況，可以將提示放在一張特殊的表hint_plan.hints中。這個表包含了下欄欄位。

說明

建立pg_hint_plan外掛程式的使用者預設擁有提示表的許可權，提示表的優先權高於注釋提示。

欄位	描述
id	提示ID號，唯一且自動填滿。
norm_query_string	與要提示的查詢匹配的模式。查詢中的常量必須替換為`?`。空格在模式中很重要。
application_name	應用會話的名稱，置空表示任意應用。
hints	提示句，不需要注釋標記。

樣本命令如下：

INSERT INTO hint_plan.hints(norm_query_string, application_name, hints)
    VALUES (
        'EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?;',
        '',
        'SeqScan(t1)'
    );
INSERT 0 1
postgres=# UPDATE hint_plan.hints
postgres-#    SET hints = 'IndexScan(t1)'
postgres-#  WHERE id = 1;
UPDATE 1
postgres=# DELETE FROM hint_plan.hints
postgres-#  WHERE id = 1;
DELETE 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);
```
串連方法提示
串連方法提示強制指定相關表格彙總在一起的方法。
串連方法提示對普通表、繼承表、無日誌表、暫存資料表、外部表格、系統資料表、表函數、常量值命令和通用運算式有效，對視圖和子查詢無效。
串連順序提示
串連順序提示指定兩個或多個表的串連順序。這裡有兩種強制指定方法：
- 強制執行特定的串連順序，但不限制每個串連層級的方向。
- 強制串連方向。
樣本命令如下：
```
/*+
    NestLoop(t1 t2)
    MergeJoin(t1 t2 t3)
    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的介紹請參見PostgreSQL官方文檔。