SQL　パフォーマンスチューニングの仕方

最近の現場でSQLのパフォーマンスチューニングが割とあったので
ここに備忘録として記載しておく

チューニングのやり方は基本はインデックス検索がされているかどうかだけど
何でもかんでもインデックスを付与するわけにはいかない

インデックスを付与するとDBが重くなるからだ
特にトランザクションデータ系は毎日何百万というレコードが流れるので
インデックスの付与だけでも何分も処理が遅れたりする

そのためインデックスは最終手段だと思ってほしい。

今回の手法は実行結果を最初は10分以上かかる状態から
3秒まで改善した例を記そうと思う。

DBはPostgres
商品の売り上げなどを記録するテーブルが複数あり
1000万件あるデータを抽出するようなSQL

まずやることは実行計画を取得してSeqScanの箇所を探す。
これはフルスキャンといって1行目からデータを探しているため
1000万件すべてをチェックするような検索の仕方である。
そりゃ当然遅いよね。

IndexScanであれば日付＋店舗名等で絞り込むため非常に速い。
例えばＤＢ10年分の中からデータ202201~~202301までを抽出したいとした場合
ざっくりと10年分(2015~2025)のデータを半分に分割して、該当の2022年は
前半にあるか後半にあるか？みたいな感じで半分ずつしぼっていく2分割法
そんな感じで効率よくさがしていくのがIndexScan。
この限りではないが一般的にはこのように説明されている。

さて次はどのようにしてIndexにさせていくかという点

結論から言うと、地道に色んな組み合わせにしていくしかない

Postgresの場合はなぜかIndexされない結合方法がありもうこのDBあほ
って思えるくらいしんどかった。

基本は年月や店舗コードでLeft Joinするのだが普通に結合するとフルスキャンになるので
事前にUnionしたり、UnionしたものにLeftJoinさせることで本命のJoinの部分を軽量化するイメージ。

select store_code,biz_ymd 
from A left join B left join C left join D

こんな感じで結合しまくると結合テーブルが非常に重くなりなぜかIndexがされなくなる
そのためWithによって事前に結合するとなぜか上手くいく
↓

with CD AS NOT MATERIALIZED(
    select * from C left join D
)
select store_code,biz_ymd 
from A left join B left join CD

イメージで言うとこんな感じで一度ＣＤで結合したテーブルを作成して
それを後続のＡ＋Ｂ＋ＣＤのような感じで結合するとうまくいく。
ちなみになぜだかわからないし他のＤＢだとこんな書き方しなくてもうまくいくかもしれない

NOT MATERIALIZEDを付与すると
一時テーブルを保持しないような作りになるので多少早くなるぜ

ではこのへんで