Japan
サイト内の現在位置
PostgreSQL
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 |
ファイル編集後、PostgreSQLサーバを起動、または再起動してください。
2. pg_hint_planエクステンションをPostgreSQLに登録
pg_hint_planを使用したいデータベース上で、pg_hint_planエクステンションをPostgreSQLに登録します。
$ psql |
ヒント句の指定方法
PostgreSQLでpg_hint_planを使用してSQL文にヒント句を指定する方法は2種類あります。
・コメント文による指定
SQL文内の特殊なブロックコメント内にヒントを記述する方法です。
「/*+ ヒント */ クエリ」というフォーマットで記述します。
・事前のテーブル登録による指定
上記の他に、ヒント用のテーブルhint_plan.hintsに予めヒントを登録しておく方法もあります。
具体的なやり方としては、まず、以下のいずれかを実行してヒント用のテーブルを使用する設定を有効にします。
・ PostgreSQL本体の設定ファイル(「postgresql.conf」)に「pg_hint_plan.enable_hint_table = on」を追記後、設定反映($ pg_ctl reload) |
SET文で設定を変更した場合、変更はそのセッション内でのみ有効となります。
次に、ヒント用のテーブルにヒント指定用のレコードを追加します。
postgres=# INSERT INTO hint_plan.hints(norm_query_string, application_name, hints) |
クライアントの実行したクエリ文が、ヒント用のテーブル内に登録されているクエリ文と合致した場合にヒントが適用されます。
なお、hint_plan.hintsテーブルの内容は以下のようになっています。
列名 | 内容 | 注意点 |
---|---|---|
id | ヒント番号 (連番型) | 新しいヒントの登録時にはこの列を指定しない |
norm_query_string | クエリ文 | ・クエリ文はスペース・改行も含めて厳密に区別される ・対象のクエリに定数があるときは「?」に置き換える |
application_name | ヒント適用対象の アプリケーション名 |
ここで指定した名前が、セッションの「application_name」GUCパラメータと等しい場合にヒントが適用される。全てのアプリケーションにヒントを適用したいときは空文字列を 登録する |
hints | ヒント句 | クエリ文でテーブル名に別名が付いている場合、 ヒント句では別名を使う |
指定できるヒント句の種類
pg_hint_planで使用できるヒント句には以下のものがあります。
種類 | 説明 | 実際の使用例 |
---|---|---|
スキャン方式 | 単一オブジェクト内でのスキャン方式を 指定する |
指定したテーブルについて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 |
ヒント句に従い、スキャン方式に「Index Scan」が使用されていることがわかります。
サンプルシナリオ
ここからは、実際の運用を想定しpg_hint_planで実行計画を改善する例をご紹介します。
1. 実行計画における各処理の処理時間(actual timeの値)を確認する
postgres=# EXPLAIN ANALYZE |
2. 処理時間が長くなっている部分を改善できないか考える
1. より、テーブル結合に時間がかかっていることがわかります。
そこで、結合方式をプランナのデフォルト選択(Hash Join)からNested Loopに変更してみます。
postgres=# /*+ NestLoop(t1 t2)*/ |
ヒント句で結合方式を指定することにより、処理時間を改善できました。
注意事項
最後に、pg_hint_planを使用する際の注意点を記載します。
SQL文内におけるヒント句の挿入位置
ヒント句は基本的にSQL文の先頭に配置します。
SQL文の先頭以外に配置する場合、文字種の制約が原因となってヒント句が無視される場合があります。
SQL文でテーブル名に別名が付いている場合の扱い
テーブル名に別名が付いている場合、以下「正しい例」のようにヒント句内では別名を使う必要があります。
#正しい例 |
「誤った例」のように元テーブル名を使うと無効なヒントとなります。
ヒント用のテーブル使用時の注意
以下の各項目にご注意ください。
・pg_hint_plan.enable_hint と pg_hint_plan.enable_hint_table を on に設定する
・ヒント用のテーブルに登録したSQL文と、実行されるSQL文がスペース・改行も含めて一字一句完全合致でないと
ヒントは適用されない
・テーブルに登録するSQL文に定数があるときは「?」に置き換える
#正しい例 |
「誤った例」のように定数をそのままにすると、完全一致でもヒントは適用されません。
関連ページ
Advancedサポートサービスのご紹介
PostgreSQL保守Advancedサポートサービスでは、PostgreSQL技術者による24時間365日体制のサポートのもと、PostgreSQLの技術的な問い合わせや、障害調査、pg_repackを含む指定のOSSツールのサポートに対応します。
詳細はこちらをご覧ください。
その他周辺ツールの活用例
お問い合わせ