前置き
これまでは、どのSQL実行計画を選ぶかはPostgreSQLのプランナ任せでした。プランナが期待どおりの実行計画を選んでくれずに、性能が出ずに困ったという場面があったと思います。例えば、インデックスを使って検索してほしいのに、なぜかシーケンシャルスキャンが選ばれたしまったというようなケースです。pg_hint_planを使うと、PostgreSQLのプランナに対して、どのような実行計画を選ぶべきか指示(HINT)を与えることができます。つまり、どのような実行計画でSQLを実行するかユーザがほぼ思いどおりに制御できるということです。これにより、プランナが望ましくない実行計画を選んでしまう、上記の性能問題を解決することができます。
ただし、HINTは魔法の杖ではありません。OracleにもHINT機能はあり、使っている人は多いですが、HINTを使ったがために性能問題にはまるというケースをよく聞きます(具体的にどんなトラブルがあるかはまた別の日に)。PostgreSQLコミュニティもHINTは基本的にトラブルの素と考えており、PostgreSQL本体ではHINTをサポートしていません。将来的にサポートの予定も一切ありません。コミュニティのHINTに対する考え方はWiki(http://wiki.postgresql.org/wiki/OptimizerHintsDiscussion)に整理されています。pg_hint_planを使おうと考えている方は、一度このWikiページを読んで、本当にHINTが必要なのか検討するとよいと思います。
コミュニティには反対意見が多いですが、やはりチューニングの最終兵器としてHINTを使いたいというようなユーザからのアツい要望があり、pg_hint_planは開発されたという経緯になっています。HINTはトラブルの素になるかもというリスクを理解した上で、pg_hint_planを使いましょう。
インストール
前置きが長くなりましたが、以下、pg_hint_planを試してみます。pg_hint_planのサイトはhttp://en.sourceforge.jp/projects/pghintplan/です。 ここからpg_hint_planのソースのtarボールをダウンロードします。今回はPostgreSQL9.2でHINTを使うので、pg_hint_plan92-1.0.0.tar.gzをダウンロードします。
tarボールを展開します。
$ tar zxf pg_hint_plan92-1.0.0.tar.gz
$ ls pg_hint_plan92-1.0.0
COPYRIGHT core-9.1.c expected pg_hint_plan.c
COPYRIGHT.postgresql core-9.2.c input sql
Makefile data make_join_rel.c
SPECS doc output
pg_hint_planのマニュアルは、docディレクトリ内にあります。使い方や制約などが詳しく載っているので、見ておきましょう。
次にpg_hint_planをインストールします。
$ cd pg_hint_plan92-1.0.0
$ make USE_PGXS=1 PG_CONFIG=/Users/postgres/pgsql/9.2/pgsql-9.2.2/bin/pg_config
$ make USE_PGXS=1 PG_CONFIG=/Users/postgres/pgsql/9.2/pgsql-9.2.2/bin/pg_config install
USE_PGXSを使ってインストールできます。PG_CONFIGの指定は、PostgreSQLのbinディレクトリにPATHが通っているのであれば不要です。私の環境では、PATHを通していないために、PostgreSQL9.2のpg_configコマンドを指定しています。
次にpg_hint_planをロードします。postgresql.confでshared_preload_librariesにpg_hint_planを設定します。
$ emacs $PGDATA/postgresql.conf
shared_preload_libraries = 'pg_hint_plan'
これでpg_hint_planを使うための準備が整いました。PostgreSQLを再起動すれば、pg_hint_planがロードされてHINTが使えるようになります。
$ pg_ctl -D $PGDATA restart
HINTを使ってみる
HINTを使って、プランナにインデックススキャンを選ばせてみましょう。 $ psql
=# CREATE TABLE tbl (i int, j int);
=# INSERT INTO tbl SELECT x, x * 2 FROM generate_series(1, 10) x;
=# CREATE INDEX idx ON tbl (i);
=# VACUUM ANALYZE tbl;
※テーブル内のデータ件数が少ないため、単純な検索ではシーケンシャルスキャンが選ばれる
=# EXPLAIN SELECT * FROM tbl WHERE i = 7;
QUERY PLAN
---------------------------------------------------
Seq Scan on tbl (cost=0.00..1.12 rows=1 width=8)
Filter: (i = 7)
(2 rows)
※HINTでインデックススキャンを選ぶように指定
=# /*+ IndexScan(tbl) */ EXPLAIN SELECT * FROM tbl WHERE i = 7;
QUERY PLAN
---------------------------------------------------------------
Index Scan using idx on tbl (cost=0.00..8.27 rows=1 width=8)
Index Cond: (i = 7)
(2 rows)
HINTは、/*+ */のコメント内に記述します。これは、OracleのHINTに似た構文なので、Oracleユーザも取っ付きやすいかもしれません。インデックススキャンをプランナに選ばせたい場合は、検索対象のテーブル名を括弧で囲みながら、IndexScanのキーワードを指定します。括弧内で、テーブル名に続けてインデックス名を指定することで、インデックスが複数定義されていたときに、どのインデックスでインデックススキャンをしたいのかを指定することもできます。
※インデックスhogeidxを使ってインデックススキャンをするようにHINT指定
=# CREATE INDEX hogeidx ON tbl (i);
=# /*+ IndexScan(tbl hogeidx) */ EXPLAIN SELECT * FROM tbl WHERE i = 7;
QUERY PLAN
-------------------------------------------------------------------
Index Scan using hogeidx on tbl (cost=0.00..8.27 rows=1 width=8)
Index Cond: (i = 7)
(2 rows)
pg_hint_planは、OracleのHINTで特に使われることの多いものをサポートしています。例えば、以下です。
- スキャン方法の指定
- シーケンシャルスキャンの選択 → SeqScan(テーブル)
- インデックススキャンの選択 → IndexScan(テーブル[ インデックス...])
- インデックスオンリースキャンの選択 → IndexOnlyScan(テーブル[ インデックス...])
- 結合順序の指定
- テーブルの結合順序を指定 → Leading(テーブル テーブル[ テーブル...])
- 結合方法の指定
- ネストループの選択 → NestLoop(テーブル テーブル[ テーブル...])
- ハッシュ結合の選択 → HashJoin(テーブル テーブル[ テーブル...])
- マージ結合の選択 → MergeJoin(テーブル テーブル[ テーブル...])
指定できるHINTの詳細については、pg_hint_plan付属のマニュアルを参照してください。