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という名前のオブジェクトは無視されます。
制限事項
- ヒントは、次のタイプのステートメントに対してのみ有効です。
- SELECT、INSERT、UPDATE、およびDELETEステートメント。
- RETURN QUERYステートメント。
- EXECUTE QUERYステートメント。
- OPENステートメント。
- 声明のために。
- ヒントは、SQL文の最初の単語の直後に配置する必要があります。 ヒントが最初の単語の前に配置されている場合、ヒントはクエリの一部とは見なされません。
pg_hint_plan拡張機能の作成と読み込み
- エクステンションを作成します。
拡張の作成pg_hint_plan;
- エクステンションをロードします。
- 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に対応する [操作] 列で [適用] をクリックします。
- 拡張機能がロードされているかどうかを確認します。
- 次のステートメントを実行して、デバッグ情報をクライアントに送信できるようにします。
SET pg_hint_plan.debug_printに; pg_hint_plan.message_levelを通知します。
- 次のステートメントを実行して、拡張機能がロードされているかどうかを確認します。
拡張子が読み込まれた場合、次の情報が返されます。/* + Set(enable_seqscan 1)*/select 1;
注意: pg_hint_plan: 使用済みヒント: セット (enable_seqscan 1)
- 次のステートメントを実行して、クライアントへのデバッグ情報の送信を停止します。
RESET pg_hint_plan.debug_print; リセットpg_hint_plan.message_level;
- 次のステートメントを実行して、デバッグ情報をクライアントに送信できるようにします。
- 1人のユーザーの拡張機能を自動的にロードします。
使用上の注意
- 基本的な使い方
ヒントは、スラッシュ、アスタリスク、プラス記号 (
/* +
) の組み合わせで始まり、アスタリスクとスラッシュ (*/
) の組み合わせで終わります。 ヒントは、ヒント名とパラメータで構成されます。 パラメータは括弧 () で囲まれ、スペースで区切られています。 読みやすくするために、各ヒントを新しい行で開始できます。例:
この例では、結合方法として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という名前のテーブルにヒントを配置できます。 テーブルは次の列で構成されています。
ヒントテーブルの例を次に示します。 デフォルトでは、pg_hint_plan拡張機能を作成するユーザーには、ヒントテーブルに対する権限があります。 ステートメントコメントとヒントテーブルの両方にヒントが追加されると、ヒントテーブルのヒントがコメントのヒントよりも優先されます。列 説明 id ヒントのID。 IDは一意であり、自動的に生成されます。 norm_query_string ヒントを追加するSQL文と一致するパターン。 SQL文の定数は、ワイルドカードとして機能する疑問符 (?) で置き換える必要があります。 スペース文字は、パターンの必要な部分です。 application_name ヒントが適用されるアプリケーションの名前。 このパラメーターが空の場合、ヒントはすべてのアプリケーションに適用されます。 ヒント ヒントを含むコメント。 コメントマークを含める必要はありません。 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)
- 例1: c1テーブルの並列度 (DOP) を3に設定し、c2テーブルのDOPを5に設定します。
- GUCパラメータ設定のヒント
このタイプのヒントは、GUCパラメーターの値を一時的に変更します。 GUCパラメーターの値は、エグゼキュータが実行プランを生成した場合にのみ有効です。 この値は、セッション全体に影響を与えることなく、クエリのパフォーマンスを向上させます。 GUCパラメーターに複数のヒントを設定すると、最新のヒントが有効になります。
例:/* + セット (random_page_cost 2.0) * / SELECT * FROM table1 t1 WHERE key = 'value';
- Hints for scan methods
- ヒント構文の一覧次の表に、サポートされているすべてのヒント構文を示します。 ヒントをクエリのコメントとしてサーバー固有の目的に追加できます。 オプションのパラメーターは、構文で括弧 ([ ]) のペアで囲まれています。
データ型 ヒント構文 説明 スキャン方法のヒント 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つのテーブルの結合にのみ使用でき、結合順序のヒントはすべてのテーブルにのみ使用できます。