pg_hint_planを用いて実行計画を制御する

pg_hint_planは、クエリに実行計画を示すヒント句を指定することで、ユーザーが実行計画を制御できるようにするツールです。
このページでは、pg_hint_planを使用する際の注意点や運用を想定したサンプルシナリオを紹介します。
※pg_hint_planの機能概要や基本的な使い方はこちらをご覧ください。

pg_hint_planのメリット
pg_hint_planの使い方
サンプルシナリオ
注意事項

pg_hint_planのメリット

PostgreSQLでは、クエリ実行時にプランナが作成する実行計画が必ずしも最適な実行計画とならない場合がありますが、
その際にユーザーは実行計画に一切介入できません。
pg_hint_planを使用することによって、ユーザー側で実行計画を直接指示できるため、DBの状況に応じたクエリ実行の
細かな最適化を実現できます。

pg_hint_planの使い方

初期設定

pg_hint_planを使用するために必要な初期設定を行います。

1. PostgreSQL本体の設定ファイル(「postgresql.conf」)の修正
設定ファイルにpg_hint_planの事前ロード用の設定を追加します。

$ vi /var/lib/pgsql/14/data/postgresql.conf
#下記を追記して上書き保存
shared_preload_libraries = 'pg_hint_plan'

ファイル編集後、PostgreSQLサーバを起動、または再起動してください。

2. pg_hint_planエクステンションをPostgreSQLに登録
pg_hint_planを使用したいデータベース上で、pg_hint_planエクステンションをPostgreSQLに登録します。

$ psql
postgres=# CREATE EXTENSION pg_hint_plan;

ヒント句の指定方法

PostgreSQLでpg_hint_planを使用してSQL文にヒント句を指定する方法は2種類あります。

・コメント文による指定
SQL文内の特殊なブロックコメント内にヒントを記述する方法です。
「/*+ ヒント */ クエリ」というフォーマットで記述します。

・事前のテーブル登録による指定
上記の他に、ヒント用のテーブルhint_plan.hintsに予めヒントを登録しておく方法もあります。
具体的なやり方としては、まず、以下のいずれかを実行してヒント用のテーブルを使用する設定を有効にします。

・ PostgreSQL本体の設定ファイル(「postgresql.conf」)に「pg_hint_plan.enable_hint_table = on」を追記後、設定反映($ pg_ctl reload)
・ postgres=# SET pg_hint_plan.enable_hint_table TO on;

SET文で設定を変更した場合、変更はそのセッション内でのみ有効となります。

次に、ヒント用のテーブルにヒント指定用のレコードを追加します。

postgres=# INSERT INTO hint_plan.hints(norm_query_string, application_name, hints)
VALUES ( 'SELECT * FROM table_1 t1 JOIN table_2 t2 ON t1.id = t2.id ORDER BY t1.id;','', 'HashJoin(t1 t2) SeqScan(t1)');

クライアントの実行したクエリ文が、ヒント用のテーブル内に登録されているクエリ文と合致した場合にヒントが適用されます。
なお、hint_plan.hintsテーブルの内容は以下のようになっています。

表1 hint_plan.hintsテーブルの説明
列名	内容	注意点
id	ヒント番号 (連番型)	新しいヒントの登録時にはこの列を指定しない
norm_query_string	クエリ文	・クエリ文はスペース・改行も含めて厳密に区別される・対象のクエリに定数があるときは「?」に置き換える
application_name	ヒント適用対象のアプリケーション名	ここで指定した名前が、セッションの「application_name」GUCパラメータと等しい場合にヒントが適用される。全てのアプリケーションにヒントを適用したいときは空文字列を登録する
hints	ヒント句	クエリ文でテーブル名に別名が付いている場合、ヒント句では別名を使う

指定できるヒント句の種類

pg_hint_planで使用できるヒント句には以下のものがあります。

