pg_hint_plan 1.3

名前

pg_hint_plan -- 実行計画を示すヒントをクエリに指定することで、SQL文やGUCパラメータを変えずに実行計画を制御します。

概要

PostgreSQLのプランナははコストベースでのオプティマイズを行なっており、SQL文と統計情報を元に可能な実行計画のコストを見積もり、最もコストの低い実行計画を選択します。プランナは可能な限りよい実行計画を作成しようとしますが、例えばカラム間の相関関係などは考慮しないため、複雑なクエリでは常に最適なプランを選択するとは限りません。

pg_hint_planを用いると、ヒントでスキャン方式や結合方式を指定することで、SQL文やGUCパラメータを変更することなく実行計画を制御することができます。

機能説明

pg_hint_planの機能について説明するにあたり、まず文中で使用されている用語について説明します。

用語説明
ヒント句 実行計画を制御するための情報です。
ヒント 実行計画を制御したいクエリに適用するヒント句を列挙したものです。

ヒントの指定方法

ヒントは二つの方法で指定することができます。

  • コメントでの指定

    • 特殊なSQLブロックコメント内にヒントを記述します。

  • テーブルでの指定

    • ヒント用のテーブルにヒントを登録します。

特定のアプリケーションではヒントをコメントで指定することができないため、「テーブルでの指定」でヒントを指定します。なお、「コメントでの指定」と異なり、アプリケーションのソースコードに手を入れずに指定するヒントを変更することができます。

コメントでの指定

指定したいヒントを、実行計画を制御したいクエリの先頭または途中のSQLブロックコメントの中に記述します。

ヒント用コメントと通常のコメントを区別するために、ヒント用のブロックコメントは「/*+」で始めます。ヒントの対象は、カッコ内にオブジェクト名または別名で指定します。オブジェクト名は、スペース、タブ、または改行のいずれかで区切って指定します。

以下の例では、HashJoinとSeqScanヒント句により、pgbench_accountsテーブルに対するSeq Scanの結果をHash Joinする実行計画が選択されています。なおかつ、Setヒント句によりこのクエリの実行計画を作成する間だけrandom_page_costが2.0に変更されています。

postgres=# EXPLAIN (VERBOSE, COSTS)
postgres-# /*+
postgres*#     SeqScan(a)
postgres*#     HashJoin(a b)
postgres*#     Set(random_page_cost 2.0)
postgres*#  */
postgres-# SELECT *
postgres-#   FROM pgbench_accounts a
postgres-#   JOIN pgbench_branches b
postgres-#     ON a.bid = b.bid
postgres-#  ORDER BY a.aid
postgres-#  LIMIT 10;
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Limit  (cost=6176.99..6177.01 rows=10 width=461)
   Output: a.aid, a.bid, a.abalance, a.filler, b.bid, b.bbalance, b.filler, a.aid
   ->  Sort  (cost=6176.99..6426.99 rows=100000 width=461)
         Output: a.aid, a.bid, a.abalance, a.filler, b.bid, b.bbalance, b.filler, a.aid
         Sort Key: a.aid
         ->  Hash Join  (cost=1.02..4016.02 rows=100000 width=461)
               Output: a.aid, a.bid, a.abalance, a.filler, b.bid, b.bbalance, b.filler, a.aid
               Hash Cond: (a.bid = b.bid)
               ->  Seq Scan on public.pgbench_accounts a  (cost=0.00..2640.00 rows=100000 width=97)
                     Output: a.aid, a.bid, a.abalance, a.filler
               ->  Hash  (cost=1.01..1.01 rows=1 width=364)
                     Output: b.bid, b.bbalance, b.filler
                     ->  Seq Scan on public.pgbench_branches b  (cost=0.00..1.01 rows=1 width=364)
                           Output: b.bid, b.bbalance, b.filler
(14 rows)

postgres=#

テーブルでの指定

指定したいヒントを、実行計画を制御したいクエリと併せてヒント用のテーブルに登録します。

デフォルトでは無効なので、テーブルで指定したヒントは適用されません。そのため、ヒントをテーブルで指定する場合は、pg_hint_planのGUCパラメータpg_hint_plan.enable_hint_tableの設定を変更します。

ヒント用のテーブルは「hint_plan.hints」です。hint_plan.hintsテーブルには、以下の情報を登録します。

