PolarDB:pg_hint_plan

背景情報

PolarDB for PostgreSQL (Compatible with Oracle) は、静的ルールではなくデータ統計を利用するコストベースのオプティマイザを使用します。 オプティマイザは、SQL文のすべての可能な実行計画のコストを評価し、最も低いコストで実行計画を実行します。 オプティマイザは最善の努力をしますが、オプティマイザはデータ間の基礎となる関係を考慮しないため、選択された実行計画は最善の計画ではない可能性があります。

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

注意事項

• データ管理 (DMS) はヒントをサポートしていません。 データベースにアクセスするには、他の方法を使用する必要があります。

• pg_hint_plan拡張機能は、最初のコメントブロックからのみヒントを読み取ります。

• pg_hint_plan拡張機能は、文字、数字、スペース、および特定の特殊文字のみを受け入れ、他の文字を識別するとすぐに解析を停止します。 受け入れられた特殊文字には、_、()

• pg_hint_plan拡張機能は、オブジェクト名を大文字と小文字を区別して比較します。これは、PostgreSQLがオブジェクト名を比較する方法とは異なります。 たとえば、ヒント内のTBLという名前のオブジェクトは、データベース内のTBLのみに一致します。 tblまたはTblという名前のオブジェクトは無視されます。

制限事項

pg_hint_plan拡張機能の作成と読み込み

1. エクステンションを作成します。

   CREATE EXTENSION pg_hint_plan;

2. エクステンションをロードします。

   • 1人のユーザーの拡張機能を自動的にロードします。

     • 次のステートメントを実行して、拡張機能をロードします。

       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;

   • データベースクラスターの拡張機能を自動的に読み込みます。

     pg_hint_plan拡張機能を使用するには、Quota Centerに移動します。 polardb_pg_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;

使用上の注意

• 基本的な使い方

  ヒントは、スラッシュ、アスタリスク、プラス記号 (/* +) の組み合わせで始まり、アスタリスクとスラッシュ (*/) の組み合わせで終わります。 ヒントは、ヒント名とパラメータで構成されます。 パラメータは括弧 () で囲まれ、スペースで区切られています。 読みやすくするために、各ヒントを新しい行で開始できます。

  例：

  この例では、結合方法としてHashJoinが使用され、pgbench_accountsテーブルはSeqScanメソッドを使用してスキャンされます。

  /*+
     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文が編集可能な場合にのみ便利です。 SQL文を編集できない場合は、hint_plan.hintsという名前のテーブルにヒントを配置できます。 テーブルは次の列で構成されています。

  列	説明
  id	ヒントのID。 IDは一意であり、自動的に生成されます。
  norm_query_string	ヒントを追加するSQL文と一致するパターン。 SQL文の定数は、ワイルドカードとして機能する疑問符 (?) で置き換える必要があります。 スペース文字はパターンの必要な部分です。
  application_name	ヒントが適用されるアプリケーションの名前。 このパラメーターが空の場合、ヒントはすべてのアプリケーションに適用されます。
  ヒント	ヒントを含むコメント。 コメントマークを含める必要はありません。

  ヒントテーブルの例を次に示します。 デフォルトでは、pg_hint_plan拡張機能を作成するユーザーには、ヒントテーブルに対する権限があります。 ステートメントコメントとヒントテーブルの両方にヒントが追加されると、ヒントテーブルのヒントがコメントのヒントよりも優先されます。

  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

ヒントタイプ

• ヒントタイプ

  ヒントは、実行計画への影響に基づいて、次の6つのタイプに分類されます。

  • Hints for scan methods

    このタイプのヒントは、指定されたテーブルをスキャンするために使用されるメソッドを指定します。 指定されたテーブルにエイリアスがある場合、pg_hint_plan拡張機能はエイリアスに基づいてテーブルを識別します。 サポートされているスキャン方法には、SeqScan、IndexScanなどがあります。

    スキャン方法のヒントは、通常テーブル、継承テーブル、未ログテーブル、一時テーブル、およびシステムテーブルで有効です。 ただし、スキャンメソッドのヒントは、外部テーブル、テーブル関数、定数の値が指定されているステートメント、普遍的な式、ビューでは効果的ではありません。 とサブクエリ。

    例：

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

  • 結合メソッドのヒント

    このタイプのヒントは、指定されたテーブルを結合するために使用されるメソッドを指定します。 結合メソッドのヒントは、通常テーブル、継承テーブル、未ログテーブル、一時テーブル、外部テーブル、システムテーブル、テーブル関数、定数の値が指定されているステートメント、および普遍式で有効です。 結合メソッドのヒントは、ビューやサブクエリでは効果的ではありません。

  • 参加注文のヒント

    このタイプのヒントは、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テーブルを結合するための方法を指定する。

    • 先行 (t1 t2 t3): 3つのテーブルが結合される順序を指定する。

  • 行番号修正のヒント

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

    /*+ Rows(a b #10) */ SELECT... ; # Sets the row number of join results to 10.
     /*+ Rows(a b +10) */ SELECT... ; # Increases the row number by 10.
     /*+ Rows(a b -10) */ SELECT... ; # Decreases the row number by 10.
     /*+ Rows(a b *10) */ SELECT... ; # Increases the row number by 10 times.

  • 並列実行のヒント

    このタイプのヒントは、SQL文を並列に実行するために使用されるプランを指定します。

    並列実行のヒントは、通常テーブル、継承テーブル、未ログテーブル、およびシステムテーブルで有効です。 ただし、並列実行のヒントは、外部テーブル、定数の値が指定されている句、普遍的な式、ビュー、およびサブクエリには影響しません。 ビューの内部テーブルは、その実名またはエイリアスで指定できます。

    次の例は、各テーブルでSQL文を異なる方法で実行する方法を示しています。

    • 例1: c1テーブルの並列度 (DOP) を3に設定し、c2テーブルのDOPを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テーブルのDOPを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パラメーターの値は、エグゼキュータが実行プランを生成した場合にのみ有効です。 この値は、セッション全体に影響を与えることなく、クエリのパフォーマンスを向上させます。 GUCパラメーターに複数のヒントを設定すると、最新のヒントが有効になります。

    例：

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

