すべてのプロダクト
Search

このプロダクト

    PolarDB:pg_hint_plan

    ドキュメントセンター

    PolarDB:pg_hint_plan

    最終更新日:Jun 05, 2024

    pg_hint_plan拡張機能を使用して、SQL文にヒントを追加できます。 ヒントは、SQL文の実行方法を指定します。 これにより、SQL文の実行計画を最適化できます。

    背景情報

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

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

    注意事項

    • データ管理 (DMS) はヒントをサポートしていません。 データベースにアクセスするには、他の方法を使用する必要があります。
    • pg_hint_planは、最初のコメントブロックのみからヒントを読み取ります。
    • pg_hint_planは、英数字、スペース、および次の特殊文字のみを受け入れます。_、() 他の文字はすぐに解析を停止します。
    • pg_hint_planは、オブジェクト名を大文字と小文字を区別して比較します。これは、PostgreSQLがオブジェクト名を比較する方法とは異なります。 たとえば、ヒント内のTBLという名前のオブジェクトは、データベース内のTBLのみに一致します。 tblまたはTblという名前のオブジェクトは無視されます。

    制限事項

    PL/pgSQLを使用して定義されたストレージプロシージャにpg_hint_planを使用するには、次の制限があります。
    • ヒントは、次のタイプのステートメントに対してのみ有効です。
      • SELECT、INSERT、UPDATE、およびDELETEステートメント。
      • RETURN QUERYステートメント。
      • EXECUTE QUERYステートメント。
      • OPENステートメント。
      • 声明のために。
    • ヒントは、SQL文の最初の単語の直後に配置する必要があります。 ヒントが最初の単語の前に配置されている場合、ヒントはクエリの一部とは見なされません。

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

    1. エクステンションを作成します。 
      拡張の作成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リセットsession_preload_libraries;
ALTER DATABASE xxxリセットsession_preload_libraries;
      • データベースクラスターの拡張機能を自動的に読み込みます。

        pg_hint_plan拡張機能を使用するには、Quota Centerに移動します。 polardb_pg_pg_hint_planに対応する [操作] 列で [適用] をクリックします。

      • 拡張機能がロードされているかどうかを確認します。
        1. 次のステートメントを実行して、デバッグ情報をクライアントに送信できるようにします。 
          SET pg_hint_plan.debug_printに;
pg_hint_plan.message_levelを通知します。
        2. 次のステートメントを実行して、拡張機能がロードされているかどうかを確認します。 
          /* + Set(enable_seqscan 1)*/select 1;
          拡張子が読み込まれた場合、次の情報が返されます。 
          注意: pg_hint_plan: 使用済みヒント: セット (enable_seqscan 1)
        3. 次のステートメントを実行して、クライアントへのデバッグ情報の送信を停止します。 
          RESET pg_hint_plan.debug_print;
