pg_hint_planで実行計画を制御する
PostgreSQLインサイド

pg_hint_planでヒント句を設定して実行計画を制御するチューニング方法について解説します。

1. 実行計画を制御する

PostgreSQLでは、プランナ（オプティマイザ）が入力されたSQL文をもとに統計情報を参照して、最も速くてコストの低いと予想される方法を選択し、実行計画を作成します。しかし、必ずしもプランナ（オプティマイザ）が最適な実行計画を作成するとは限りません。例えば、大量の更新クエリなどで統計情報の最新化が間に合わない場合や、刻々と統計情報が変化する場合に、プランナ（オプティマイザ）が作成する実行計画が不安定になることがあります。基幹系の業務システムでは、性能の向上よりも性能の安定化の方が重要であり、実行計画の変化を避けたい場合もあります。
その対策として、クエリにヒント句を設定してテーブルスキャン方法や結合方法などのアクセス方法を明示的に指示することで、プランナ（オプティマイザ）が出力する実行計画を制御することができます。それにより、プランナ（オプティマイザ）が安定した実行計画を出力するようになります。
ここでは、PostgreSQLの周辺ツールの1つであるpg_hint_planを使ったヒント句によるチューニングについて解説します。

1.1 pg_hint_planとは

pg_hint_planは、PostgreSQLにヒント句を設定することでユーザーが実行計画を制御できるツールで、Linux上、Windows上およびSolaris上で動作します。pg_hint_planで指定できるヒント句の種類は以下のとおりです。

• テーブルの問い合わせの（スキャン）方法の指定
• 複数のテーブルの結合順序の指定
• テーブルの結合方法の指定
• テーブルの結合結果の見積り件数補正
• パラレルクエリ実行の強制 / 禁止の指定
• SQL文実行時のみのPostgreSQLパラメーターの変更

参考

• pg_hint_planの仕様については、オープンソース・ソフトウェアのWebページを参照してください。
• pg_hint_planは便利な機能ですが、注意点もあります。ご利用の前には必ず「3. pg_hint_planの注意点」をお読みください。

2. pg_hint_planを使う

pg_hint_planを使い、ヒント句を設定する方法について順を追って、見ていきましょう。今回はPostgreSQL 11.1とpg_hint_plan 11.1.3.2を組み合わせた環境での検証例を説明します。

2.1 準備する

pg_hint_planを利用するには、追加モジュールを公開サイトなどから取得してインストールし、以下の準備をします。

1. PostgreSQLを起動して本機能を利用するデータベースに対して、CREATE EXTENSIONを実施します。なお、対象のデータベースは「mydb」とします。

$ psql -d mydb -c "CREATE EXTENSION pg_hint_plan;"

1. postgresql.confファイルのshared_preload_librariesパラメーターに｢pg_hint_plan｣を追加します。

shared_preload_libraries = 'pg_hint_plan'

1. PostgreSQLを再起動します。

2.2 ヒント句を設定する

インデックスのあるテーブルの検索処理に対して、ヒント句を設定してチューニングする例を説明します。なお、empテーブルおよびdeptテーブルが作成されているものとします。

1. empテーブルおよびdeptテーブルの構成を確認します。
   
   （1）empテーブルの列「empno」に「emp_pkey」というインデックス（主キー）が設定されています。
   （2）empテーブルの列「age」に「emp_age_index」というインデックスが設定されています。
   （3）deptテーブルの列「deptno」に「dept_deptno_index」というインデックス（一意性制約）が設定されています。
2. empテーブルおよびdeptテーブルのデータ件数を確認します。
   
3. ANALYZEコマンド（SQLコマンド）で統計情報を更新し、条件を指定してデータを検索します。
   
   （1）empテーブルに対して「Bitmap Scan」が選択されたことがわかります。
   （2）deptテーブルに対して「Seq Scan」が選択されたことがわかります。
   empテーブルに対する「Bitmap Scan」の処理行数は（a）です。deptテーブルに対する「Seq Scan」の処理行数は（b）です。
   インデックスを使った代表的なスキャン方法には以下があります。丸付数字はインデックスとテーブルへのアクセス順を示します。

