2018年 10月 の投稿一覧

1. pg_hint_planとは

pg_hint_plan は PostgreSQL のクエリの実行計画を制御するためのモジュールです。本モジュールにより、他のリレーショナルデータベースで一般的な機能であるヒント句を PostgreSQL で利用することが可能になります。ヒント句とは実行されるクエリの実行計画の決定をプランナに任せきりにせず、プランナに実行計画を直接指示することを可能とする機能です。

PostgreSQL ではクエリの実行計画はプランナが統計情報を用いて決定します。プランナのバグや使用する統計情報が正確でないことで不適切な実行計画が作成されたり、
刻々と変化する統計情報に依存する実行計画によってクエリの実行性能が不安定になる場合があります。pg_hint_plan を用いることで、プランナの不適切な実行計画を改善したり、統計情報に依存する不安定な実行計画を安定化することが可能になります。

2018年 10月時点では、PostgreSQL10.x のみに対応した 1.3.1 がリリースされています。そのほかも PostgreSQL バージョン 9.1 から 9.6 まで、それぞれに対応するパッケージを提供しています。
PostgreSQL 9.0.x 以前のバージョンに対応していないのは PostgreSQL 側にヒント句を追加できる枠組みが備わっていないためです。

2. 本文書の環境

本文書は、PostgreSQL 9.6.2 と pg_hint_plan 1.2.0 を使って、pg_hint_plan の Linux 上での使い方を解説します。

3. 導入方法

本節では CentOS 7.2 で pg_hint_plan 1.2.0 を導入する例を示します。PostgreSQL 9.6 が RPM でインストールされているものとします。

◆ インストール

pg_hint_plan のダウンロードページから pg_hint_plan96-1.2.0-1.el7.x86_64.rpm をダウンロードします。ダウンロード後はローカルファイルに対して yum や rpm コマンドでインストールを実行します。

# yum install pg_hint_plan96-1.2.0-1.el7.x86_64.rpm
もしくは
# rpm -ivh pg_hint_plan96-1.2.0-1.el7.x86_64.rpm

◆ データベースへの登録

pg_hint_plan モジュールを利用するには利用先データベースへの登録が必要になります。

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

データベースの登録が済むと次のオブジェクトが作成されます。

• hint_plan スキーマ
• hint_plan.hints テーブル
• hint_plan.hints_pkey インデックス
• hint_plan.hints_norm_and_app インデックス

◆ セッションへのロード

セッションにおいて pg_hint_plan を利用するには予めロードが必要になります。特定のセッションでのみ利用する場合は LOAD コマンドを実行してください。

db1=# LOAD ‘pg_hint_plan’;
LOAD

全セッションで利用する場合は PostgreSQL の 設定ファイル postgresql.conf の shared_preload_libraries パラメータに ‘pg_hint_plan’ を追加してください。

shared_preload_libraries = 'pg_hint_plan'

4. ヒントの使用

ヒントはクエリにコメントの形で与えます。ヒント用のコメントは通常のコメントと区別するために /*+ で始めるようにして、「/*+ ヒント */ クエリ」という書式で記述します。複数のヒントを指定する場合には空白文字で区切って並べます。

以下表に示すカテゴリのヒントが利用可能です。使用できるヒント一覧はマニュアルに記載があります。

<利用可能なヒントのカテゴリ>

ヒント句一覧
http://pghintplan.osdn.jp/hint_list-ja.html
（上記ページは本文書作成時点では v1.1 の内容です）

◆ 実行例

ここから実際にヒントを与えて実行計画を変更する例を示します。この例ではヒントを指定することでプランナの計画した不適切な実行計画よりも適切な実行計画を指定しています。

本検証例のサンプルデータは以下の通り pgbench コマンドで作成しています。

$ pgbench -U postgres -i -s 10 db1
creating tables...
...
$ psql db1
db1=# VACUUM ANALYZE;
VACUUM

pgnemch コマンドで作成された pgbench_accounts テーブル、pgbench_branches テーブルに対して結合とソート操作を行うクエリの実行計画を検証します。

<ヒントを指定しない場合>

db1=# EXPLAIN ANALYZE
        SELECT * FROM pgbench_branches b
          JOIN pgbench_accounts a ON b.bid = a.bid ORDER BY a.aid;
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.42..192378.55 rows=1000000 width=465) (actual time=0.033..2147.035 rows=1000000 loops=1)
   Join Filter: (b.bid = a.bid)
   Rows Removed by Join Filter: 9000000
   ->  Index Scan using pgbench_accounts_pkey on pgbench_accounts a  (cost=0.42..42377.43 rows=1000000 width=97) (actual time=0.025..210.312 rows=1000000 lo
ops=1)
   ->  Materialize  (cost=0.00..1.15 rows=10 width=364) (actual time=0.000..0.001 rows=10 loops=1000000)
         ->  Seq Scan on pgbench_branches b  (cost=0.00..1.10 rows=10 width=364) (actual time=0.003..0.005 rows=10 loops=1)
 Planning time: 0.222 ms
 Execution time: 2219.069 ms
