Aurora PostgreSQL pg_hint_plan エクステンション

pg_hint_plan の有効化
ヒント句の指定
ヒントテーブル

pg_hint_plan は SQL 文にヒント句を指定するエクステンション。

SQL 文に /*+ HashJoin(a b) */ のようなヒントを指定する方法と、hint_plan.hints テーブルに予めヒント定義する方法の2つがあり、実行計画の選択をコントロールできる。

pg_hint_plan の有効化

pg_hint_plan を利用するにはエクステンションを有効化する

CREATE EXTENSION pg_hint_plan;

現在のエクステンションは以下で確認できる。

select * from pg_extension;

oid|extname     |extowner|extnamespace|extrelocatable|extversion|...
---+------------+--------+------------+--------------+----------+...
...|plpgsql     |      10|          11|false         |1.0       |...
...|pg_hint_plan|      10|      184339|false         |1.6.0     |...

上記により、hint_plan スキーマ、hint_plan.hints テーブルが作成される。

パラメータグループに pg_hint_plan のロード設定を追加する(デフォルトのパラメータグループを利用している場合はカスタムパラメータグループを作成してインスタンスに紐づけ変更)。

shared_preload_libraries に pg_hint_plan を追加する。

shared_preload_libraries の設定は Static であるため、インスタンスの再起動が必要となる。

pg_hint_plan の GUC パラメータは以下(以下いずれも動的パラメータなので即時反映可能)。

パラメータ名	説明	デフォルト値
`pg_hint_plan.enable_hint`	`pg_hint_plan` を有効化	`on`(`1`)
`pg_hint_plan.enable_hint_table`	`hint_plan.hints` テーブルでのヒント指定を有効化	`off`(`0`)
`pg_hint_plan.parse_messages`	ヒントの構文解析エラー時のログレベル。`error`,`warning`,`notice`,`info`,`log`,`debug`	`INFO`
`pg_hint_plan.debug_print`	デバッグログレベル。`off`,`on`,`detailed`,`verbose`	`off`
`pg_hint_plan.message_level`	動作ログレベル。`error`,`warning`,`notice`,`info`,`log`,`debug`	`INFO`

hint_plan.hints テーブルでのヒント指定する場合は pg_hint_plan.enable_hint_table を on とする(インスタンスパラメータグループの設定が空であれば、クラスタパラメータグループ側で一括設定できる)。

現在の接続に対して設定する場合は以下

SET pg_hint_plan.enable_hint_table TO on

なお、CREATE EXTENSION pg_hint_plan; を行っていない状態で pg_hint_plan.enable_hint_table を設定すると、relation "hint_plan.hints" does not exist というエラーで接続できなくなるため注意が必要。

ヒント句の指定

pg_hint_plan は最初に見つかったブロックコメントからのみヒントを読み取る。

/*+
 HashJoin(a b)
 SeqScan(a)
 Set(random_page_cost 2.0)
*/
/*+ IndexScan(a) */
EXPLAIN SELECT /*+ MergeJoin(a b) */ *
 FROM pgbench_branches b
 JOIN pgbench_accounts a ON b.bid = a.bid
 ORDER BY a.aid;

上記では、HashJoin(a b) と SeqScan(a) Set(random_page_cost 2.0) のみが有効となる。

ヒントは以下の種類がある。

Group	Format	Description
Scan method	`SeqScan(table)`	テーブル上で順次スキャンを強制
	`TidScan(table)`	テーブル上で TID スキャンを強制
	`IndexScan(table[ index...])`	テーブルに対してインデックススキャンを強制。指定されたインデックスがある場合は、そのインデックスに制限される
	`IndexOnlyScan(table[ index...])`	テーブルに対してインデックスのみのスキャンを強制。指定されたインデックスがある場合は、そのインデックスに制限される。インデックスのみのスキャンが使用できない場合は、インデックススキャンを使用できる
	`BitmapScan(table[ index...])`	テーブル上でビットマップスキャンを強制。指定されたインデックスがある場合は、そのインデックスに制限される
	`IndexScanRegexp(table[ POSIX Regexp...])` `IndexOnlyScanRegexp(table[ POSIX Regexp...])` `BitmapScanRegexp(table[ POSIX Regexp...])`	テーブルに対してインデックススキャン、インデックスのみのスキャン (PostgreSQL 9.2 以降の場合)、またはビットマップスキャンを強制。指定された POSIX 正規表現パターンに一致するインデックスに制限する
	`NoSeqScan(table)`	テーブル上で順次スキャンを行わないように強制
	`NoTidScan(table)`	テーブル上で TID スキャンを実行しないように強制
	`NoIndexScan(table)`	テーブルに対してインデックススキャンとインデックスのみのスキャンを実行しないように強制
	`NoIndexOnlyScan(table)`	テーブルに対してインデックスのみのスキャンを行わないように強制
	`NoBitmapScan(table)`	テーブル上でビットマップスキャンを行わないように強制