表2 指定できるヒント句の種類
種類	説明	実際の使用例
スキャン方式	単一オブジェクト内でのスキャン方式を指定する	指定したテーブルについてSeq Scanを選択する /+ SeqScan(t1) / SELECT...
結合方式	複数オブジェクト間での結合方式を指定する	指定したテーブル間の結合にNested Loopを選択する /+ NestLoop(t1 t2) / SELECT...
結合順序	複数オブジェクト間の結合順序を指定する	テーブルt1とt3を最初に結合し、その後にt2を結合する /+ Leading(t1 t3 t2) / SELECT...
見積もり件数補正	複数オブジェクト間の結合結果の件数を補正する	テーブルt1とt2の結合結果の件数を10件に補正する /+ Rows(t1 t2 #10) / SELECT...
並列実行	並列実行の方法(ワーカー数と実行強制度)を指定する	テーブルt1のスキャン時にワーカー数4での実行を強制する /+ Parallel(t1 4 hard) / SELECT...
GUCパラメータ	PostgreSQL本体の実行計画パラメータの設定値を一時的に変更する	実行計画内においてIndexScanが選択されないようにする (このクエリのみ) /+ Set(enable_indexscan off) / SELECT...
結合時の挙動制御	指定したテーブル間の結合時において、最上位の内部結合の計画の結果のキャッシング(Memoize)の有効/無効を指定する	テーブル間結合時に結合計画の結果のキャッシングを有効にする (強制ではない) /+ Memoize(t1 t2) / SELECT...

実行例

では、実際にpg_hint_planを使ってみましょう。
ここでは、コメント文でIndexScanを指定する例を示します(指定なし時はスキャン方式に「Seq Scan」が使用された)。

postgres=# /*+ IndexScan(t1) */

postgres-# EXPLAIN ANALYZE
postgres-# SELECT * FROM table_a t1 WHERE update_time >= '2023-10-27 00:00:00.000000';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using table_a_update_time_idx on table_a t1 (cost=0.42..34853.43 rows=1000000 width=23) (actual time=0.037..262.575 rows=1000000 loops=1)
Index Cond: (update_time >= '2023-10-27 00:00:00'::timestamp without time zone)
Planning Time: 0.999 ms
Execution Time: 307.402 ms

ヒント句に従い、スキャン方式に「Index Scan」が使用されていることがわかります。

サンプルシナリオ

ここからは、実際の運用を想定しpg_hint_planで実行計画を改善する例をご紹介します。

1. 実行計画における各処理の処理時間(actual timeの値)を確認する

postgres=# EXPLAIN ANALYZE
SELECT * FROM table_1 t1 JOIN table_2 t2 ON (t1.id = t2.id) WHERE (t2.id BETWEEN 300001 AND 600000);
                                                                      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=16886.93..49377.94 rows=303031 width=46) (actual time=791.328..2109.705 rows=300000 loops=1)
   Hash Cond: (t1.id = t2.id)
   -> Seq Scan on table_1 t1 (cost=0.00..16370.00 rows=1000000 width=23) (actual time=0.031..573.206 rows=1000000 loops=1)
   -> Hash (cost=11323.04..11323.04 rows=303031 width=23) (actual time=407.191..407.193 rows=300000 loops=1)
         Buckets: 65536 Batches: 8 Memory Usage: 2561kB
         -> Index Scan using table_2_pkey on table_2 t2 (cost=0.42..11323.04 rows=303031 width=23) (actual time=0.016..206.809 rows=300000 loops=1)
               Index Cond: ((id >= 300001) AND (id <= 600000))
Planning Time: 0.298 ms
Execution Time: 2266.987 ms

2. 処理時間が長くなっている部分を改善できないか考える
1. より、テーブル結合に時間がかかっていることがわかります。
そこで、結合方式をプランナのデフォルト選択(Hash Join)からNested Loopに変更してみます。

