PolarDB:pg_hint_plan

背景情報

PostgreSQL は、固定ルールではなくデータ統計に依存するコストベースのオプティマイザーを使用します。各 SQL 文に対して、オプティマイザーはすべての可能な実行計画を評価し、最もコストの低いものを選択します。オプティマイザーは最良の計画を選択しようとしますが、オプティマイザーは基礎となるデータ関係を認識していないため、その計画は完全ではない場合があります。

Grand Unified Configuration (GUC) 変数を指定して実行計画を調整できますが、この変更はセッション全体に影響します。pg_hint_plan 拡張機能を使用すると、セッション全体に影響を与えることなく、単一の実行計画を最適化できます。

注意事項

• Data Management Service (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 Use] クォータを見つけ、[操作] 列の [適用] をクリックして 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(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)
                        

ヒントタイプ

• ヒントのタイプ

  サポートされているヒントのタイプは、実行計画への影響によって分類され、スキャンメソッドのヒント、結合メソッドのヒント、結合順序のヒント、行数補正のヒント、並列実行のヒント、および GUC パラメーター設定のヒントが含まれます。

  • スキャンメソッドのヒント

    スキャンメソッドのヒントは、ターゲットテーブルに特定のスキャンメソッドを強制します。pg_hint_plan 拡張機能は、エイリアスが存在する場合、そのエイリアスによってターゲットテーブルを識別します。スキャンメソッドには、シーケンシャルスキャンやインデックススキャンなどのオプションが含まれます。

    スキャンヒントは、標準テーブル、継承テーブル、ログなしテーブル、一時テーブル、およびシステムテーブルに有効です。外部テーブル、テーブル関数、定数値文、共通テーブル式、ビュー、またはサブクエリには有効ではありません。

    コマンド例:

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

  • 結合メソッドのヒント

    結合メソッドのヒントは、関連するテーブルを結合するための特定のメソッドを強制します。これらは、標準テーブル、継承テーブル、ログなしテーブル、一時テーブル、外部テーブル、システムテーブル、テーブル関数、定数値文、および共通テーブル式に有効です。ビューやサブクエリには有効ではありません。

  • 結合順序のヒント

    結合順序のヒントは、2 つ以上のテーブルの結合順序を指定します。順序を強制するには 2 つの方法があります:

    • 各結合レベルで方向を制限せずに、特定の結合順序を強制します。

    • 結合方向を強制します。

    コマンド例:

     /*+
        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): 3 つのテーブルの結合順序を指定します。

  • 行数補正

    行数補正ヒントは、クエリオプティマイザーの制限によって引き起こされる行数エラーを修正します。

     /*+ Rows(a b #10) */ SELECT... ; # 結合結果の行数を 10 に設定します。
     /*+ Rows(a b +10) */ SELECT... ; # 行数を 10 増やします。
     /*+ Rows(a b -10) */ SELECT... ; # 行数を 10 減らします。
     /*+ Rows(a b *10) */ SELECT... ; # 行数を 10 倍します。

  • 並列実行のヒント

    並列実行のヒントは、並列実行計画を指定します。

    並列度のヒントは、標準テーブル、継承テーブル、ログなしテーブル、およびシステムテーブルに有効です。外部テーブル、定数句、共通テーブル式、ビュー、またはサブクエリには有効ではありません。ビューの内部テーブルは、実際の名前またはエイリアスによってターゲットオブジェクトとして指定できます。

    次の 2 つの例は、クエリが各テーブルで異なる方法で実行されることを示しています:

    • 方法 1: テーブル 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)
                                              

    • 方法 2: テーブル 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';

• ヒントのフォーマット

  次の表に、すべてのヒントでサポートされているフォーマットを示します。ヒントを使用するには、指定されたフォーマットでコメントに追加します。角括弧 [] はオプションのパラメーターを示します。

  タイプ	フォーマット	説明
  スキャンメソッド	SeqScan(table)	'table' という名前のテーブルでシーケンシャルスキャンを強制します。
  	TidScan(table)	'table' という名前のテーブルで TID スキャンを強制します。
  	IndexScan(table[ index...])	'table' という名前のテーブルでインデックススキャンを強制します。インデックス名を追加することで、使用するインデックスを指定できます。
  	IndexOnlyScan(table[ index...])	'table' という名前のテーブルでインデックスオンリースキャンを強制します。インデックス名を追加することで、使用するインデックスを指定できます。
  	BitmapScan(table[ index...])	'table' という名前のテーブルでビットマップインデックススキャンを強制します。インデックス名を追加することで、使用するインデックスを指定できます。
  	NoSeqScan(table)	'table' という名前のテーブルでシーケンシャルスキャンを無効にします。
  	NoTidScan(table)	'table' という名前のテーブルで TID スキャンを無効にします。
  	NoIndexScan(table)	'table' という名前のテーブルでインデックススキャンを無効にします。
  	NoIndexOnlyScan(table)	'table' という名前のテーブルでインデックスオンリースキャンを無効にします。
  	NoBitmapScan(table)	'table' という名前のテーブルでビットマップインデックススキャンを無効にします。
  結合メソッド	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>)	2 つのテーブル間の結合順序を指定します。
  行数補正	Rows(table table[ table...] correction)	指定されたテーブルの結合結果の行数を修正します。利用可能な修正方法には、絶対値 (#<n>)、加算 (+<n>)、減算 (-<n>)、および乗算 (*<n>) があり、ここで <n> は行数です。
  パラレルクエリ設定	Parallel(table <# of workers> [soft|hard])	指定されたテーブルに対して並列スキャンを強制または無効にします。 説明 <# of workers> は、目的の並列度 (並列ワーカープロセスの数) です。値 0 は並列実行を無効にします。 3 番目のパラメーターが soft (デフォルト) の場合、max_parallel_workers_per_gather パラメーターの値のみが変更されます。オプティマイザーが実際の並列度を決定します。 hard は指定された並列度を強制します。
  	PX(<# of workers>)	クロスノード並列実行中にパラレルクエリが実行されることを示します。 説明 <# of workers> は並列度を指定します。
  	NoPX()	クエリがクロスノード並列実行機能を使用しないように強制します。
  GUC パラメーター設定	Set(GUC-param value)	オプティマイザーの実行時に GUC パラメーターを指定された値に設定します。

  説明

  pg_hint_plan は、クロスノード並列実行によって生成されるクエリ計画も指定できます。クロスノード並列実行シナリオでは、行数補正ヒントはサポートされていません。結合メソッドのヒントは、2 つのテーブル間の結合にのみ適用できます。結合順序のヒントは、関連するすべてのテーブルの順序のみを指定できます。