« 大人になったなと思うこと | トップページ | いまさら「f」とか「ヒ」のアイコンと格闘してみる »

2014.02.14

プランナをだまして実行計画を誘導する

だます、というと言葉が悪いけども、特定のSQLを実行した際に
「なんでそんな実行計画を組むんだ~!」
と叫びたくなることは時々あるだろう。
各DBMSにはこのあたりをある程度オプティマイザ任せにしないためのオプションを持っている。
今回紹介するのはPostgreSQLにおけるそれ。

  1. プランナメソッドの調整
  2. 明示的なJOIN構文の利用

1.プランナメソッドの調整

EXPLAINなどで実行計画をみたときに、どこでコストがかかっているのかを当然ながらチェックする。 その際にもしシーケンシャルスキャンを多用している場合はこの方法を試してみることができる。
SET ENABLE_SEQSCAN to off;
このセットを事前に行うと、次からの問合せではシーケンシャルスキャンは実行計画としては採用されなくなる。

あるいはたくさんのテーブルを結合しているときに、それぞれのテーブルの読み出しはちゃんとインデックスが使われたりしてコストも小さいのに、クエリー結果が遅いことがある。

上記のようにMerge Join処理自体に13秒もかかっているようなケースでマージJOINではなくHash JOINやNested Loop Joinをしてもらえば速くなるとわかった場合、

SET ENABLE_MERGEJOIN to off;
として実行することで、ジョインのコストがほぼ0になってしまうことすらある。

例示したのはマージジョインについてだが、その他のJOINについても禁止することができる。

SET ENABLE_HASHJOIN to off; --ハッシュJOINの禁止
SET ENABLE_BITMAPSCAN to off; --ビットマップスキャンの禁止
SET ENABLE_NESTLOOP to off; --ネステッドループジョインの禁止
上記はマージジョイン以外の代表的な3種類のジョイン方法について利用させないような設定である。

BITMAPSCANやHASHJOINについてもサーバのメモリが少ない場合などだと利用されるとスワップが発生するなどしてかえって遅くなるケースもあるので、このオプションで試してみることが可能だ。
ただし、うまくはまるとかなり効果を確認することもできる。

→ref)[プランナメソッド設定]

2.明示的なJOIN構文の利用

テーブル同士のジョインを行う順番について制御したい場合は、JOIN句内で()を使うことで優先度を変更できる。詳細は公式のドキュメントがわかりやすいと思う。

ref)[明示的なJOIN句でプランナを制御する]

このときの肝は、どのテーブル同士を先に連結すると無駄な走査がないか、ということになる。
ただし、この明示的利用をする際には、PostgreSQLのパラーメタをひとつ調整しておかないと、思ったように動作しないことがある。

join_collapse_limitという変数である。デフォルト値は「8」。

SET join_collapse_limit=1;

この値を「1」とすることで、必ず記述した順序でJOINを行うようになるため、期待通りの連結作業が可能になる反面、すべてのSQLについて結合順序を考えて生成・発行をしなければならなくなる。これは普段であれば気楽にJOIN処理だけしてオプティマイザに任せる仕事を、すべてのSQL文に意思を込めなくてはならないことを意味するので大変である。
この数値はFROM句で指定される結合対象数がこの数値を下回った場合に明示的なJOINを実行する、ということであり、SQL内の結合対象数が少ない場合はデフォルト設定だとせっかく()を使って順番を指定しても、オプティマイザは自分の知っている統計情報のほうを信頼して実行計画を作成する。

ただしいずれの場合であっても業務システムでのデータ状況は日々刻々と変わっていくため、あるタイミングにおいて最適だった実行計画が、未来永劫有効である保証は無い。

特に1番のプランナメソッドの無効化はリスクが高いため、この変更は調査もしくはワークアラウンド対応であると位置づけるべきである。
2番目の明示化については特定の処理限定で適用させることは検討に値すると思う。
幸いなことにこれらのSET処理は、プロセスごとに指定できるので、特定の処理を実施したい場合だけ値を指定する、ということも可能であろう。

|

« 大人になったなと思うこと | トップページ | いまさら「f」とか「ヒ」のアイコンと格闘してみる »

コメント

コメントを書く



(ウェブ上には掲載しません)




トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/2022/59129762

この記事へのトラックバック一覧です: プランナをだまして実行計画を誘導する:

« 大人になったなと思うこと | トップページ | いまさら「f」とか「ヒ」のアイコンと格闘してみる »