PolarDB：pg_hint_plan

背景資訊

PostgreSQL使用基於代價的最佳化器，最佳化路線使用統計資料而非固定的規則。對於一條SQL語句，最佳化器會去評估所有可能的代價並最終選擇代價最低的去執行。最佳化器會儘力選擇最好的執行計畫，但由於其並不瞭解資料中可能存在的一些內在串連關係，導致這些執行計畫可能並不完美。

通過指定GUC變數，可以調整執行計畫，但會影響整個會話。而pg_hint_plan只調整單個執行計畫，不影響整個會話，可以最佳化執行計畫。

注意事項

• DMS暫時不支援提示注釋，請使用其他串連方式串連資料庫。
• pg_hint_plan外掛程式只識別第一個註解區塊中的內容。
• 當進行掃描時，如果遇到了除字母、數字、空格、底線、逗號、圓括弧以外的字元時會立即停止掃描。
• pg_hint_plan外掛程式對於對象的處理與PostgreSQL並不一致，只會按照對象的對象名進行比較。例如，一個名為TBL的對象在提示句中只會匹配TBL，而不會匹配tbl或Tbl。

使用限制

建立外掛程式

1. 建立外掛程式。

   CREATE EXTENSION pg_hint_plan;

2. 載入外掛程式。
   • 單個使用者自動載入。
     • 執行以下命令，載入外掛程式。

       ALTER USER xxx set session_preload_libraries='pg_hint_plan';

       說明 其中，xxx需要更改為實際登入的使用者名稱。
     • 執行以下命令，對單獨資料庫自動載入。

       ALTER DATABASE xxx set session_preload_libraries='pg_hint_plan';

     說明 如果配置錯誤導致無法登入資料庫，則需要以其它使用者/資料庫登入到PolarDB中，進行重設：

     ALTER USER xxx reset session_preload_libraries;
     ALTER DATABASE xxx reset session_preload_libraries;

   • 資料庫叢集自動載入。

     請前往配額中心，在配額名稱PolarDB PG pg_hint_plan使用的操作列，單擊申請，申請添加pg_hint_plan外掛程式。

   • 查看是否已載入外掛程式。
     1. 執行以下命令，將調試資訊輸出到用戶端。

        SET pg_hint_plan.debug_print TO on;
        SET pg_hint_plan.message_level TO notice;

     2. 執行以下命令，查看是否載入成功。

        /*+Set(enable_seqscan 1)*/select 1;

        顯示結果如下，表示載入成功。

        NOTICE:  pg_hint_plan: used hint: Set(enable_seqscan 1)

     3. 執行以下命令，關閉調試資訊輸出。

        RESET pg_hint_plan.debug_print;
        RESET pg_hint_plan.message_level;

使用說明

• 注釋提示

  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中。表結構如下所示：

  欄位	說明
  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
   UPDATE hint_plan.hints
      SET hints = 'IndexScan(t1)'
    WHERE id = 1;
  UPDATE 1
   DELETE FROM hint_plan.hints
    WHERE id = 1;
  DELETE 1

提示類型

• 提示類型

  根據提示短語影響執行計畫的方式，支援的提示類型包括掃描方法提示、串連方法提示、串連順序提示、行數校正提示、並存執行提示和GUC參數設定提示。

  • 掃描方法提示

    掃描方法提示對目標表強制執行特定的掃描方法，pg_hint_plan通過表的別名（如果存在的話）來識別目標表。掃描方法可能是序列掃描、索引掃描等。

    掃描提示對普通表、繼承表、無日誌表、暫存資料表和系統資料表有效。對外部表格、表函數、常量值語句、通用運算式、視圖和子查詢無效。

    樣本命令如下：

    /*+
        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);

    說明 其中

    • NestLoop(t1 t2)：指定表t1和t2的串連方法。
    • MergeJoin(t1 t2 t3)：指定表t1、t2和t3之間的串連方法。
    • Leading(t1 t2 t3)：指定三張表的串連順序。
  • 行數校正提示

    行數校正提示會校正由於查詢最佳化工具限制而導致的行數錯誤。

     /*+ Rows(a b #10) */ SELECT... ; # 設定串連結果的行數為10
     /*+ Rows(a b +10) */ SELECT... ; # 行數增加10
     /*+ Rows(a b -10) */ SELECT... ; # 行數減去10
     /*+ Rows(a b *10) */ SELECT... ; # 行數增大10倍

  • 並存執行提示

    並存執行提示會指定並行的執行計畫。

    並行層級提示對普通的表、繼承表、無日誌表和系統資料表有效。對外部表格、常量從句、通用運算式、視圖和子查詢無效。視圖的內部表可以通過其真實名稱或別名指定目標對象。

    下面兩個樣本說明在每張表上執行查詢的方式不同：

    • 方式一：指定表c1的並行度為3，表c2的並行度為5。

      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)
                                              

    • 方式二：指定表t1的並行度為5。

      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參數進行多次設定，則以最後一個為準。

    樣本命令如下：

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