• ヒント構文の一覧

  次の表に、サポートされているすべてのヒント構文を示します。 ヒントをクエリのコメントとしてサーバー固有の目的に追加できます。 オプションのパラメーターは、構文で括弧 ([ ]) のペアで囲まれています。

  データ型	ヒント構文	説明
  スキャン方法のヒント	SeqScan (テーブル)	シーケンススキャンを指定します。
  	TidScan (テーブル)	TIDスキャンを指定します。
  	IndexScan (テーブル [インデックス...])	インデックススキャンを指定します。 インデックスを指定できます。
  	IndexOnlyScan (テーブル [インデックス...])	インデックスのみのスキャンを指定します。 インデックスを指定できます。
  	BitmapScan (テーブル [インデックス...])	ビットマップスキャンを指定します。 インデックスを指定できます。
  	NoSeqScan (テーブル)	シーケンススキャンを禁止します。
  	NoTidScan (テーブル)	TIDスキャンを禁止します。
  	NoIndexScan (テーブル)	インデックススキャンを禁止します。
  	NoIndexOnlyScan (テーブル)	インデックススキャンを禁止します。 テーブルのみがスキャンされます。
  	NoBitmapScan (テーブル)	ビットマップスキャンを禁止します。
  結合メソッドのヒント	NestLoop (テーブルテーブル [テーブル...])	ネストされたループ結合を指定します。
  	HashJoin (テーブルテーブル [テーブル...])	ハッシュ結合を指定します。
  	MergeJoin (テーブルテーブル [テーブル...])	マージ結合を指定します。
  	NoNestLoop (テーブルテーブル [テーブル...])	ネストされたループ結合を禁止します。
  	NoHashJoin (テーブルテーブル [テーブル...])	ハッシュ結合を禁止します。
  	NoMergeJoin (テーブルテーブル [テーブル...])	マージ結合を禁止します。
  参加注文のヒント	リーディング (テーブルテーブル [テーブル...])	結合順序を指定します。
  	リーディング (<参加ペア>)	結合の順序と方向を指定します。
  行番号修正のヒント	行 (テーブルテーブル [テーブル...] 修正)	指定したテーブルから取得した結合結果の行番号を修正します。 次の演算子がサポートされています: #<n> 、+ <n> 、-<n> 、および * <n> 。 <n> 演算子はstrtod関数でサポートされています。
  並列実行のヒント	Parallel (テーブル <# of workers> [soft | hard])	指定されたテーブルの並列実行を指定または禁止します。 説明 <worker#> パラメーターは、必要な作業プログラムの数を指定します。 値0は、並列実行を禁止することを指定する。 3番目のパラメーターがsoftに設定されている場合、max_parallel_workers_per_gatherパラメーターの値のみが変更され、その他のパラメーターはオプティマイザによって指定されます。 3番目のパラメーターがhardに設定されている場合、関連するすべてのパラメーターの値が変更されます。 3番目のパラメーターはデフォルトでsoftに設定されています。
  	PX(<# of workers>)	クロスノード並列実行を指定します。 クロスノード並列実行の詳細については、「概要」をご参照ください。 説明 <# of workers> はDOPを指定します。
  	NoPX()	クロスノード並列実行を禁止します。
  GUCパラメータ設定のヒント	セット (GUC-param値)	オプティマイザの実行時のGUCパラメーターの値を指定します。

  説明

  pg_hint_plan拡張機能は、クロスノード並列実行中に生成される実行プランを指定することもできます。 クロスノード並列実行中、Rows(table table[ table...] correction) ヒントはサポートされません。 結合メソッドのヒントは2つのテーブルの結合にのみ使用でき、結合順序のヒントはすべてのテーブルにのみ使用できます。