列名説明
id ユーザがヒントを識別するための番号です。連番型ですので、新しいヒントを登録するときにはこの列を指定しないでください。
norm_query_string 実行計画を制御したいクエリを指定します。対象のクエリに定数があるときは、下記の例のように「?」に置き換えます。キーワード間の空白の数が、登録するクエリと実行するクエリで異なると別のSQL文として扱われます。
application_name ヒント適用対象のアプリケーション名を指定します。下記の例では「psql」から実行されたクエリのみがヒント適用対象となります。全てのアプリケーションにヒントを適用したいときは、空文字列を登録します。アプリケーション名は、セッションの「application_name」GUCパラメータと等しいか判断します。
hints ヒントを指定します。SQLコメント記号を除いた内容のみを登録します。

ヒントの登録情報を変更する場合は、変更したい登録情報のidを指定して登録情報を更新してください。 ヒントの登録を解除する場合は、解除したい登録情報のidを指定してテーブルから削除してください。

以下の例では、ヒントの登録、ヒント登録情報の変更、ヒントの解除の順に、クエリの実行結果を示しています。 

postgres=# INSERT INTO hint_plan.hints(norm_query_string, application_name, hints)
postgres-#     VALUES (
postgres(#         'EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?;',
postgres(#         '',
postgres(#         'SeqScan(t1)'
postgres(#     );
INSERT 0 1
postgres=# UPDATE hint_plan.hints
postgres-#    SET hints = 'IndexScan(t1)'
postgres-#  WHERE id = 1;
UPDATE 1
postgres=# DELETE FROM hint_plan.hints
postgres-#  WHERE id = 1;
DELETE 1
postgres=#

なお、ヒントの登録や変更や解除はスーパーユーザでのみ可能です。一般ユーザでヒントの登録や変更や解除を実施したい場合は、スーパーユーザから一般ユーザにhint_plan.hintsテーブルの各権限を付与してください。

以下の例では、hint_plan.hintsテーブルに対してINSERT権限を持たない一般ユーザがヒントの登録を実施しようとしたときの結果を示しています。

postgres=> INSERT INTO hint_plan.hints(norm_query_string, application_name, hints)
postgres->     VALUES (
postgres(>         'EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?;',
postgres(>         '',
postgres(>         'SeqScan(t1)'
postgres(>     );
ERROR:  permission denied for relation hints
postgres=>

以下の例では、テーブルに登録した「コメントでの指定」の例と同じヒントと、登録したクエリの実行結果を示しています。

postgres=# SELECT * FROM hint_plan.hints;
 id |     norm_query_string      | application_name |                      hints
----+----------------------------+------------------+--------------------------------------------------
  1 | EXPLAIN (VERBOSE, COSTS)  +| psql             | SeqScan(a)HashJoin(a b)Set(random_page_cost 2.0)
    |  SELECT *                 +|                  |
    |    FROM pgbench_accounts a+|                  |
    |    JOIN pgbench_branches b+|                  |
    |      ON a.bid = b.bid     +|                  |
    |   ORDER BY a.aid          +|                  |
    |   LIMIT ?;                +|                  |
    |                            |                  |
...

postgres=# SET pg_hint_plan.enable_hint_table TO on;
postgres=# EXPLAIN (VERBOSE, COSTS)
postgres-# SELECT *
postgres-#   FROM pgbench_accounts a
postgres-#   JOIN pgbench_branches b
postgres-#     ON a.bid = b.bid
postgres-#  ORDER BY a.aid
postgres-#  LIMIT 10;
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Limit  (cost=6176.99..6177.01 rows=10 width=461)
   Output: a.aid, a.bid, a.abalance, a.filler, b.bid, b.bbalance, b.filler, a.aid
   ->  Sort  (cost=6176.99..6426.99 rows=100000 width=461)
         Output: a.aid, a.bid, a.abalance, a.filler, b.bid, b.bbalance, b.filler, a.aid
         Sort Key: a.aid
         ->  Hash Join  (cost=1.02..4016.02 rows=100000 width=461)
               Output: a.aid, a.bid, a.abalance, a.filler, b.bid, b.bbalance, b.filler, a.aid
               Hash Cond: (a.bid = b.bid)
               ->  Seq Scan on public.pgbench_accounts a  (cost=0.00..2640.00 rows=100000 width=97)
                     Output: a.aid, a.bid, a.abalance, a.filler
               ->  Hash  (cost=1.01..1.01 rows=1 width=364)
                     Output: b.bid, b.bbalance, b.filler
                     ->  Seq Scan on public.pgbench_branches b  (cost=0.00..1.01 rows=1 width=364)
                           Output: b.bid, b.bbalance, b.filler
(14 rows)

postgres=#
指定方法の優先度

ヒントをコメントとテーブルの両方で指定した場合、テーブルで指定したヒントが適用され、コメントで指定したヒントは無視されます。

以下の例では、コメントでヒントを指定して実行計画を制御しているクエリに対して、テーブルに空文字列のヒントを登録しています。テーブルで指定したヒントが優先されるので、コメントで指定したヒントを取り消すことができます。

postgres=# select * from hint_plan.hints;
 id |             norm_query_string              | application_name | hints
----+--------------------------------------------+------------------+-------
  1 | EXPLAIN (VERBOSE, COSTS)                  +| psql             |
    | /*+                                       +|                  |
    |     HashJoin(a b)                         +|                  |
    |     SeqScan(a)                            +|                  |
    |  */                                       +|                  |
    | SELECT *                                  +|                  |
    |   FROM pgbench_accounts a                 +|                  |
    |   JOIN pgbench_branches b ON a.bid = b.bid+|                  |
    |  ORDER BY a.aid;                           |                  |
...

postgres=# EXPLAIN (VERBOSE, COSTS)
postgres-# /*+
postgres*#     HashJoin(a b)
postgres*#     SeqScan(a)
postgres*#  */
postgres-# SELECT *
postgres-#   FROM pgbench_accounts a
postgres-#   JOIN pgbench_branches b ON a.bid = b.bid
postgres-#  ORDER BY a.aid;
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..5750.47 rows=100000 width=461)
   Output: a.aid, a.bid, a.abalance, a.filler, b.bid, b.bbalance, b.filler, a.aid
   Join Filter: (a.bid = b.bid)
   ->  Index Scan using pgbench_accounts_pkey on public.pgbench_accounts a  (cost=0.00..4249.45 rows=100000 width=97)
         Output: a.aid, a.bid, a.abalance, a.filler
   ->  Materialize  (cost=0.00..1.01 rows=1 width=364)
         Output: b.bid, b.bbalance, b.filler
         ->  Seq Scan on public.pgbench_branches b  (cost=0.00..1.01 rows=1 width=364)
               Output: b.bid, b.bbalance, b.filler
(9 rows)

ヒント句のグループ

pg_hint_planで使えるヒント句の種類は、スキャン方式と結合方式、結合順序、見積もり件数補正、並列実行の設定、GUCパラメータの6グループです。各グループの具体的なヒント句は、ヒント句一覧を参照してください。

スキャン方式

あるオブジェクトでどのスキャン方式を選択するかを指定できるヒント句のグループです。「SeqScan」や「IndexScan」などを含みます。

スキャン方式を指定できるオブジェクトは、通常のテーブル、継承テーブル、UNLOGGEDテーブル、一時テーブル、システムカタログです。スキャン方式を指定できないオブジェクトは、外部テーブル、テーブル関数、VALUESコマンド結果、CTE(共通テーブル式)、ビュー、副問い合わせ結果です。

特定のオブジェクトについてあるスキャン方式を選択して欲しい場合は、そのスキャン方式のヒント句と、対象となるオブジェクトの名前を指定します。逆に、特定のオブジェクトについてあるスキャン方式を選択して欲しくない場合は、Noで始まるヒント句を指定します。同じオブジェクトに対して複数のスキャン方式のヒント句を指定した場合は、最後に指定したヒント句が適用されます。

結合方式

あるオブジェクトの組み合わせでどの結合方式を選択するかを指定できるヒント句のグループです。「MergeJoin」や「NestLoop」などを含みます。

結合方式を指定できるオブジェクトは、通常のテーブル、継承テーブル、UNLOGGEDテーブル、一時テーブル、外部テーブル、システムカタログ、テーブル関数、VALUESコマンド結果、CTE(共通テーブル式)です。結合方式を指定できないオブジェクトは、ビュー、副問い合わせ結果です。

特定のオブジェクトの組み合わせについてある結合方式を選択して欲しい場合は、その結合方式のヒント句と、対象となる2つ以上のオブジェクトの名前を指定します。逆に、特定のオブジェクトの組み合わせについてある結合方式を選択して欲しくない場合は、Noで始まるヒント句を指定します。同じオブジェクトの組み合わせに対して複数の結合方式のヒント句を指定した場合は、最後に指定したヒント句が適用されます。

結合順序

あるオブジェクトの組み合わせでどのような順番で結合するかを指定できるヒント句のグループです。「Leading」のみを含みます。

結合順序を指定できるオブジェクトは結合方式と同じです。

先に結合して欲しいオブジェクトから順にオブジェクト名または別名を指定します。同じ問合せブロックのオブジェクトに対して複数の結合順序のヒント句を指定した場合は、最後に指定したヒント句が適用されます。

結合対象のテーブルが3つ以上ある場合、結合方式のヒント句を指定したとしてもコスト見積もりによっては対象のテーブルが直接結合されないことがあります。対象のテーブルが直接結合されない場合は、結合順序のヒント句を併せて指定します。

以下の例では、table1とtable2を直接結合する場合はNested Loopを、table1とtable2とtable3を結合する場合はMerge Joinを指定しています。また、コスト見積もりによってはtable1とtable2が直接結合されない場合を避けるため、table1とtable2を結合してからtable3を結合するようにLeadingヒント句を併用しています。

postgres=# /*+
postgres*#     NestLoop(t1 t2)
postgres*#     MergeJoin(t1 t2 t3)
postgres*#     Leading(t1 t2 t3)
postgres*#  */
postgres-# SELECT * FROM table1 t1
postgres-#     JOIN table table2 t2 ON (t1.key = t2.key)
postgres-#     JOIN table table3 t3 ON (t2.key = t3.key);
...

結合方向

上記の結合順序の指定を行なった際にはプランナの都合により結合方向(外部表/内部表もしくは駆動表/被駆動表の別)が期待とは異なるものになる場合があります。このような状況に対して結合方向を固定したい場合はもうひとつの書式を使う必要があります。

postgres=# /*+ Leading((t1 (t2 t3))) */ SELECT...

この書式では2つの要素を丸括弧で囲ったものがネストする形になっており、一つの括弧内では1つ目の要素が外部/駆動表、2番めの要素が内部/被駆動表として結合されます。

見積もり件数補正

あるオブジェクトの結合結果の件数を補正できるヒント句のグループです。「Rows」のみを含みます。

見積もり件数補正対象として指定できるオブジェクトは結合方式と同じです。補正できるのは結合結果の見積もり件数だけで、スキャンの見積もり件数を補正することはできません。

以下の例では、テーブルaとテーブルbの結合結果の件数を、ヒント句で指定した値に補正しています。

postgres=# /*+ Rows(a b #10) */
postgres-# EXPLAIN SELECT *
postgres-#    FROM pgbench_branches b
postgres-#    JOIN pgbench_accounts a ON b.bid = a.bid
postgres-#   WHERE aid < 20
postgres-#   ORDER BY a.aid;
                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.29..9.92 rows=10 width=461)
   Join Filter: (b.bid = a.bid)
   ->  Index Scan using pgbench_accounts_pkey on pgbench_accounts a  (cost=0.29. .8.62 rows=19 width=97)
         Index Cond: (aid < 20)
   ->  Materialize  (cost=0.00..1.01 rows=1 width=364)
         ->  Seq Scan on pgbench_branches b  (cost=0.00..1.01 rows=1 width=364)
(6 行)

並列実行の指定

スキャンの並列実行の方法を指定します。最初のパラメータは対象とするオブジェクトの指定で、2番目に指定する数だけ並列処理ワーカを起動するように指定します。

3番目のパラメータは強制の程度を指定します。 "soft" は max_parallel_workers_per_gather のみを変更して適用はプランナに任せます。"hard" は他のプランナ変数も変更するなどしてワーカ数を強制します。

以下の例では、テーブル c1 とテーブル c2 の結合で各々異なるワーカ数を強制します。

postgres=# explain /*+ Parallel(c1 3 hard) Parallel(c2 5 hard) */
                  select c2.a from c1 join c2 on (c1.a = c2.a);
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Hash Join  (cost=2.86..11406.38 rows=101 width=4)
   Hash Cond: (c1.a = c2.a)
   ->  Gather  (cost=0.00..7652.13 rows=1000101 width=4)
         Workers Planned: 3
         ->  Parallel Seq Scan on c1  (cost=0.00..7652.13 rows=322613 width=4)
   ->  Hash  (cost=1.59..1.59 rows=101 width=4)
         ->  Gather  (cost=0.00..1.59 rows=101 width=4)
               Workers Planned: 5
               ->  Parallel Seq Scan on c2  (cost=0.00..1.59 rows=59 width=4)

GUCパラメータ

そのクエリの実行計画を作成している間だけGUCパラメータを変更できるヒント句のグループです。「Set」のみを含みます。

設定したいGUCパラメータとそのパラメータの値を指定します。指定できるGUCパラメータは問い合わせ計画のGUCパラメータのみです。同じGUCパラメータのヒント句を2回以上指定した場合は、最後に指定したヒント句が適用されます。

Setヒント句の制限

Setヒント句に問い合わせ計画のGUCパラメータ以外を指定した場合の動作は保証できません。問い合わせ計画以外のGUCパラメータを指定した場合の例の一つとして、pg_hint_planのGUCパラメータを指定した場合の動作を、以下に示します。

  • pg_hint_plan.enable_hint、pg_hint_plan.enable_hint_tableをSetヒント句の対象に指定した場合は、無効になります。なお、pg_hint_plan.debug_printをonに設定していると、そのヒント句はログ出力で「適用されたヒント句」として出力されます。
  • pg_hint_plan.debug_printをSetヒント句の対象に指定した場合は、指定したとおりに適用されます。
  • pg_hint_plan.parse_messagesをSetヒント句の対象に指定した場合は、構文エラーと一部のSetヒント句のエラーのメッセージはクエリ開始時の設定レベルで出力され、それ以外のメッセージはSetヒント句で指定したレベルで出力されます。

pg_hint_planのGUCパラメータ

pg_hint_planの動作を制御するGUCパラメータを以下に記述します。

GUCパラメータ説明デフォルト値
pg_hint_plan.enable_hint pg_hint_planの機能を有効または無効にします。on
pg_hint_plan.enable_hint_table ヒントをテーブルで指定する機能を有効または無効にします。off
pg_hint_plan.parse_messages 指定したヒントを解釈できなかった場合のログメッセージのレベルを指定します。有効な値は、debug5、debug4、debug3、debug2、debug1、log、info、notice、warning、またはerrorです。INFO
pg_hint_plan.debug_print 動作状況を示すログメッセージの出力を制御します。指定可能な値は off, on, verbose, detailed です。off
pg_hint_plan.message_level 動作ログメッセージのログレベルを指定します。有効な値は、debug5、debug4、debug3、debug2、debug1、log、info、notice、warning、またはerrorです。LOG

インストール

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はPostgreSQLの拡張(EXTENSION)を使用しているので、pg_hint_planを利用するデータベースにスーパーユーザもしくはそのデータベースの所有者で接続してCREATE EXTENSIONコマンドを実行します。

以下にデータベースへの登録の例を示します。 dbnameは対象となるデータベース名を意味します。

$ psql -d dbname -c "CREATE EXTENSION pg_hint_plan"

pg_hint_planのロード

特定のセッションでのみpg_hint_planを使う場合は、以下の例のようにpg_hint_planの共有ライブラリをLOADコマンドでロードします。一般ユーザで利用する場合は$libdir/pluginsにもインストールする必要があるので注意してください。 

postgres=# LOAD 'pg_hint_plan';
LOAD
postgres=#

全てのセッションでpg_hint_planを有効にするには、shared_preload_libraries GUCパラメータに'pg_hint_plan'を追加してからサーバを再起動します。

注意: pg_hint_planをデータベースに登録せずに、ロード後にSQL文を実行すると以下に示す例のようなエラーとなります。 pg_hint_planを使うときは、データベースへの登録を忘れないように注意してください。

postgres=# EXPLAIN SELECT * FROM pgbench_accounts a WHERE aid = 1;
ERROR:  schema "hint_plan" does not exist
LINE 1: SELECT hints   FROM hint_plan.hints  WHERE norm_query_string...
                            ^
QUERY:  SELECT hints   FROM hint_plan.hints  WHERE norm_query_string = $1    AND ( application_name = $2     OR application_name = '' )  ORDER BY application_name DESC
postgres=#

アンインストール

pg_hint_planをアンインストールするときは、以下の手順を実行します。 dbnameは対象となるデータベース名を意味します。

  1. pg_hint_planのソースを展開したディレクトリでmake uninstallを実行します。make uninstallはPostgreSQLをインストールしたOSユーザで実行します。
    2. $ cd pg_hint_plan-1.0.0
$ su
# make uninstall
  2. pg_hint_planを登録したデータベースにスーパーユーザもしくはそのデータベースの所有者で接続して、DROP EXTENSIONコマンドおよびDROP SCHEMAコマンドを実行します。
    3. $ psql -d dbname -c "DROP EXTENSION pg_hint_plan"
$ psql -d dbname -c "DROP SCHEMA hint_plan"

ヒントの指定に関する詳細

以下にいくつかのトピックに関してヒントの動作について説明します。

コメント指定でのヒント記述位置と文法
ヒントはクエリの先頭または途中に記述できます。ただし、ヒントをクエリの途中に記述する場合、ヒント用のブロックコメントより前に以下の文字以外が含まれると、「/*+」で始まるコメントでもヒントと見なされず無視されます。
  • 空白文字（半角スペース、水平/垂直タブ、改行、フォームフィード、復帰）
  • アルファベット（大文字/小文字）
  • 数字
  • アンダースコア
  • カンマ
  • 開き括弧（(）、閉じ括弧（)）

指定したヒントが無視される例を以下に示します。

  1. 二重引用符（"）がヒント用のブロックコメントより前に含まれている場合
    2. postgres=# SELECT bid AS "BID" 
postgres-# /*+
postgres*#     SeqScan(b)
postgres*#  */
postgres-#   FROM pgbench_branches b;
...
  2. 演算子（>=）がヒント用のブロックコメントより前に含まれている場合
    3. postgres=# WITH avg_aid AS (
postgres(#    SELECT avg(aid) FROM pgbench_history h
postgres(#     WHERE delta >= 0
postgres(# )
postgres-# /*+
postgres*#     SeqScan(h)
postgres*#  */
postgres-# SELECT * FROM avg_aid;
...
一つのクエリに複数のブロックコメントを記述する場合は、最初のブロックコメントにのみヒントを記述してください。二番目以降のブロックコメントは、ヒントと見なされず無視されます。以下の例では、HashJoin(a b)とSeqScan(a)がヒントと見なされ、IndexScan(a)とMergeJoin(a b)は無視されています。 
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=#
PL/pgSQL中の個別のクエリに対するヒント
ヒントは、PL/pgSQLブロック中の各クエリにも指定できます。ただし、以下のような制約があります。
  • 指定したヒントが適用されるSQLは以下の通りです。他のSQLにヒントを指定しても適用されません。
    • 1行の結果を返す問い合わせ文(SELECT/INSERT/UPDATE/DELETE)
    • 複数行の結果を返す問い合わせ文(RETURN QUERY)
    • 動的SQL文(EXECUTE)
    • カーソルを開く文(OPEN)
    • 問い合わせの結果を用いる繰り返し文(FOR)
  • ヒントをコメントで指定する場合は、SELECTなどのSQLキーワードより後に指定してください。ただし、動的SQL文にヒントをコメントで指定しても、そのヒントは適用されません。

以下の例では、一つ目のクエリにNoIndexScanを、二つ目のクエリにSeqScanをそれぞれ指定しています。

postgres=# CREATE FUNCTION hints_func(integer) RETURNS integer AS $$
postgres$# DECLARE
postgres$#     id  integer;
postgres$#     cnt integer;
postgres$# BEGIN
postgres$#     SELECT /*+ NoIndexScan(a) */ aid
postgres$#         INTO id FROM pgbench_accounts a WHERE aid = $1;
postgres$#     SELECT /*+ SeqScan(a) */ count(*)
postgres$#         INTO cnt FROM pgbench_accounts a;
postgres$#     RETURN id + cnt;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
ヒント句内のオブジェクト名の文字ケース
PostgreSQL は引用符で囲われないオブジェクト名を文字ケースを無視して扱いますが、pg_hint_plan は指定されたオブジェクト名の文字ケースはそのまま PostgreSQL の内部表現と比較します。つまり、ヒント句で TBL と指定した場合、データベース上で "TBL" と定義したもののみと合致し, TBL, tbl, Tbl など引用符で囲われないオブジェクト名とは合致しません。
ヒント句内のオブジェクト名の引用符付け
ヒントに記述するオブジェクト名や別名が括弧（(、)のいずれか）、二重引用符（"）、空白（スペース、タブ、改行のいずれか）を含む場合は、通常のSQL文で使う場合と同じように二重引用符(")で囲んでください。二重引用符を含むオブジェクト名は、全体を二重引用符で括ったうえで、内部に含む二重引用符を二重引用符でエスケープしてください(例: 「quoted"table"name」→「"quoted""table""name"」)。
同一名称テーブルの区別
スキーマ違いや同一テーブルの複数回使用などでクエリ中に同一名称のテーブルが複数回出現する場合は、テーブルに別名をつけてそれぞれのテーブルを区別してください。以下の例の1つ目のSQL文では、HashJoin(t1 t1)をヒントに指定したとき、ヒント句対象のオブジェクトが特定できずにエラーになっています。2つ目のSQL文では、各テーブルにptやstという別名をつけているため、実行計画作成時にヒントで指定した通りにHash Joinを選択しています。 
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=#
ビューおよびルールの中でアクセスされているテーブル
ヒント句で指定した名称と一致すれば、ビュー定義や関数内クエリなどに出現するテーブルについても、ヒントを指定したクエリ内と同じようにヒント句が適用されます。このため、ヒント句の適用有無や適用するヒント句をそれぞれのテーブルで変えたい場合は、それぞれ異なる別名を指定してください。 以下の例では、ビュー定義で使われている「t1」という別名をSeqScanヒント句で指定したことで、表スキャンとビュー経由のスキャンの両方でSeq Scanが選択されています。ビュー定義で使用されている「t1」とは別の別名を実表に指定することで、個別にスキャン方式を制御できます。 
postgres=# CREATE VIEW view1 AS SELECT * FROM table1 t1;
CREATE TABLE
postgres=# /*+ SeqScan(t1) */
postgres=# EXPLAIN SELECT * FROM table1 t1 JOIN view1 t2 ON (t1.key = t2.key) WHERE t2.key = 1;
                           QUERY PLAN
-----------------------------------------------------------------
 Nested Loop  (cost=0.00..358.01 rows=1 width=16)
   ->  Seq Scan on table1 t1  (cost=0.00..179.00 rows=1 width=8)
         Filter: (key = 1)
   ->  Seq Scan on table1 t1  (cost=0.00..179.00 rows=1 width=8)
         Filter: (key = 1)
継承テーブル
継承テーブルにスキャン方式のヒント句を指定する場合は、オブジェクト名として親テーブルの名称または別名を指定してください。全ての子テーブルに同じスキャン方式を選択します。子テーブルごとに別のスキャン方式を指定することはできません。
マルチステートメント
クエリをマルチステートメントで実行する場合は、ヒントの指定方法によって以下のように注意点が異なります。
コメントでの指定
1つ目のクエリで指定したヒントが全てのクエリで使用されます。2つ目以降のクエリに指定したヒントは無視されます。
テーブルでの指定
ヒントを指定したいクエリの定数部分を？に置き換えてから、全てのクエリを一つにまとめてヒント用テーブルに登録します。ただし、複数の動的クエリをマルチステートメントで実行する場合には対応していません。
FROM句にVALUES式を使っている問い合わせ
FROM句にVALUES式を使っている場合は、ヒント句のオブジェクト名に「*VALUES*」を指定することでヒントを与えることができる場合があります。 ただし複数のVALUESを使っている場合は内部的には区別がつかないためヒントを与えることはできません。 
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)
postgres=#
副問い合わせ結果
以下のような形式の副問い合わせでは外側の文への併合が行われず「ANY_subquery」という名前の独立した副問合せとして残される場合があります。このような副問合せが一つしかない場合は ANY_subquery という名前でこの副問合せを特定することができます。
  • IN (SELECT ... { LIMIT | OFFSET 等} ... )
  • ANY (SELECT ... { LIMIT | OFFSET 等} ...)
  • SOME (SELECT ... { LIMIT | OFFSET 等} ...)
以下の例では、そのことを利用して Hash Joinを強制しています。 
postgres=# /*+HashJoin(a1 ANY_subquery)*/
postgres=# EXPLAIN SELECT *
postgres=#    FROM pgbench_accounts a1
postgres=#   WHERE aid IN (SELECT bid FROM pgbench_accounts a2 LIMIT 10);
                                         QUERY PLAN

---------------------------------------------------------------------------------------------
 Hash Semi Join  (cost=0.49..2903.00 rows=1 width=97)
   Hash Cond: (a1.aid = a2.bid)
   ->  Seq Scan on pgbench_accounts a1  (cost=0.00..2640.00 rows=100000 width=97)
   ->  Hash  (cost=0.36..0.36 rows=10 width=4)
         ->  Limit  (cost=0.00..0.26 rows=10 width=4)
               ->  Seq Scan on pgbench_accounts a2  (cost=0.00..2640.00 rows=100000 width=4)
(6 rows)

postgres=#
IndexOnlyScanヒント句
IndexOnlyScanヒントで指定されているインデックスで index only scan が実行できない場合は、index scan を他のインデックスで実行する可能性があります。
NoIndexScanヒントの挙動について
NoIndexScanヒント句を指定した場合は、Index ScanだけでなくIndex Only Scanも選択されません。
UNION に対する並列実行ヒント
UNIONは直下のサブクエリが全て並列実行可能な場合にだけ並列実行を行います。一方ですべてのサブクエリが並列実行可能な場合は、そのうちの一つで並列実行を強制するとコスト比較の結果UNION全体が並列実行されることになります。ただし並列実行ヒントによる並列実行の禁止を行った場合はそのスキャンは並列実行不可となります。
Set ヒントでの pg_hint_plan 自身の制御変数の設定
pg_hint_plan 自身の挙動を設定する変数を Set ヒントで変更した場合は期待通りに動作しない場合があります。
  • enable_hint, enable_hint_tables の設定ヒントはログ上は「使用済み」と出ますが、実際には無視されています。
  • debug_print と message_level の変更は処理の途中から影響を与えます。

ヒントのパースエラーについて

構文エラー
ヒント句の記述に構文上の誤りがあった場合、pg_hint_planは誤った記述より前のヒント句のみ有効とし、誤った記述以降のヒント句を無視してクエリを実行します。誤りの内容はpg_hint_plan.parse_messagesで指定したレベルでサーバログに記録されます。
  • ヒント句名を間違っている。
  • オブジェクト指定を正しく括弧で囲っていない。
  • オブジェクト名を空白で区切っていない。
オブジェクト指定エラー
pg_hint_planは、ヒント句対象のオブジェクト指定に誤りがあった場合、pg_hint_planは正常に解析できたヒントのみを適用して、それ以外を無視します。誤りの内容はpg_hint_plan.parse_messagesで指定したレベルでサーバログに記録されます。
    重複したヒント
    同じオブジェクトに対して同じグループのヒント句を重複して指定した場合は、各グループで最後に指定したヒント句を使用します。
    ネストしたブロックコメント
    pg_hint_planでは、ヒントを指定したブロックコメントにネストしたブロックコメントを含めることができません。ネストしたブロックコメントを含めた場合は、誤った記述に関する情報を出力しますがエラー終了しません。ヒントを無視してクエリを実行します。
    メッセージの出力レベル
    ヒントに誤りがあった場合に出力されるメッセージのレベルは、基本的にはpg_hint_plan.parse_messagesに指定したレベルです。ただし、ヒント句に指定したオブジェクトの長さが識別子の最大長(デフォルトでは63バイト)を超えた際に切り詰めた場合は、NOTICEで出力します。

    注意事項

    プランナパラメータの影響
    FROM句の要素数が from_collapse_limit 以上または join_collapse_limit を超える場合には、結合順序のヒント句は正しく動作しません。また、FROM句の要素数が geqo_threshold 以上の場合は、結合順序のヒント句、および結合方式のヒント句は動作しません。ヒント句が使われるようにするには、これらのGUCパラメータの値を十分に大きくしてください。
    実行不能なプランを指定するヒント
    ヒントで指定した結果正しく実行できない実行計画が生成される場合は、実行可能なプランに強制的に修正します。実行不能な実行計画例を以下に示します。
    • 特殊な場合を除いた FULL OUTER JOINに対する Nested Loop。
    • WHERE句やJOIN条件などに指定していない列のみを含むインデックス。
    • 検索条件にctidを指定しない場合の Tid Scan。
    ECPGにおける制限
    ECPGで実装したアプリケーションから発行するクエリにヒントをコメントで指定した場合、実行計画を制御できません。これは、CプリプロセッサがCコードに変換するタイミングで、全てのブロックコメントを取り除いてしまうためです。ただし、EXECUTEコマンドで実行する動的SQLに含まれるコメントは使用可能です。
    異なるヒントを与えた同一クエリの pg_stat_statements での扱い
    pg_stat_statements ではクエリIDの生成の際にコメントは無視されます。そのため与えているヒントが異なる同一のクエリは一つのクエリとして集計されます。

    動作環境

    pg_hint_plan13 1.3 は PostgreSQL 13 のみをサポートします。
    PostgreSQL
    バージョン 13
    動作確認済みOS
    CentOS 8.2

    関連項目

    PostgreSQLドキュメント

    EXPLAIN SET サーバの設定 パラレルプラン

    Copyright (c) 2012-2020, NIPPON TELEGRAPH AND TELEPHONE CORPORATION