スキャン方法	解説
Index Scan	インデックスとテーブルを交互にランダムアクセスします。WHERE句による絞り込みにより取り出す件数が少ない場合や目的のデータにピンポイントでアクセスしたい場合に有効な方法です。
Bitmap Scan	インデックスから得られた候補行をメモリー上にビットマップ化し、テーブルの候補行のみ取得します。ビットマップ化したあと、スキップしながらテーブルにシーケンシャルアクセスします。取り出す件数が中程度の場合に有効な方法です。
Index Only Scan	インデックスにだけアクセスして、そのインデックスの結果データを返します。インデックスの情報だけが必要な場合に有効な方法です。

1. ヒント句を使って実行計画を変更してみます。empテーブルのインデックス「emp_age_index」を使った「Index Scan」が選択されるように、ヒント句を設定します。ヒント句は「/*+ ~ */」の形式で指定します。
   
   （1）ヒント句の指定に従い、empテーブルに対して「Index Scan」が選択されたことがわかります。

このように、pg_hint_planを使い、意図的にPostgreSQLの動作を制御することができます。

ポイント

pg_hint_planでのヒント句の指定方法には以下があります。

• コメントでの指定：アプリケーション内で、特殊なSQLブロックコメントの中にヒントを記述します。
• テーブルでの指定：ヒント用のテーブルにヒントを登録します。アプリケーションに手を入れずにヒントを調整できます。

pg_hint_planで指定できるヒント句の種類と、代表的なヒント句の書式を示します。

• テーブルの問い合わせの（スキャン）方法の指定：SeqScan（テーブル）,IndexScan（テーブル[ インデックス...]）
• 複数のテーブルの結合順序の指定：Leading（テーブル テーブル[ テーブル...]）
• テーブルの結合方法の指定：NestLoop（テーブル テーブル[ テーブル...]）,HashJoin（テーブル テーブル[ テーブル...]）
• テーブルの結合結果の見積り件数補正：Rows（テーブル テーブル[ テーブル...] 件数補正）
• パラレルクエリ実行の強制 / 禁止の指定：Parallel（テーブル ワーカ数 [強制度]）
• SQL文実行時のみのPostgreSQLパラメーターの変更：Set（GUCパラメーター値）

SQL文中でテーブル名に別名が付けられている場合、ヒント句では別名を指定してください。

1つのクエリに複数のヒント句を指定することもできます。例を以下に示します。

• SQL文中の同じオブジェクトに対して、別々のヒント句を指定したい場合は、各オブジェクトに別名を定義し、その別名に対してヒント句を指定してください。
  

ポイント

FUJITSU Software Enterprise Postgresでは、バージョン 9.5から、pg_hint_planを同梱しています。製品のインストール時にpg_hint_planをプリインストールしているので、追加モジュールを取得する必要はありません。

3. pg_hint_planの注意点

pg_hint_planはユーザーが実行計画を制御できる便利な機能ですが、以下のような注意があります。

• テーブルのデータサイズの変更による影響
  ヒント句を設定すると実行計画が固定化されるため、テーブルのデータサイズが小さいときに最適だった実行計画が、テーブルのデータサイズが大きくなったときに最適になるとは限りません。
• SQL文変更よる影響
  アプリケーション改修などで、ヒント句が設定されたSQL文を変更するときは、再度、ヒント句の妥当性を検証し、見直す必要があります。
• PostgreSQLのメジャーバージョンアップによる影響
  以前のバージョンで使っていたヒント句を、新しいバージョンでそのまま使用すると、性能劣化の原因になる可能性があります。理由としては、PostgreSQLがメジャーバージョンアップにより、プランナ（オプティマイザ）自体が改良されることがあるためです。

pg_hint_planは業務要件に変更が発生しない単発のSQLのチューニング、実行計画がどう動くかを確認するための検証手段、または性能を安定化させる手段として役立ちます。利用するケースを見極めることで有効なツールとなります。
性能チューニングの1つの手段として、pg_hint_planの機能・用途を理解して、ご利用のシステムや業務要件に適した方法を選択してください。

2019年7月12日公開

こちらもおすすめ

• チューニング ～ データベースチューニング ～
• チューニング ～ SQLチューニングの概要 ～
• チューニング ～ SQLチューニングを実施する ～
• pg_dbms_statsで統計情報を固定化して、実行計画を制御する

本コンテンツに関するお問い合わせ