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

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

前提條件

執行個體大版本為RDS PostgreSQL 10或以上版本。
說明
如果無法建立該外掛程式，請先升級核心小版本。例如，對於RDS PostgreSQL 17版本的執行個體，其核心的小版本應為20241030或以上。
已建立RDS PostgreSQL高許可權帳號，如何建立高許可權帳號請參見建立帳號。

背景資訊

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

安裝和卸載外掛程式

通過控制台管理外掛程式

安裝外掛程式
1. 訪問RDS執行個體列表，在上方選擇地區，然後單擊目標執行個體ID。
2. 在左側導覽列單擊外掛程式管理。
3. 在管理外掛程式頁面的未安裝外掛程式頁簽，搜尋pg_hint_plan外掛程式，並單擊操作列的安裝。
4. 在彈出的視窗中選擇目標資料庫和高許可權帳號後，單擊安裝，將外掛程式安裝至目標資料庫。
  當執行個體的狀態由維護執行個體中變為運行中時，表示外掛程式已成功安裝。
更新和卸載外掛程式
- 在管理外掛程式頁面的在已安裝外掛程式頁簽，單擊目標外掛程式操作列的升級版本，將外掛程式升級到最新版本。
  說明
  如果操作列沒有升級版本按鈕，表示該外掛程式的版本已是最新。
- 在管理外掛程式頁面的已安裝外掛程式頁簽，單擊目標外掛程式操作列的卸載，卸載目標外掛程式。

通過SQL命令管理外掛程式

設定執行個體參數，在shared_preload_libraries的運行參數值中添加pg_hint_plan。例如，將運行參數值改為'pg_stat_statements,auto_explain,pg_hint_plan'。
使用高許可權帳號串連需要安裝外掛程式的資料庫，執行以下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。