このトピックでは、ApsaraDB RDS for PostgreSQLのpg_hint_plan拡張機能について説明します。 この拡張機能を使用して、ApsaraDB RDS for PostgreSQLインスタンスのSQL文の実行計画を変更するヒントを追加できます。
前提条件
- RDSインスタンスはPostgreSQL 10以降を実行します。 説明 pg_hint_plan拡張機能を作成できない場合は、RDSインスタンスのマイナーエンジンバージョンを更新する必要があります。 詳細については、「ApsaraDB RDS For PostgreSQLインスタンスのマイナーエンジンバージョンの更新」をご参照ください。
- pg_hint_planは、RDSインスタンスのshared_preload_librariesパラメーターの値に追加されます。
shared_preload_librariesパラメーターの値にpg_hint_planを追加する方法の詳細については、「ApsaraDB RDS For PostgreSQLインスタンスのパラメーターの変更」をご参照ください。
- この拡張機能は、データ管理 (DMS) ではサポートされません。 別のクライアントを使用してRDSインスタンスに接続することを推奨します。
背景情報
PostgreSQLは、静的ルールではなくデータ統計に基づいて機能するコストベースのオプティマイザを使用します。 オプティマイザーは、各SQL文のすべての可能な実行プランのコストを評価し、コストが最も低い実行プランを選択します。 しかし、オプティマイザは、データ間の可能な内部関係を考慮しない。 したがって、最終的な実行計画は最良の計画ではない可能性があります。 pg_hint_plan拡張機能を使用して、SQL文にヒントを追加できます。 ヒントは、SQL文の実行方法を指定します。 これにより、SQL文の実行計画を最適化できます。
基本的な使用方法
ヒントは、スラッシュ、アスタリスク、プラス記号 (/* +
) で始まり、アスタリスクとスラッシュ (*/
) で終わります。 ヒントは、ヒント名とパラメータで構成されます。 ヒント名はパラメータの前にあります。 パラメータは括弧 () で囲まれ、スペースで区切られています。 読みやすくするために、各ヒントを改行で区切ることができます。
例:
この例では、HashJoinヒントは、pgbench_accountsテーブルをスキャンするためにSeqScanメソッドを使用することを指定します。
/*+
HashJoin(a b)
SeqScan(a)
*/
EXPLAIN SELECT *
pgbench_branchesからb
JOIN pgbench_accounts a ON b.bid = a.bid
a.aidによる注文;
次の応答が返されます。
クエリ計画
---------------------------------------------------------------------------------------
並べ替え (コスト=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という名前のテーブルにヒントを配置できます。 hint_plan.hintsテーブルには、次の表に示す列が含まれています。
フィールド | 説明 |
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
postgres=# UPDATE hint_plan.hints
postgres-# SET hints = 'IndexScan(t1)'
postgres-# WHERE id = 1;
更新1
postgres=# hint_plan.hintsからの削除
postgres-# WHERE id = 1;
削除1
ヒントタイプ
ヒントは、実行計画への影響に基づいて、次のタイプに分類されます。
- スキャン方法のヒント
このタイプのヒントは、指定されたテーブルをスキャンするために使用されるメソッドを指定します。 指定されたテーブルにエイリアスがある場合、pg_hint_plan拡張機能はエイリアスに基づいてテーブルを識別します。 サポートされているスキャン方法には、
SeqScan
、IndexScan
、およびNoSeqScan
があります。スキャンメソッドのヒントは、通常テーブル、継承テーブル、未ログテーブル、一時テーブル、およびシステムテーブルで有効です。 スキャンメソッドのヒントは、外部テーブル、テーブル関数、定数の値が指定されているステートメント、ユニバーサル式、ビュー、およびサブクエリでは無効です。
例:
/*+ 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);
- 行番号修正のヒント
このタイプのヒントは、オプティマイザによって引き起こされる行番号エラーを修正します。
例:
/* + 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 3ハード) パラレル (c2 5ハード) * / c1からc2.aを選択c2に参加 (c1.a = c2.a);
次の応答が返されます。
クエリ計画 ------------------------------------------------------------------------------- ハッシュ結合 (コスト=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:
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:
- GUCパラメータ設定のヒント
このタイプのヒントは、GUCパラメーターの値を一時的に変更します。 実行計画のGUCパラメーターの値は、期待どおりの効果を得るのに役立ちます。 ただし、指定されたヒントが他のSQL文の実行プランと競合する場合、これは適用されません。 GUCパラメーターを複数回設定すると、最新の値が有効になります。
例:
/* + セット (random_page_cost 2.0) * / SELECT * FROM table1 t1 WHERE key = 'value';
次の表に、pg_hint_plan拡張機能でサポートされているすべてのヒントを示します。
データ型 | Format | 説明 |
スキャン方法のヒント | 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です。 |
GUCパラメータ設定のヒント | セット (GUC-param値) | オプティマイザの実行時のGUCパラメーターの値を指定します。 |
詳細については、PostgreSQL Webサイトをご覧ください。