🚀
意図的なindex only scanでクエリを爆速にした話
環境
- PostgreSQL（バージョン11以降）
概要
-
covering indexを作り、意図的にindex only scanを起こしたら爆速になった
背景
- 数万レコードの駆動表に対して、数億レコード程度の内部表のJOINが400msくらいかかってしまっていた
- 色々試して、これが限界かなと思ってた（奮闘の記事も書くかも）
- 駆動表と内部表入れ替え
- パーティショニング（LIST, HASH）→HASHで各パーティション数十万レコードくらい
- indexの調整（複合index）
- パラメータチューニング
- pg_prewarm
- etc...
- 色々試して、これが限界かなと思ってた（奮闘の記事も書くかも）
- JOINの条件には複合indexを張っているが、SELECTするカラムは結合条件に含まれていない
- テーブルアクセスが発生してしまっている←【仮説】これを無くしたら早くなりそう
やったこと
-
covering indexの作成
create index if not exists t_idx on t (c_1, c_2) include (c_3);
- すぐさまEXPLAIN！
- 狙い通りJOIN Condら辺にいんでっくおんりーすきゃんを発見
爆速かはDBサーバのスペックなどによるのでなんともという感じだが、100〜200ms程度にできた。
簡単、便利！
ここでがっつり詳細書いても対象の環境次第で要件が合わないと思うので、今回はうまく行きました〜という話だけにしておく。
注意点
covering indexを使える（価値がある）条件を知っておくこと。
- 対象テーブルの更新頻度が低い
- そもそもDBエンジンがindex only scanに対応している
- ぽすぐれの場合、btreeとgistで使える
- 対象列が少ない
- 対称列が多いとそもそもインデックスが肥大化してしまい意味がない
- ちなみにindexの効果を期待したい時は全体レコードに対して、クエリ結果が30%以内でないとテーブルスキャンの方が早いケースがあります。index張ってるから早くなるは間違い
- 対称列が多いとそもそもインデックスが肥大化してしまい意味がない
これからやりたいこと
- PostgreSQLのメジャーバージョンアップ 🍡
- DB（テーブル）リファクタリング 🧼
参考
さいごに
今回帰宅中の電車の中でスマホから書いたので書き殴りではありますが、誰かの参考にならば幸いです。
ちなみにテーブルのクラスタ化も試してみましたが、今回はほぼ効果がなかったので不採用としました。
なんとか100msを切りたい。
Discussion