Join method	`NestLoop(table table[ table...])`	指定されたテーブルの結合に対してネストされたループを強制
	`HashJoin(table table[ table...])`	指定されたテーブルの結合にハッシュ結合を強制
	`MergeJoin(table table[ table...])`	指定されたテーブルの結合に対してマージ結合を強制
	`NoNestLoop(table table[ table...])`	指定されたテーブル上の結合に対してネストされたループを実行しないように強制
	`NoHashJoin(table table[ table...])`	指定されたテーブル上の結合に対してハッシュ結合を行わないように強制
	`NoMergeJoin(table table[ table...])`	指定されたテーブル上の結合に対してマージ結合を行わないように強制
Join order	`Leading(table table[ table...])`	指定されたとおりに結合順序を強制
	`Leading(<join pair>)`	指定されたとおりに結合順序と方向を強制。結合ペアは、括弧で囲まれたテーブルのペアや他の結合ペアであり、ネストされた構造を作成できる
Behavior control on Join	`Memoize(table table[ table...])`	指定されたテーブル間の結合の最上位結合で、内部結果をメモ化できるようにする
	`NoMemoize(table table[ table...])`	指定されたテーブル間の結合の最上位結合が内部結果をメモ化することを禁止
Row number correction	`Rows(table table[ table...] correction)`	指定されたテーブルの結合結果の行番号を修正。使用可能な修正方法は、絶対 (`#`)、加算 (`+`)、減算 (`-`)、および乗算 (`*`)。strtod() が理解できる文字列である必要がある
Parallel query configuration	`Parallel(table <# of workers> [soft\|hard])`	指定されたテーブルの並列実行を強制または禁止。`<# of workers>` は、必要な並列ワーカーの数で。ゼロは並列実行を禁止することを意味する。`soft` (デフォルト) の場合、`max_parallel_workers_per_gather` のみを変更し、その他はすべてプランナーに任せる。`hard` は、指定されたワーカー数を強制する
GUC	`Set(GUC-param value)`	プランナーの実行中に定義された値に GUC パラメータを設定する

ヒントテーブル

クエリを編集できない場合は、hint_plan.hints テーブルにヒントを配置できる(pg_hint_plan.enable_hint_table が on になっている必要)。

カラム	説明
`id`	ID値(シーケンスによる自動採番値)
`norm_query_string`	実行計画を制御したいクエリを指定。対象のクエリに定数があるときは、`?` に置き換える。スペースの数や `;` の有無など含めて一致する必要がある
`application_name`	`application_name`セッションがヒントを適用できる値(空文字は、すべてのセッションがヒントを適用することを意味する)
`hints`	ヒントフレーズ(周囲のコメントマークを除いた一連のヒント)

PostgreSQL 17 からは、norm_query_string ではなく、query_id (クエリを特定するIDで、compute_query_id が有効な場合に生成される)

以下のようにSQLを登録しておくことで、指定したヒントが自動的に適用されるようになる。

INSERT INTO hint_plan.hints(norm_query_string, application_name, hints)
VALUES (
    'EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?;',
    '',
    'SeqScan(t1)');

SQLは登録内容と完全一致している必要があるので注意が必要。

PostgreSQL 17 以降の場合は、compute_query_id パラメータを有効化し、SQL文ではなくクエリID を指定する。

クエリIDは、pg_stat_statements または EXPLAIN (VERBOSE) できる。

EXPLAIN (VERBOSE, COSTS false) SELECT * FROM t1 WHERE t1.id = 1;
               QUERY PLAN
----------------------------------------
 Seq Scan on public.t1
   Output: id, id2
   Filter: (t1.id = 1)
 Query Identifier: -7164653396197960701
(4 rows)

Query Identifier で出力された値を指定する。

INSERT INTO hint_plan.hints(query_id, application_name, hints)
     VALUES (-7164653396197960701, '', 'SeqScan(t1)');

SQLコメントとして定義したものより、ヒントテーブルで指定したものが優先される。