pg_hint_plan 拡張機能は、特別なコメント文のヒントを使用して実行計画を調整します。
背景情報
PolarDB for PostgreSQL (Oracle 互換) は、コストベースのオプティマイザーを使用します。このオプティマイザーは、固定されたルールではなく統計データを使用して、最適な実行パスを決定します。各 SQL 文に対して、オプティマイザーはすべての可能な実行計画を評価し、コストが最も低いものを選択します。オプティマイザーは最適な実行計画を選択するために最善を尽くします。ただし、オプティマイザーは一部の基礎となるデータリレーションシップを認識していないため、選択された計画が完全ではない場合があります。
Grand Unified Configuration (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)。
ヒント文は、クエリの最初の単語の直後に配置する必要があります。それより前に配置されたヒントは、そのクエリの一部とは見なされません。
拡張機能の作成
拡張機能を作成します。
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)
ヒントの種類
ヒントの種類
サポートされているヒントの種類は、実行計画にどのように影響するかによって分類されます。これらには、スキャンメソッド、結合メソッド、結合順序、行数補正、並列実行、および GUC パラメーター設定のヒントが含まれます。
スキャンメソッドのヒント
スキャンメソッドのヒントは、ターゲットテーブルに特定のスキャンメソッドを強制します。pg_hint_plan は、エイリアスが存在する場合、エイリアスによってターゲットテーブルを識別します。スキャンメソッドの例には、シーケンシャルスキャンやインデックススキャンなどがあります。
スキャンヒントは、標準テーブル、継承テーブル、ログに記録されないテーブル、一時テーブル、およびシステムテーブルに有効です。外部テーブル、テーブル関数、定数値の文、共通テーブル式、ビュー、またはサブクエリには有効ではありません。
次にコマンドの例を示します。
/*+ SeqScan(t1) IndexScan(t2 t2_pkey) */ SELECT * FROM table1 t1 JOIN table table2 t2 ON (t1.key = t2.key);結合メソッドのヒント
結合メソッドのヒントは、関連するテーブルを結合するための特定のメソッドを強制します。これらは、標準テーブル、継承テーブル、ログに記録されないテーブル、一時テーブル、外部テーブル、システムテーブル、テーブル関数、定数値コマンド、および共通テーブル式に有効です。ビューやサブクエリには有効ではありません。
結合順序のヒント
結合順序のヒントは、2 つ以上のテーブルの結合順序を指定します。結合順序は 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 倍します。並列実行のヒント
並列実行のヒントは、並列実行計画を指定します。
並列度のヒントは、標準テーブル、継承テーブル、ログに記録されないテーブル、およびシステムテーブルに有効です。外部テーブル、定数句、共通テーブル式、ビュー、またはサブクエリには有効ではありません。ビューの内部テーブルは、実際の名前またはエイリアスでターゲットにすることができます。
次の 2 つの例は、テーブルに対してクエリを実行するさまざまな方法を示しています。
方法 1: テーブル c1 に 3、テーブル c2 に 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 に 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 パラメーターを複数回設定した場合、最後の設定が有効になります。
次にコマンドの例を示します。
/*+ Set(random_page_cost 2.0) */ SELECT * FROM table1 t1 WHERE key = 'value';
ヒントフォーマットのリスト
次の表に、すべてのヒントでサポートされているフォーマットを示します。機能を使用するには、対応するフォーマットのヒントをコメントに追加します。角括弧 [] はオプションのパラメーターを示します。
タイプ
フォーマット
説明
スキャンメソッド
SeqScan(table)
'table' という名前のテーブルでシーケンシャルスキャンを強制します。
TidScan(table)
'table' という名前のテーブルで TID スキャンを強制します。
IndexScan(table[ index...])
'table' という名前のテーブルでインデックススキャンを強制します。インデックス名を追加することで、使用するインデックスを指定できます。
IndexOnlyScan(table[ index...])
'table' という名前のテーブルでインデックスオンリースキャンを強制します。インデックス名を追加することで、使用するインデックスを指定できます。
BitmapScan(table[ index...])
'table' という名前のテーブルでビットマップインデックススキャンを強制します。インデックス名を追加することで、使用するインデックスを指定できます。
NoSeqScan(table)
'table' という名前のテーブルでのシーケンシャルスキャンを禁止します。
NoTidScan(table)
'table' という名前のテーブルでの TID スキャンを禁止します。
NoIndexScan(table)
'table' という名前のテーブルでのインデックススキャンを禁止します。
NoIndexOnlyScan(table)
'table' という名前のテーブルでのインデックスオンリースキャンを禁止します。
NoBitmapScan(table)
'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> は、希望する並列度 (並列ワーカープロセスの数) です。値 0 は並列実行を禁止します。
3 番目のパラメーターが soft (デフォルト) の場合、max_parallel_workers_per_gather パラメーターの値のみが変更されます。オプティマイザーが実際の並列度を決定します。
hard は指定された並列度を強制します。
PX(<# of workers>)
ノード間並列実行中に並列クエリを指定します。
説明<# of workers> は並列度を指定します。
NoPX()
クエリがノード間並列実行機能を使用しないように強制します。
GUC パラメーター構成のヒント
Set(GUC-param value)
オプティマイザーの実行時に GUC パラメーターを指定された値に設定します。
説明pg_hint_plan は、ノード間並列実行によって生成されるクエリ計画も指定できます。ノード間並列実行シナリオでは、行数補正のヒントはサポートされていません。結合メソッドのヒントは、2 つのテーブル間の結合にのみ適用できます。結合順序のヒントは、すべてのテーブルの全体的な順序のみを指定できます。