• 提示格式列表
  所有提示支援的格式如下所示。在注釋中增加以下格式的欄位，可以使用相應的功能。其中[]表示選擇性參數。

  類型	格式	說明
  掃描方法提示（Scan method）	SeqScan(table)	強制對名為table的表使用Sequence Scan。
  	TidScan(table)	強制對名為table的表使用TID Scan。
  	IndexScan(table[ index...])	強制對名為table的表使用Index Scan，如果在後面增加index名稱，則可以指定需要使用的索引。
  	IndexOnlyScan(table[ index...])	強制對名為table的表使用Index Only Scan，如果在後面增加index名稱，則可以指定需要使用的索引。
  	BitmapScan(table[ index...])	強制對名為table的表使用Bitmap Index Scan，如果在後面增加index名稱，則可以指定需要使用的索引。
  	NoSeqScan(table)	禁止對名為table的表使用Seqence Scan。
  	NoTidScan(table)	禁止對名為table的表使用Tid Scan。
  	NoIndexScan(table)	禁止對名為table的表使用Index Scan。
  	NoIndexOnlyScan(table)	禁止對名為table的表使用Index Only Scan。
  	NoBitmapScan(table)	禁止對名為table的表強制使用Bitmap Index Scan。
  串連方法提示（Join method）	NestLoop(table table[ table...])	對包含指定表名的表之間的Join串連操作，強制使用Nest Loop Join進行串連操作。
  	HashJoin(table table[ table...])	對包含指定表名的表之間的Join串連操作，強制使用Hash Join進行串連操作。
  	MergeJoin(table table[ table...])	對包含指定表名的表之間的Join串連操作，強制使用Merge Join進行串連操作。
  	NoNestLoop(table table[ table...])	對包含指定表名的表之間的Join串連操作，禁止使用Nest Loop Join進行串連操作。
  	NoHashJoin(table table[ table...])	對包含指定表名的表之間的Join串連操作，禁止使用Hash Join進行串連操作。
  	NoMergeJoin(table table[ table...])	對包含指定表名的表之間的Join串連操作，禁止使用Merge Join進行串連操作。
  串連順序提示（Join order）	Leading(table table[ table...])	指定表之間進行Join串連的順序。
  	Leading(<join pair>)	指定兩個表之間進行Join的先後順序。
  行數校正提示（Row number correction）	Rows(table table[ table...] correction)	校正由指定表組成的連接結果的行數。 可用的校正方法包括絕對值（＃<n>）、加法（+ <n>）、減法（-<n>）和乘法（* <n>）， 其中<n>表示需要指定的行的數量。
  並存執行提示（Parallel query configuration）	Parallel(table <# of workers> [soft|hard])	強制或禁止並存執行針對指定表的掃描。 說明 <# of workers>是所需的並行度（並存執行的程式數量），其中0表示禁止並存執行。 如果第三個參數是soft（預設），表示僅修改max_parallel_workers_per_gather參數的值，由最佳化器決定實際的並行度。 hard表示強制使用其指定的並行度。
  	PX(<# of workers>)	表示跨機並行查詢時進行並行查詢。 說明 <# of workers>表示並行度。
  	NoPX()	表示針對此查詢強制不使用跨機並行查詢功能。
  GUC參數配置提示	Set(GUC-param value)	最佳化器運行時，將GUC參數設定為該值。

  說明 pg_hint_plan也可以指定跨機並行查詢產生的查詢計劃。目前在跨機並行查詢情境下，不支援行數校正提示，串連方法提示僅能作用於兩表之間的串連，串連順序提示僅能指定全部表之間的先後順序。