pg_hint_plan 拡張を使用して、SQL ステートメントにヒントワードを追加できます。これらのヒントワードは、SQL ステートメントの実行方法を指定するものであり、これにより SQL ステートメントの実行計画を最適化できます。
背景情報
PostgreSQL は静的ルールではなくデータ統計情報を使用するコストベースオプティマイザを採用しています。このオプティマイザは、SQL ステートメントに対して考えられるすべての実行計画のコストを評価し、最もコストが低い実行計画を実行します。オプティマイザは最善を尽くしますが、データ間の潜在的な関係を考慮しないため、選択された実行計画が必ずしも最適とは限りません。
実行計画を調整するために Grand Unified Scheme (GUC) 変数を指定することも可能ですが、その場合、セッション全体に影響を与えてしまいます。セッション全体への影響を避けたい場合は、pg_hint_plan を使用して単一の実行計画のみを最適化できます。
注意事項
-
Data Management Service (DMS) はコメント内のヒントワードをサポートしていません。ご利用のデータベースに接続するには、別のデータベースクライアントをご使用ください。
-
pg_hint_plan 拡張は、ステートメント内の最初のコメントブロックからのみヒントワードを読み取ります。
-
ヒントスキャナーは、英数字、スペース、アンダースコア (_)、カンマ (,)、括弧 (()) 以外の文字に遭遇すると、直ちに処理を停止します。
-
pg_hint_plan は PostgreSQL とは異なる方法でオブジェクト名を処理します。具体的には、オブジェクト名の比較を大文字と小文字を区別して行います。たとえば、TBL という名前のオブジェクトに対するヒントワードは、TBL という名前のオブジェクトにのみ一致し、tbl や Tbl には一致しません。
制限事項
PL/pgSQL ストアドプロシージャ内で pg_hint_plan 拡張を使用する場合、以下の制限事項が適用されます。
-
ヒントワードは、以下の種類のステートメントに対してのみ有効です。
-
単一行を返すクエリ（SELECT、INSERT、UPDATE、DELETE）
-
複数行を返すクエリ（RETURN QUERY）
-
SQL ステートメントの実行（EXECUTE QUERY）
-
カーソルを開くステートメント（OPEN）
-
クエリ結果をループ処理するステートメント（FOR）
-
-
ヒントワードはクエリの最初の単語の直後に配置する必要があります。pg_hint_plan は、最初の単語より前に配置されたヒントワードを無視します。
pg_hint_plan 拡張の作成およびロード
-
拡張を作成します。
CREATE EXTENSION pg_hint_plan; -
拡張をロードします。
-
特定のユーザーに対して拡張を自動的にロードするには、次の手順を実行します。
-
次のコマンドを実行して、拡張をロードします。
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; -
-
データベースクラスター全体に対して拡張を自動的にロードするには、次の手順を実行します。
クォータセンターに移動します。PolarDB PG pg_hint_plan usage クォータの行にある 操作 列の 申請 をクリックして、pg_hint_plan 拡張へのアクセスをリクエストします。
-
拡張が正常にロードされたことを確認します。
-
次のコマンドを実行して、デバッグ出力をクライアントに送信します。
SET pg_hint_plan.debug_print TO on; SET pg_hint_plan.message_level TO notice; -
次のコマンドを実行して、拡張がロードされていることを確認します。
/*+Set(enable_seqscan 1)*/select 1;拡張がロードされている場合、コマンドは次の出力を返します。
NOTICE: pg_hint_plan: used hint: Set(enable_seqscan 1) -
次のコマンドを実行して、デバッグ出力を無効にします。
RESET pg_hint_plan.debug_print; RESET pg_hint_plan.message_level;
-
-
注意事項
コメントヒントワード
pg_hint_plan のコメントブロックは /*+ で始まり、*/ で終わります。ヒントワードは、ヒント名とその後に続く括弧内のパラメーターで構成されます。パラメーターはスペースで区切ります。可読性を高めるため、各ヒントワードを新しい行に記述してもかまいません。
例
次の例では、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)
ヒントワードの種類
-
ヒントワードの種類
ヒントワードは、実行計画に与える影響に基づいて、以下の 6 種類に分類されます。
-
スキャン方法のヒント
このタイプのヒントワードは、指定されたテーブルのスキャン方法を指定します。指定されたテーブルにエイリアスが存在する場合、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 の結合方法を指定します。
-
Leading(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 ステートメントを並列で実行するための計画を指定します。
並列実行ヒントワードは、通常のテーブル、継承テーブル、アンロギングテーブル、システムテーブルに対して有効です。ただし、外部テーブル、定数値が指定された句、汎用式、ビュー、サブクエリに対しては有効ではありません。ビューの内部テーブルは、実際の名前またはエイリアスで指定できます。
次の例は、各テーブルに対して異なる並列度でクエリを実行する方法を示しています。
-
例 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(table)
指定されたテーブルに対してシーケンシャルスキャンを強制します。
TidScan(table)
指定されたテーブルに対して TID (Tuple ID) スキャンを強制します。
IndexScan(table[ index...])
指定されたテーブルに対してインデックススキャンを強制します。使用するインデックスを 1 つ以上指定できます。
IndexOnlyScan(table[ index...])
指定されたテーブルに対してインデックスオンリースキャンを強制します。使用するインデックスを 1 つ以上指定できます。
BitmapScan(table[ index...])
指定されたテーブルに対してビットマップインデックススキャンを強制します。使用するインデックスを 1 つ以上指定できます。
NoSeqScan(table)
指定されたテーブルに対するシーケンシャルスキャンを禁止します。
NoTidScan(table)
指定されたテーブルに対する TID スキャンを禁止します。
NoIndexScan(table)
指定されたテーブルに対するインデックススキャンを禁止します。
NoIndexOnlyScan(table)
指定されたテーブルに対するインデックスオンリースキャンを禁止します。
NoBitmapScan(table)
指定されたテーブルに対するビットマップインデックススキャンを禁止します。
結合メソッドのヒント
NestLoop(table table[ table...])
指定されたテーブルに対してネステッドループ結合を強制します。
HashJoin(table table[ table...])
指定されたテーブルに対してハッシュ結合を強制します。
MergeJoin(table table[ table...])
指定されたテーブルに対してマージ結合を強制します。
NoNestLoop(table table[ table...])
指定されたテーブルに対してネステッドループ結合を禁止します。
NoHashJoin(table table[ table...])
指定されたテーブルに対してハッシュ結合を禁止します。
NoMergeJoin(table table[ table...])
指定されたテーブルに対してマージ結合を禁止します。
結合順序ヒント
Leading(table table[ table...])
テーブルの結合順序を指定します。
Leading(<join pair>)
2 つのテーブルの結合順序と方向を指定します。
行番号修正のヒント
Rows(table table[ table...] correction)
結合結果の推定行数を補正します。使用可能な補正方法には、絶対値 (#<n>)、加算 (+<n>)、減算 (-<n>)、乗算 (*<n>) があり、<n> は行数を表します。
並列実行のヒント
Parallel(table <# of workers> [soft|hard])
指定されたテーブルに対して並列スキャンを強制または無効にします。
説明-
<# of workers> は、希望する並列処理の次数 (DOP)、つまりパラレルワーカープロセスの数を指定します。0 を指定すると並列処理が無効になります。
-
3 番目のパラメーターが
soft（デフォルト）の場合、ヒントはmax_parallel_workers_per_gatherパラメーターの値のみを変更し、実際の並列処理の次数はオプティマイザが決定します。 -
hardパラメーターは、指定された並列処理の次数を強制します。
PX(<# of workers>)
ノード間並列実行を指定します。
説明<# of workers> は、並列処理の次数 (DOP) を指定します。
NoPX()
クエリがノード間並列実行を使用しないようにします。
GUC パラメーター設定ヒントワード
Set(GUC-param value)
オプティマイザが実行計画を生成している間、GUC パラメーターを指定された値に設定します。
説明pg_hint_plan を使用してノード間並列実行の実行計画を指定することもできます。ただし、ノード間並列実行のシナリオでは、行数補正ヒントワードはサポートされていません。結合方法ヒントワードは 2 つのテーブル間の結合にのみ適用でき、結合順序ヒントワードは関与するすべてのテーブルの順序のみを指定できます。
-