すべてのプロダクト
Search
ドキュメントセンター

PolarDB:pg_hint_plan

最終更新日:Jun 21, 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つのテーブルの結合にのみ使用でき、結合順序のヒントはすべてのテーブルにのみ使用できます。