(8 行)

ヒントを指定しない場合は Index Scan, Nested Loop が計画されました。
これに対して Seq Scan, Hash Join を計画するようにヒント指定してみます。SeqScan(a) はテーブル a をシーケンシャルスキャンすること、HashJoin(a b) はテーブル a , b をハッシュジョインすることを意味します。ヒント指定においては、クエリ内でテーブル名に別名が付けられている場合、ヒント指定では別名を使う点に注意してください。

<ヒントを指定した場合>

db1=# /*+ HashJoin(a b) SeqScan(a) */
      EXPLAIN ANALYZE
        SELECT * FROM pgbench_branches b
          JOIN pgbench_accounts a ON b.bid = a.bid ORDER BY a.aid;
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=563632.07..566132.07 rows=1000000 width=465) (actual time=942.519..1081.707 rows=1000000 loops=1)
   Sort Key: a.aid
   Sort Method: external sort  Disk: 119264kB
   ->  Hash Join  (cost=1.23..40145.22 rows=1000000 width=465) (actual time=0.038..395.556 rows=1000000 loops=1)
         Hash Cond: (a.bid = b.bid)
         ->  Seq Scan on pgbench_accounts a  (cost=0.00..26394.00 rows=1000000 width=97) (actual time=0.020..103.247 rows=1000000 loops=1)
         ->  Hash  (cost=1.10..1.10 rows=10 width=364) (actual time=0.010..0.010 rows=10 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 9kB
               ->  Seq Scan on pgbench_branches b  (cost=0.00..1.10 rows=10 width=364) (actual time=0.003..0.007 rows=10 loops=1)
 Planning time: 0.134 ms
 Execution time: 1201.389 ms
(11 行)

ヒントにより、指定にしたがったプラン実行をしていることが確認できました。

◆ ヒントを予め登録する

ヒントの与え方はクエリにコメントとして直接指定する方式の他に、ヒント用のテーブル hint_plan.hints に実行計画を制御したいクエリとヒントを登録しておく方式があります。テーブルでのヒントの指定を有効にするには、設定パラメータで pg_hint_plan_enable_hint_table = on と指定されている必要があります。

先ほどのクエリにテーブルでヒントを指定してみます。

まず、hint_plan.hints テーブルに指示対象のクエリ、アプリケーション名、ヒントを登録します。このとき、アプリケーション名に ’’（空文字列）を登録することで、どの名称のアプリケーションにも適用されます。また、ここでは実行計画検証のため、EXPLAIN ANALYZE を含めたクエリを登録していますが、実際に動作させるクエリでは除いて登録してください。

db1=# INSERT INTO hint_plan.hints
        (norm_query_string, application_name, hints)                                                                                                                                               
        VALUES (
          'EXPLAIN ANALYZE
           SELECT * FROM pgbench_branches b
           JOIN pgbench_accounts a ON b.bid = a.bid
           ORDER BY a.aid;',                                                                                                                                                                       
          '', 'HashJoin(a b c) SeqScan(a)');
INSERT 0 1

以下の通り、コメントでヒントを指定せずともヒントが適用されていることが確認できます。

db1=# EXPLAIN ANALYZE
      SELECT * FROM pgbench_branches b
        JOIN pgbench_accounts a ON b.bid = a.bid ORDER BY a.aid;
                                                                                                 QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Sort  (cost=563632.07..566132.07 rows=1000000 width=465) (actual time=923.405..1059.397 rows=1000000 loops=1)
   Sort Key: a.aid
   Sort Method: external sort  Disk: 119264kB
   ->  Hash Join  (cost=1.23..40145.22 rows=1000000 width=465) (actual time=0.018..384.143 rows=1000000 loops=1)
         Hash Cond: (a.bid = b.bid)
         ->  Seq Scan on pgbench_accounts a  (cost=0.00..26394.00 rows=1000000 width=97) (actual time=0.007..96.927 rows=1000000 loops=1)
         ->  Hash  (cost=1.10..1.10 rows=10 width=364) (actual time=0.006..0.006 rows=10 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 9kB
               ->  Seq Scan on pgbench_branches b  (cost=0.00..1.10 rows=10 width=364) (actual time=0.001..0.003 rows=10 loops=1)
 Planning time: 0.225 ms
 Execution time: 1177.613 ms
(11 行)

テーブルでヒントを指定するときは以下の点に注意してください。

• 指示対象クエリ末尾のセミコロン(;)の有無も一致する必要がある
• 指示対象クエリ中の定数は ? で指定する必要がある
• 「コメントでの指定」と「テーブルでの指定」の両方で指定した場合、「テーブルでの指定」が優先される

◆ Parallelヒント

もう一つ例を示します。pg_hint_plan 1.2.0 から PostgreSQL 9.6 新機能のパラレルクエリに対応しています。Parallel (テーブル名 ワーカ数 [強制度]) というヒントを指定することでクエリをパラレルクエリとして実行させることが可能です。

引き続き、pgbench で生成したテーブルを使って例を示します。

<ヒントを指定しない場合>

db1=# EXPLAIN ANALYZE  SELECT * FROM pgbench_accounts;
                                                                                                 QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Seq Scan on pgbench_accounts  (cost=0.00..26394.00 rows=1000000 width=97) (actual time=0.023..96.409 rows=1000000 loops=1)
 Planning time: 0.071 ms
 Execution time: 155.825 ms
(3 行)

以下の Parallel ヒントは、pgbench_accounts テーブルをワーカ数8で実行させるように指定していますが、実際はワーカ数3で実行されています。これは Parallel ヒントの強制度はデフォルトで soft 指定であり、max_parallel_workers_per_gather のみを変更してあとはプランナに任せるので、必ずしもワーカ数は指定した値で実行されるとは限りません。

<強制度 soft で Parallel ヒント指定する場合>

db1=# /*+ Parallel(pgbench_accounts 8) */
      EXPLAIN ANALYZE  SELECT * FROM pgbench_accounts;
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..20619.81 rows=1000000 width=97) (actual time=0.152..358.230 rows=1000000 loops=1)
   Workers Planned: 3
   Workers Launched: 3
   ->  Parallel Seq Scan on pgbench_accounts  (cost=0.00..19619.81 rows=322581 width=97) (actual time=0.021..45.338 rows=250000 loops=4)
 Planning time: 0.106 ms
 Execution time: 430.357 ms
(6 行)

ワーカ数を強制するには強制度を hard に指定します。以下では、実際に指定したワーカ数 8 で実行されていることがわかります。

<強制度 hard で Parallel ヒント指定する場合>

b1=# /*+ Parallel(pgbench_accounts 8 hard) */
      EXPLAIN ANALYZE  SELECT * FROM pgbench_accounts;
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Gather  (cost=0.00..17644.00 rows=1000000 width=97) (actual time=0.157..347.372 rows=1000000 loops=1)
   Workers Planned: 8
   Workers Launched: 8
   ->  Parallel Seq Scan on pgbench_accounts  (cost=0.00..17644.00 rows=125000 width=97) (actual time=0.013..20.577 rows=111111 loops=9)
 Planning time: 0.105 ms
 Execution time: 435.160 ms
(6 行)

◆ ログメッセージとデバッグ出力

クエリ実行時にヒントを解釈できなかった場合、クエリ自体はヒントが与えられなかったものとして通常通り実行されます。ヒントが適正でなかったことはログに出力されます。

以下に、存在しないヒントを入力したときの例を示します。

db1=# /*+ error_hint */
      EXPLAIN ANALYZE
        SELECT * FROM pgbench_branches b
          JOIN pgbench_accounts a ON b.bid = a.bid ORDER BY a.aid;

INFO:  pg_hint_plan: hint syntax error at or near "error_hint "
DETAIL:  Unrecognized hint keyword "error_hint".

このときのメッセージレベルは pg_hint_plan.parse_messages（デフォルト info）パラメータで指定できます。

また、pg_hint_plan.debug_print = on （デフォルト off）とすることで指定したヒントの実行計画への影響をログに出力させることが可能です。指定したヒントが以下 4 つのカテゴリに分類されて報告されます。

pg_hint_plan.debug_print によるログ出力の例を示します。

LOG:  pg_hint_plan:
        used hint:
        SeqScan(a)
        HashJoin(a b)
        not used hint:
        duplication hint:
        error hint:

STATEMENT:  /*+ HashJoin(a b) SeqScan(a) */
EXPLAIN ANALYZE SELECT * FROM pgbench_branches b
  JOIN pgbench_accounts a ON b.bid = a.bid ORDER BY a.aid;

5. まとめ

本文書では pg_hint_plan について基本的な情報を取りまとめました。PostgreSQL では制御の難しい実行計画を容易に制御可能な pg_hint_plan は強力なツールといえます。
しかしながら、ヒントを利用するかどうかは慎重な検討してください。PostgreSQL のプランナは基本的には賢くかつ網羅的であり、コスト計算が適切であれば様々な実行計画の中から一番適切な実行計画が選択されるはずです。ヒントを指定しても正しい保証は無く、ある状況で適切に思えても他の多くの状況で不適切な計画を実行してしまう危険もはらんでいます。

◆ 関連サイトリンク

• pg_hint_plan プロジェクトページ
  https://ja.osdn.net/projects/pghintplan/
• pg_hint_plan ドキュメント
  http://pghintplan.osdn.jp/pg_hint_plan-ja.html
  (2018 年 10 月現在、最新バージョンの 1.2 および 1.3 ドキュメントは web 公開されておりませんが、
  パッケージ内には含まれています)
• pg_hint_plan ソースコードリポジトリ
  https://github.com/ossc-db/pg_hint_plan