postgres=# /*+ NestLoop(t1 t2)*/
           EXPLAIN ANALYZE
           SELECT * FROM table_1 t1 JOIN table_2 t2 ON (t1.id = t2.id) WHERE (t2.id BETWEEN 300001 AND 600000);
                                                                         QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.85..111921.52 rows=303031 width=46) (actual time=0.300..578.973 rows=300000 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   -> Nested Loop (cost=0.85..80618.42 rows=126263 width=46) (actual time=0.048..541.174 rows=100000 loops=3)
         -> Parallel Index Scan using table_2_pkey on table_2 t2 (cost=0.42..9555.36 rows=126263 width=23) (actual time=0.022..76.475 rows=100000 loops=3)
               Index Cond: ((id >= 300001) AND (id <= 600000))
         -> Index Scan using table_1_pkey on table_1 t1 (cost=0.42..0.56 rows=1 width=23) (actual time=0.003..0.003 rows=1 loops=300000)
               Index Cond: (id = t2.id)
Planning Time: 0.228 ms
Execution Time: 756.527 ms

ヒント句で結合方式を指定することにより、処理時間を改善できました。

注意事項

最後に、pg_hint_planを使用する際の注意点を記載します。

SQL文内におけるヒント句の挿入位置
ヒント句は基本的にSQL文の先頭に配置します。
SQL文の先頭以外に配置する場合、文字種の制約が原因となってヒント句が無視される場合があります。

SQL文でテーブル名に別名が付いている場合の扱い
テーブル名に別名が付いている場合、以下「正しい例」のようにヒント句内では別名を使う必要があります。

#正しい例
/*+ NestLoop(t1 t2) */
SELECT * FROM table_1 t1 JOIN table_2 t2 ON t1.id = t2.id;

#誤った例
/*+ NestLoop(table_1 table_2) */
SELECT * FROM table_1 t1 JOIN table_2 t2 ON t1.id = t2.id;

「誤った例」のように元テーブル名を使うと無効なヒントとなります。

ヒント用のテーブル使用時の注意
以下の各項目にご注意ください。
・pg_hint_plan.enable_hint と pg_hint_plan.enable_hint_table を on に設定する
・ヒント用のテーブルに登録したSQL文と、実行されるSQL文がスペース・改行も含めて一字一句完全合致でないと
ヒントは適用されない
・テーブルに登録するSQL文に定数があるときは「?」に置き換える

#正しい例
postgres=# SELECT * FROM hint_plan.hints;
id |             norm_query_string             | application_name |    hints
----+-------------------------------------------+------------------+-------------
1 | SELECT * FROM table_1 t1 WHERE t1.id = ?; |                  | SeqScan(t1)

#誤った例
postgres=# SELECT * FROM hint_plan.hints;
id |               norm_query_string                | application_name |    hints
----+------------------------------------------------+------------------+-------------
2 | SELECT * FROM table_1 t1 WHERE t1.id = 500000; |                  | SeqScan(t1)

「誤った例」のように定数をそのままにすると、完全一致でもヒントは適用されません。

種類	説明	実際の使用例
スキャン方式	単一オブジェクト内でのスキャン方式を指定する	指定したテーブルについてSeq Scanを選択する /+ SeqScan(t1) / SELECT...
結合方式	複数オブジェクト間での結合方式を指定する	指定したテーブル間の結合にNested Loopを選択する /+ NestLoop(t1 t2) / SELECT...
結合順序	複数オブジェクト間の結合順序を指定する	テーブルt1とt3を最初に結合し、その後にt2を結合する /+ Leading(t1 t3 t2) / SELECT...
見積もり件数補正	複数オブジェクト間の結合結果の件数を補正する	テーブルt1とt2の結合結果の件数を10件に補正する /+ Rows(t1 t2 #10) / SELECT...
並列実行	並列実行の方法(ワーカー数と実行強制度)を指定する	テーブルt1のスキャン時にワーカー数4での実行を強制する /+ Parallel(t1 4 hard) / SELECT...
GUCパラメータ	PostgreSQL本体の実行計画パラメータの設定値を一時的に変更する	実行計画内においてIndexScanが選択されないようにする (このクエリのみ) /+ Set(enable_indexscan off) / SELECT...
結合時の挙動制御	指定したテーブル間の結合時において、最上位の内部結合の計画の結果のキャッシング(Memoize)の有効/無効を指定する	テーブル間結合時に結合計画の結果のキャッシングを有効にする (強制ではない) /+ Memoize(t1 t2) / SELECT...

サイト内の現在位置

PostgreSQL