この記事はPostgresのＳＱＬチューニングの基本的な仕組みを知ってる事を前提に次のステップに行く際に読んでもらえればと思って書いています。SQLって概要でいうとそんな風に処理されるんだ～って思ってもらえれば幸いです。
PostgresでSlowQueryが発生して困った経験がある方ってかなり多いんじゃないでしょうか。
複雑じゃないSQLについてはネットで少し調べればWHERE句に書く順番、JOINのやり方を見直したりとかで大抵解決します。
また基本的な設定値の変更なんかも含めて基本の「き」かと思います。
ただ「もっと改善したい！これが本当にベストなのか！？」と追及していくと行き着く先はPostgresのプランナーなのではないでしょうか。
複雑なＤＢだったり、ユーザの利用方法が定まっていないようなシステムの場合、適切なＳＱＬを自動生成する事は非常に困難です。
そんな時最終的に合理的な判断をしてくれるのがプランナーです。
今日はそのプランナー周りを書いておこうっ
SQLの処理はこんな順番に処理されてます
みんなが書いたSQLはパーサーにより機械が理解しやすい形に変換され、
リライターにより一定ルールの書き換えが行われ、
プランナーが統計情報をもとに実行計画が立てられ、
エグゼキューターによって実行され結果が返されます。
上に書いた文字が全てなんですが、以下の説明を読んだ後にまたこの上の文字の説明を読んで気持ちがスッキリ出来て下さいね！
以下に細分化して説明します。
リライター
例えば created_at BETWEEN 昨日 AND 今 と書いてもプランナーが立てた計画をEXPLAIN ANALYZEで確認すると
created_at >= 昨日 AND created_at <= 今 という変換が行われている事がわかります。
こういった変換を行うのがリライターです。チューニングには直接的な関係はおそらくないですが、知っていて損はないですね。
プランナー
こいつが難しい。まず統計情報ってなんですか？から始まります。
深追いするとこの勉強だけに何時間も費やしますので簡単に説明しますね。
統計情報とは？
定期的に自動バキュームが走ってるのはご存知ですかね。とある設定値を基準として満たした場合にデータベースの空箱を掃除したり、データ量やデータの偏り具合を調べたりしています。このデータ量や偏りの調査結果がまさしく統計情報というものになります。
実行計画とは？
統計情報がわかればそんな複雑な話ではないです。
つまりプランナーは「こんな風にデータ偏ってるんだったら、この条件に該当するデータは少ないはず。ならここはBitmap Index Scanが良さそうだな！」とか考えてるわけですね。
当然プランナーは「データ数の多さや偏り具合に応じてスキャン方法は何が妥当か？」とか考えるプロフェッショナルです。
つまり、SQLが求めてる内容を返すためにどの方法でスキャンしてマージして絞って並び替えたらコストが一番低いのか？を色々なパターンを検討して１つの最適解を実行計画として導き出すわけです。すごいぞプランナー！
どうすればプランナーが良い仕事をするのか
プランナーの仕事を理解できたら次はここですね。
結論からいうと以下です。
- 良質な統計情報を準備する事
- 良質な実行計画を考えさせてあげる事
書き出すと長くなるので省略しますが、統計情報であればサンプリング数がものを言います。
デフォルトではサンプルとなるレコード数は３万レコードが対象となっており、最大３００万レコードまで設定変更が可能です。
default_statistics_targetとかで調べてみると良いかもしれません。
実行計画なら例えば、join_collapse_limitとかでしょうか。
SQLの実行計画を考える際に結合処理の順番をプランナーが考えるわけですが、その計算コストも決して安くはありません。
この設定値は結合順を考えるのに何個のテーブルまでを対象とするのかの上限値になります。
これを上げる事で実行計画を考える時間は伸びるものもあるが、良質な実行計画になる可能性も秘めています。
条件次第ではありますが、デフォルト値の８より高い値にするのも良いかもしれませんね！
今日はここまでっドロン（殴り書きで失礼しました）