SQLプランナの過信
time_of_report に インデックスつけてあるから大丈夫だろうと思っていたら、Seq. Scan になっていたSQL です。
SELECT id FROM reports
WHERE user_id = 12345
AND date_trunc('day', time_of_report) = current_date;
WHERE句でdate_trunc 関数：timestampの丸めを使ったせいで、インデックスを使ってくれなかった。
それはそうですね。関数一般を適用した結果は、値の順序が変わってしまう＝index上の位置が変わってしまう、可能性があるので。
対策：
- 性能の欲しいWHERE句で、高頻度でその関数を使うなら、インデックスも関数値で作る
USING btree(date_trunc('day', time_of_report)) - date_trunc() のように、元の値のindex順でも十分に絞込可能な関数は、SQLプランナの替わりに人間が評価式を置き換える
time_of_report >= current_date AND time_of_report < current_date + interval '1days'
pg_stats_reporter や postgres log で SlowQuery を見つけたら、インデックスが意図通り使われているかどうか、explain で確認してください。
早すぎるインデックス作成
インデックスは必要悪です。スペースは食うし、更新コストは排他制御含めると1本で2倍,2本で3倍といった調子で上昇するし、たまに 再作成しないとゴミがたまって保守工数までとられる始末です。できればつけたくない。適切にスコープ管理された要件で、シンプルに正規化・分割したTABLE設計で、各TABLEの主キーだけで間に合わせたい。それが理想です。
前項の２つの対策なら、クエリの方をインデックスに合わせる方がいいです。緊急に性能改善する必要に迫られたら、date_trunc()関数インデックスを作る方が早いときもありますが。
全部のSQLを予め確認しようとしたり、初回のコードレビューの段階でexplainするのはやめましょう。チェックしきれるものではありません。無理にやるのは「早すぎる最適化」アンチパターンです。
無駄に工数を消費するだけでなく、運用DBに実際には役に立たないインデックスを抱え込むリスクがあります。
みんなに使ってもらえるはずの機能がそっぽ向かれて、リリース予定からドロップしたとか、あるTABLEの成長が遅かったとか、完全に止まったとか、こっちのクエリにはがんがんリクエストが来るのに、ちょっとひねったこっちのクエリはさっぱり来ないとか、あるでしょう？
シナリオを想定できてそれなりの量のテストデータを用意した性能検証段階や、サービスインしてデータが貯まって、Queryの利用頻度の実測値を得てからが、インデックスチューニングです。
運用で作成済みのインデックスをみて、後から「えー、これっているのかな。効いてないんじゃない？」と思っても、実証するのは大変です。
- 性能検証環境がサービス実需を全部シミュレートできているでしょうか？
- 運用環境を pg_stats などでモニタしていても、月一度、四半期に一度の低頻度 batch処理 まで、モニタ範囲に入っているでしょうか？
漏れがあったら DROP INDEX した瞬間、あるいはDROP INDEXしたことをすっかり忘れた頃になってから、Database Blackout（DB Server応答無し） のリスクがあります。
10人年単位で数えるコード量をかかえて、担当者が代替わりしまったサービスだと、「漏れなく事前チェック」は工数的に現実味がありません。
では、リスクを承知で蛮勇を奮いますか？ 「現状で動いてるものを触るな」原則が優先します。
必要悪のインデックス が、こうして増え続けます。混沌との戦いに勝ちはありません。いつまでその場に踏みとどまっていられるかの後退遅滞戦闘です。せめて後退するときは必要性を実測確認してからにしましょうよ。
1本ですむところに2本のインデックス
「SQLプランナの過信」で言及した件は、btree(time_of_report) と btree(date_trunc('day', time_of_report)) の 2本ができかねいところを、クエリの方を調整することで1本にできる例でした。
インデックス本数を減らすのには、2キーインデックス 、3キーインデックスの活用の手もあります。
たいがいのサービスで、user_id が真っ先に必要になります。他人の情報を見せる必要はない、見せてはならないのですから。
btree(user_id) が各TABLE で必要になりそうですが、「早すぎるインデックス作成」しないでもう一歩待ちましょう。
各TABLEで固有の、user_id の次に重要なキー、そのTABLEのクエリで高頻度で使用するキーも発見できるのでは？
あったら btree(user_id, when) のように、2キーインデックスにして、btree(user_id)は作らずにすませましょう。
- when だけで絞り込むのは意味がない。必ず user_id も指定される
- user_id だけで絞り込むこともある。
このように絞込のキー評価順が固まっているなら、2キーインデックスは、1キーインデックス と 2キーインデックス を兼用できるので1本で2倍の使いでがあります。
3キーなら3本兼用ですが、4キー以上だとちょっとあやしい。4キーで、SQLプランナが判断を間違って、Seq.Scan になった事件をみたことがあります。
運用中の既存インデックスの削除は現実には無理という話をしましたが、こういうキー定義の重なりがあるときの小さい方は削除可能なはずです。それでも、びびってDROP INDEXのリターンキーはなかなか押せないです。