pg_hint_planは、元のSQL文を変えずに実行計画を制御するためのツールです。
pg_hint_planを用いると、ヒントを記述したブロックコメントをSQL文の前に加えることで、実行計画を制御することができます。
pg_hint_planのインストール方法について説明します。
pg_hint_planをソースコードからビルドする場合、pg_hint_planのソースを展開したディレクトリでmake → make installの順に実行してください。make installはPostgreSQLをインストールしたOSユーザで実行してください。なお、pg_hint_planのビルドにはpgxsを使用するので、RPM版のPostgreSQLを使用している環境では、postgresql-devel パッケージが必要です。
以下にビルドの例を示します。
$ tar xzvf pg_hint_plan-1.0.0.tar.gz $ cd pg_hint_plan-1.0.0 $ make $ su # make install
pg_hint_planをアンインストールするには、pg_hint_planのソースを展開したディレクトリでmake uninstallを実行してください。make uninstallはPostgreSQLをインストールしたOSユーザで実行してください。
以下にアンインストールの例を示します。
$ cd pg_hint_plan-1.0.0 $ su # make uninstall
pg_hint_planの使い方について説明します。
pg_hint_planを使うには、以下の例のようにpg_hint_planの共有ライブラリをロードしてください。全てのセッションでpg_hint_planを有効にするには、shared_preload_libraries GUCパラメータに'pg_hint_plan'を追加してからサーバを再起動して下さい。
postgres=# LOAD 'pg_hint_plan'; LOAD postgres=#
ヒントはクエリ文字列の先頭のブロックコメント内に記述してください。ブロックコメントをヒントとして認識させるには、ブロックコメントの開始直後にプラス（+）を指定する必要があります。ヒントの対象は、カッコ内にオブジェクト名または別名(エイリアス)で指定してください。オブジェクト名は、スペース、タブ、または改行のいずれかで区切って指定してください。
以下の例では、HashJoinとSeqScanヒントにより、pgbench_accountsテーブルに対するSeq Scanの結果をHash Joinする実行計画が選択されています。
postgres=# /*+ postgres*# HashJoin(a b) postgres*# SeqScan(a) postgres*# */ postgres-# EXPLAIN SELECT * postgres-# FROM pgbench_branches b postgres-# JOIN pgbench_accounts a ON b.bid = a.bid postgres-# 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) postgres=#
pg_hint_planで使えるヒントは、スキャン方式と結合方式、結合順序、GUCパラメータの4グループに分けられます。各グループの具体的なヒントは、ヒント一覧を参照してください。
あるオブジェクトでどのスキャン方式を選択するかを指定できるヒントのグループで、「SeqScan」や「IndexScan」などが含まれます。
スキャン方式を指定できるオブジェクトは、通常のテーブル、継承テーブル、UNLOGGEDテーブル、一時テーブル、システムカタログです。スキャン方式を指定できないオブジェクトは、外部テーブル、テーブル関数、VALUESコマンド結果、CTE、VIEW、副問い合わせ結果です。
特定のオブジェクトについてあるスキャン方式を選択して欲しい場合は、そのスキャン方式のヒントと、対象となるオブジェクトの名前を指定してください。逆に、特定のオブジェクトについてあるスキャン方式を選択して欲しくない場合は、Noで始まるヒントを指定してください。同じオブジェクトに対して複数のスキャン方式のヒントを指定した場合は、最後に指定したヒントが適用されます。
あるオブジェクトの組み合わせでどの結合方式を選択するかを指定できるヒントのグループで、「MergeJoin」や「NestLoop」などが含まれます。
結合方式を指定できるオブジェクトは、通常のテーブル、継承テーブル、UNLOGGEDテーブル、一時テーブル、外部テーブル、システムカタログ、テーブル関数、VALUESコマンド結果、CTEです。結合方式を指定できないオブジェクトは、VIEW、副問い合わせ結果です。
特定のオブジェクトの組み合わせについてある結合方式を選択して欲しい場合は、その結合方式のヒントと、対象となる2つ以上のオブジェクトの名前を指定してください。逆に、特定のオブジェクトの組み合わせについてある結合方式を選択して欲しくない場合は、Noで始まるヒントを指定してください。同じオブジェクトの組み合わせに対して複数の結合方式のヒントを指定した場合は、最後に指定したヒントが適用されます。
あるオブジェクトの組み合わせでどのような順番で結合するかを指定できるヒントのグループで、「Leading」のみが含まれます。
結合順序を指定できるオブジェクトは結合方式と同じです。
先に結合して欲しいオブジェクトから順にオブジェクト名または別名を指定してください。複数の結合順序のヒントを指定した場合は、最後に指定したヒントが適用されます。クエリ中に複数の問い合わせブロックがあり、それぞれに結合順を指定したい場合は、それぞれの結合順を1つのLeadingヒントに連続して指定してください。
そのクエリの実行計画を作成している間だけGUCパラメータを変更できるヒントのグループで、「Set」のみが含まれます。
設定したいGUCパラメータとそのパラメータの値を指定してください。SETコマンドで指定できるGUCパラメータならば全て指定できますが、効果があるのは問い合わせ計画のGUCパラメータのみです。同じGUCパラメータに対して複数のGUCパラメータのヒントを指定した場合は、最後に指定したヒントが適用されます。
Setヒントにpg_hint_planのGUCパラメータを指定することはできますが、期待通りの動作をしないため、指定しないことをおすすめします。指定した場合の実際の動作は、使用上の注意と制約を参照してください。
pg_hint_planの動作を制御するGUCパラメータを以下に記述します。
| GUCパラメータ | 説明 | デフォルト値 |
|---|---|---|
| pg_hint_plan.enable | pg_hint_planの機能を有効または無効にします。 | on |
| pg_hint_plan.debug_print | pg_hint_planのデバッグ出力を有効にします。メッセージはLOGメッセージレベルで出力されますので、デフォルトではサーバログに出力され、クライアントには渡されません。 | off |
| pg_hint_plan.parse_messages | 指定したヒントを解釈できなかった場合に、どのメッセージ階層でログを出力するかを指定します。有効な値は、debug5、debug4、debug3、debug2、debug1、log、info、notice、warning、またはerrorです。fatalとpanicは指定できません。 | info |
PostgreSQL 9.1の環境でこれらのパラメータをpostgresql.confファイルで設定するには、custom_variable_classesにpg_hint_planを加える必要があります。 典型的な使用例は以下のようになります。
# postgresql.conf shared_preload_libraries = 'pg_hint_plan' custom_variable_classes = 'pg_hint_plan' # 9.2以降は廃止されたため記述不要 pg_hint_plan.parse_messages = 'debug2'
PostgreSQL 9.2以降ではcustom_variable_classesは廃止されているため、pg_hint_planのGUCパラメータを標準のGUCパラメータと同様に記述することができます。
pg_hint_planを使用する際には、以下の注意と制約があります。
postgres=# /*+ postgres*# HashJoin(a b) postgres*# SeqScan(a) postgres*# */ postgres-# /*+ IndexScan(a) */ postgres-# EXPLAIN SELECT /*+ MergeJoin(a b) */ * postgres-# FROM pgbench_branches b postgres-# JOIN pgbench_accounts a ON b.bid = a.bid postgres-# 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) postgres=#
postgres=# /*+ HashJoin(t1 t1)*/ postgres-# EXPLAIN SELECT * FROM s1.t1 postgres-# JOIN public.t1 ON (s1.t1.id=public.t1.id); INFO: hint syntax error at or near "HashJoin(t1 t1)" DETAIL: Relation name "t1" is ambiguous. QUERY PLAN ------------------------------------------------------------------ Merge Join (cost=337.49..781.49 rows=28800 width=8) Merge Cond: (s1.t1.id = public.t1.id) -> Sort (cost=168.75..174.75 rows=2400 width=4) Sort Key: s1.t1.id -> Seq Scan on t1 (cost=0.00..34.00 rows=2400 width=4) -> Sort (cost=168.75..174.75 rows=2400 width=4) Sort Key: public.t1.id -> Seq Scan on t1 (cost=0.00..34.00 rows=2400 width=4) (8 行) postgres=# /*+ HashJoin(pt st) */ postgres-# EXPLAIN SELECT * FROM s1.t1 st postgres-# JOIN public.t1 pt ON (st.id=pt.id); QUERY PLAN --------------------------------------------------------------------- Hash Join (cost=64.00..1112.00 rows=28800 width=8) Hash Cond: (st.id = pt.id) -> Seq Scan on t1 st (cost=0.00..34.00 rows=2400 width=4) -> Hash (cost=34.00..34.00 rows=2400 width=4) -> Seq Scan on t1 pt (cost=0.00..34.00 rows=2400 width=4) (5 行) postgres=#
postgres=# /*+ MergeJoin(a *VALUES*) */ postgres-# EXPLAIN SELECT * postgres-# FROM pgbench_accounts a postgres-# JOIN (VALUES (1,1),(2,2)) v (vid, vbalance) ON a.aid = v.vid postgres-# ORDER BY a.aid; QUERY PLAN --------------------------------------------------------------------------------------------------------------- Merge Join (cost=0.04..4497.33 rows=2 width=105) Merge Cond: (a.aid = "*VALUES*".column1) -> Index Scan using pgbench_accounts_pkey on pgbench_accounts a (cost=0.00..4247.26 rows=100000 width=97) -> Sort (cost=0.04..0.04 rows=2 width=8) Sort Key: "*VALUES*".column1 -> Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2 width=8) (6 行) postgres=#