リセットpg_hint_plan.message_level;

    使用上の注意

    • 基本的な使い方

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

      例：

      この例では、結合方法としてHashJoinが使用され、pgbench_accountsテーブルはSeqScanメソッドを使用してスキャンされます。
      /* +
   HashJoin(a b)
   SeqScan(a)
 * /
EXPLAIN SELECT *
   pgbench_branchesからb
   JOIN pgbench_accounts a ON b.bid = a.bid
   ORDER BY a.aid;
      次の結果が返されます。
      QUERY PLAN
---------------------------------------------------------------------------------------
 並べ替え (コスト=31465.84 .. 31715.84行=100000幅=197)
   ソートキー: a.aid
   -> ハッシュ参加 (コスト=1.02 .. 4016.02行=100000幅=197)
         Hash Cond: (a.bid = b.bid)
         -> pgbench_accounts aのSeqスキャン (コスト=0.00 .. 2640.00行=100000幅=97)
         -> ハッシュ (コスト=1.01 .. 1.01行=1幅=100)
               -> pgbench_branchbのSeqスキャン (コスト=0.00 .. 1.01行=1幅=100)
(7行)
    • ヒントテーブル
      ヒントを使用して、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)
    値 (
        'EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?;'、
        '',
        'SeqScan(t1)'
    );
挿入0 1
 更新hint_plan.hints
    SETヒント='IndexScan(t1)'
  WHERE id = 1;
更新1
 hint_plan.hintsから削除
  WHERE id = 1;
削除1

    ヒントタイプ

    • ヒントタイプ

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

      • Hints for scan methods

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

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

        例: 
        /* +
    SeqScan(t1)
    IndexScan(t2 t2_pkey)
 */
 SELECT * FROM table1 t1 JOINテーブルtable2 t2 ON (t1.key = t2.key);
      • 結合メソッドのヒント

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

      • 参加注文のヒント
        このタイプのヒントは、2つ以上のテーブルを結合する順序を指定します。 次のいずれかの方法を使用して、結合順序のヒントを指定できます。
        • 各結合レベルで方向を制限せずに、指定したテーブルを結合する順序を指定します。
        • 指定したテーブルを結合する順序と、各結合レベルでの方向を指定します。
        例: 
        /* +
    NestLoop(t1 t2)
    MergeJoin(t1 t2 t3)
    リーディング (t1 t2 t3)
  */
 SELECT * FROM table1 t1
     JOINテーブルtable2 t2 ON (t1.key = t2.key)
     JOINテーブル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... ; # 結合結果の行数を10に設定します。
 /* + Rows(a b + 10) */ SELECT... ; # 行番号を10増やします。
 /* + Rows(a b -10) */ SELECT... ; # 行番号を10だけ減らします。
 /* + Rows(a b * 10) */ SELECT... ; # 行番号を10倍に増やします。
      • 並列実行のヒント

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

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

        次の例は、各テーブルでSQL文を異なる方法で実行する方法を示しています。
        • 例1: c1テーブルの並列度 (DOP) を3に設定し、c2テーブルのDOPを5に設定します。 
          EXPLAIN /* + パラレル (c1 3ハード) パラレル (c2 5ハード) * /
       SELECT c2.a FROM c1 JOIN c2 ON (c1.a = c2.a);
          次の結果が返されます。
          QUERY PLAN
-------------------------------------------------------------------------------
 ハッシュ結合 (コスト=2.86 .. 11406.38行=101幅=4)
   ハッシュポンド :( c1.a = c2.a)
   -> 収集 (コスト=0.00 .. 7652.13行=1000101幅=4)
         Workers Planned: 3
         -> Parallel Seq c1のスキャン (コスト=0.00 .. 7652.13行=322613幅=4)
   -> ハッシュ (コスト=1.59 .. 1.59行=101幅=4)
         -> 収集 (コスト=0.00 .. 1.59行=101幅=4)
               計画されている労働者: 5
               -> c2のパラレルSeqスキャン (コスト=0.00 .. 1.59行=59幅=4)
        • 例2: t1テーブルのDOPを5に設定します。 
          EXPLAIN /* + Parallel(tl 5 hard) */ SELECT sum(a) FROM tl;
          次の結果が返されます
          。-----------------------------------------------------------------------------------
 集計の完了 (コスト=693.02 .. 693.03行=1幅=8)
   -> 収集 (コスト=693.00 .. 693.01行=5幅=8)
         計画されている労働者: 5
         -> 部分集約 (コスト=693.00 .. 693.01行=1幅=8)
               -> Parallel Seqスキャン (コスト=0.00 .. 643.00行=20000幅=4)
      • GUCパラメータ設定のヒント

        このタイプのヒントは、GUCパラメーターの値を一時的に変更します。 GUCパラメーターの値は、エグゼキュータが実行プランを生成した場合にのみ有効です。 この値は、セッション全体に影響を与えることなく、クエリのパフォーマンスを向上させます。 GUCパラメーターに複数のヒントを設定すると、最新のヒントが有効になります。

        例: 
        /* + セット (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つのテーブルの結合にのみ使用でき、結合順序のヒントはすべてのテーブルにのみ使用できます。