SnowflakeのSQLチューニングヒント

はじめに

　DBManiaです。
　突然ですが、みなさんはSnowflakeのパフォーマンスチューニングは行われていますか？
　パフォーマンスチューニングはWarehouseのスケールアップとクエリーアクセラレーターで十分という方もいらっしゃるかも知れません。
　しかし、実はSnowflakeは少しSQLのチューニングをしただけで2時間かかるクエリーが30秒で終わるようになることもあります。
　今回はREVISIOで実践しているSQLパフォーマンスチューニング方法を公開したいと思います。

SQLチューニングに必須なこと。

　みなさんは小学生のころに、相手の気持ちになって考えましょうと言われたことはないでしょうか。
　社会人になってかはら、お客様の気持ちになって考えましょうと言われた方もいるのではないでしょうか。
　SQLチューニングも同じです。
　オプティマイザーの気持ちになって考えれば、何をどうチューニングすればいいのかわかります。
　ただ、オプティマイザーの気持ちになるのは人類には難しいので、とりあえずはオプティマイザーを作った人の気持ちになって考えてみましょう。
　自分がオプティマイザーを作るとしたら、どういう計算をさせるのかという視点でSQLを見ると、今まで見えてこなかったものが見えてきます。

まずは基本を抑えましょう。

　オプティマイザーを作るためには、DBがどういった構成をしていて、どんな情報を取得できるのかを知る必要があります。
　そのため、あらためてSnowflakeが実行計画を立てるためにどのような情報を持っているのかと、一般的なDBのSQLチューニングも併せておさらいします。

Snowflakeが保持している情報

　Snowflakeはテーブルデータを行で分割し、マイクロパーティションというオブジェクトに格納します。
　マイクロパーティションが保持しているデータはこちらに書いてあります。

  • マイクロパーティションの各カラムの値の範囲。
    　あるカラムに対する最小値と最大値です。
  • 個別の値の数。
    　これはカラムの値xがn個あるという意味ではなく、そのカラムの値が何種類あるか（count(distinct カラム)）らしいです。以後種類数と書きます。
  • 最適化と効率的なクエリ処理の両方に使用される追加のプロパティ。
    　こちらについては不明ですが、Snowflakeの実際の動作を見ると、総行数くらいなのではないかと思います。

　たとえば、1が9999個,99が1個というカラムは、最小値は1、最大値は99、種類数は2種類となりますし、1～98が各1個、99が9902個なら種類数は99種類となります。

フェッチするべき表

　プログラムにおいては1,000,000と1,000のデータをループでマッチングさせる場合、1～100,000のループに対して1～1,000のループでマッチングしても1～1,000のループに対して1,000,000のループでマッチングしても、平均探索時間は1,000,000(1,000/2)=500,000,000、1,000(1,000,000/2)=500,000,000で変わりません。
　そのため、ループの順番は気にする必要がないのですが、DBで大量データを扱う場合、ループ順によって実行速度が大きく変わります。
　なぜなら、データを保持するメモリーに上限があるからです。
　特にデータ量が大きい場合、一番データ量の多いテーブルを1行ずつフェッチし、その1行ごとに小さいデータ量のテーブルをマッチングするほうが（小さいテーブルのほうがメモリーに乗せられるデータ量が多いため）キャッシュが効くため速くなります。
　逆に双方が全データメモリに載るくらい小さい場合は、データ量の大きいほうをハッシュ化して小さいほうをフェッチしたほうが（理論上は）速くなりますが、インメモリーで処理できる場合、さほど大きな差はないようです。

Snowflakeのパフォーマンスチューニング

　これらを踏まえた上で、実際のチューニングを考えます。
　上述のとおり、REVISOのSnowflakeチューニング理論はSnowflake社が公開した情報ではなく、あくまでオプティマイザーの気持ちになって考えたブラックボックス的知見です。（余談ですが、Snowflake社にオプティマイズに関して問い合わせると、「heuristicです」という回答が返ってきます。）
　ただし、今まで色々なクエリーに対して多数の試行錯誤を繰り返し、実際に適用したときの効果から、恐らく大きくは外れていないと思います。

　Snowflakeでクエリーが遅い場合の理由は大きく2つあります。

  • フェッチする表の選択ミス
  • 絞り込みの選択ミス

　どちらもオプティマイザーの計算ミスによるものですが、それはクエリーを投げる側がオプティマイザーの気持ちに寄り添っておらず、オプティマイザーにわかりやすい情報を与えていないせいでもあります。
　オプティマイザーにわかりやすい情報を与えてあげれば、Snowflakeのクエリーは劇的に速くなります。

フェッチする表の選択ミス

　Snowflakeでは、テーブルに対して抽出条件にヒットするデータがどのくらいあるのかの計算は恐らく以下の方法で行っています。

  1. MIN<=検索対象の値<=MAXのマイクロパーティションをピックアップ
  2. 1つの値あたり、行数/種類数（つまり平均数）のデータがあるとみなす。

　1,000行データの入ったマイクロパーティションで種類数が2種類なら、たとえ実際の比率は1:999であってもデータは1:1で500行ずつあると考えます。
　そのため、実際の行数より多い行数を見積もったり、少ない行数を見積もったりし、結果として一番データ量の多いテーブルをフェッチせずに、データ量の少ないテーブルをフェッチしてしまうため、速度が遅くなってしまうことがあります。
　よって、オプティマイザーに一番データ量の多いテーブルを認識してもらおうと思うなら、見積もりの行数と実際の行数を一致させればいいということになります。
　具体的には、CREATE TEMP TABLEでWHERE条件に合致するデータだけを集めたテーブルを作ってから他のテーブルと結合します。（これを私は密化と呼んでいます。）
　このとき、SELECT句にORDER BY句を入れられるなら入れるとさらに良いですが、CREATE TEMP TABLE作成にコストがかかってしまう可能性もあるため、注意が必要です。
　ナチュラルクラスタリングなりクラスタリングなりの順番でorder byをかけるならコストは高くなりませんので、それを基準にするといいでしょう。
　この方法で実行計画が大きく変わり、2時間かかっていたクエリーが30秒に縮まったケースもあります。
　注意点として、CREATE TEMP TABLEはTRANSACITONをCOMMITします。
　そのため、こちらの手法は劇的効果が見込める代わりにTRANSACITONシビアなクエリーに対しては適用できません。
　余談ですが、with句やFROM句内のサブクエリーではオプティマイザーによる最適化が実行されるため、同様の効果は得られません。

絞り込みの選択ミス

　オプティマイザーの役割は、最適に近い実行計画を立てて、可能な限り高速に結果を返すというのもありますが、それよりも重要なことは、複雑なSQLを間違えずに結果を出すことです。
　そのためには、特に検索条件を過不足なく適用する必要があります。
　ここで、不足なく適用するはわかると思いますが、過剰にならないように適用するというのはわかりにくいと思いますので、説明します。
　たとえばA BETWEEN 4 AND 7とA BETWEEN 1 AND 6という条件があったとします。
　これを律儀に2回に分けて適用するのではなく、A BETWEEN 4 AND 6に変更するというのが過剰にならないように適用するの意味です。
　そして、どうもSnowflakeのオプティマイザーは"過剰にならないように"の部分が強すぎるようです。

　どこにでもあるような以下のSQLを元に説明します。

SELECT
    A.TARGET_DATE,
    A.COL_1,
    B.COL_A
FROM
    A
INNER JOIN
    B
ON
    A.TARGET_DATE = B.TARGET_DATE
AND
    A.TARGET_DATE BETWEEN '2024-01-01' AND '2024-01-31'
AND
    B.TARGET_DATE BETWEEN '2024-01-01' AND '2024-01-31'
;

　このSQL、恐らくAテーブルもBテーブルも巨大なテーブルで、ナチュラルかどうかはわかりませんが、ある程度TARGET_DATE順に並んでいるのでしょう。
　そのため、JOINキーによって同一となっているA.TARGET_DATEとB.TARGET_DATEの両方に同一の絞り込み条件を入れています。
　では、オプティマイザーの気持ちになって実行計画を立ててみましょう。
　オプティマイザーとしては、たとえばA.TARGET_DATE BETWEEN '2023-12-01' AND '2024-01-31'とB.TARGET_DATE BETWEEN '2023-01-01' AND '2024-02-29'という条件を指定されたときに、間違えずにTARGET_DATE BETWEEN '2024-01-01' AND '2024-01-31'に集約するほうが重要ですよね。
　ですから、A,TARGET_DATE BETWEEN '2024-01-01' AND '2024-01-31'とB,TARGET_DATE BETWEEN '2024-01-01' AND '2024-01-31'のどちらかは過剰な絞り込みとして取り除きます。
　では、どちらを取り除くべきでしょうか。
　ここでSQLをよく見ると、A.TARGET_DATEがSELECTされています。
　確率的には、SELECTされたカラムでGROUP BYやORDER BYがなされていることが多いため、A.TARGET_DATEのほうが重要とみなし、こちらに条件をかけましょう。

　これがSnowflakeのオプティマイザーの最大の問題点、「SELECTされる側だけを優先して絞り込む現象（SELECTされない側に条件をかけ忘れる現象）」です。（ただし、この現象は必ず発生するわけではありません。）（発生条件が判明しましたので追記しました。）
　これが発生すると、本来は条件によってプルーニングされて少量となるはずだったテーブルが上手く絞り込まれずに大量なデータとなり、結果メモリーに乗り切らずにスピルアウトしたりすることがあります。
　クエリープロファイルを見たとき、絞りこんでほしいテーブルに絞り込み条件(Filter）が設定されず、別テーブルの結果だけでフィルターされている(join filter)のは、これが原因です。
　この場合、実際に絞り込みたいテーブルのカラムをSELECT句に入れると絞り込みたい方のテーブルにも絞り込み条件が入り速くなります。（たとえばサンプルのSQLの場合、A.TARGET_DATEではなくB.TARGET_DATEをSELECTするという意味です。）
　この方法で10分かかっていたクエリーが3秒に縮まったケースもあります。

2024/05/16 追記

　本件に関して、Snowflake社にて調査いただき、正確な理由が判明いたしましたので追記いたします。（頂いた解析結果を私が読み解いたものですので、多少の齟齬があるかも知れません。）
　ブログを書いてすぐにSnowflake社より連絡があり、こちらが実際のテーブルとクエリープロファイルを提供したところ、その日のうちに調査結果が返ってきました。
　Snowflake社のサポート体制の凄さを感じました。
　オプティマイザーがTARGET_DATE BETWEEN '2024-01-01' AND '2024-01-31'という条件でマイクロパーティションを絞った（プルーニングした）とき、本来であれば、その条件外のデータもマイクロパーティションに入ってきます。（TARGET_DATEでクラスタリングされていれば、絞り込まれた最初のパーティションは'2023-12-30'～'2024-01-02'のように、同じマイクロパーティションに過去のデータを含んでいる、という意味です。）
　そのため、オプティマイザーはプルーニング後にもTARGET_DATE BETWEEN '2024-01-01' AND '2024-01-31'という条件を判定する必要があります。
　しかし、REVISIOの場合は夜間バッチにて1日分のデータを追加するという処理を行っており、マイクロパーティションが綺麗に日別で割れる傾向があります。（つまり、'2024-01-01'を含むマイクロパーティションには'2024-01-01'のデータしかない、ということが多くなります。）
　TARGET_DATE BETWEEN '2024-01-01' AND '2024-01-31'という条件でマイクロパーティションをプルーニングすると、'2024-01-01'以前や'2024-01-31'以降のデータが1件もない、綺麗な状態でプルーニングされるため、オプティマイザーはプルーニングだけでTARGET_DATE BETWEEN '2024-01-01' AND '2024-01-31'という条件を満たせると判断して、この条件を余分な条件として取り除きます。
　結果、もう一方のテーブルに対して条件を適用せずにフルスキャンしてしまいます。
　SELECTするカラムを変えた場合、Aテーブルより先にBテーブルに対するプルーニングを実行する計画に変わり、Bテーブルのマイクロパーティションが綺麗にTARGET_DATEで割れていない場合、TARGET_DATE BETWEEN '2024-01-01' AND '2024-01-31'の条件を残す必要が出るため、Aテーブルに対してもTARGET_DATE BETWEEN '2024-01-01' AND '2024-01-31'の条件が適用されます。
　つまり、理由は異なりましたが、検索条件が片方のテーブルに適用されていない場合、SELECT句のカラムを変えると速くなる（可能性がある）という点に変わりはありません。
　また、通常マイクロパーティションが綺麗に日ごとに割れるというケースは少ないため、REVISIOのような処理を行っているケースにおいて発生するレア現象のようです。
　本件はSnowflake社にとっても想定される動作ではないと思いますので、いずれ修正されるのではないかと思います。

まとめ

  • 結合前に条件付きのCREATE TEMP TABLEで余分なデータを除去しておくと劇的に速くなることがあるが、CREATE TEMP TABLEはCREATE前にTRANSACTIONをCOMMITするので注意する。
  • 等価結合されたカラムに対する絞り込み条件は片側にしか適用されないことがあるため、適用してほしい側のテーブルカラムをSELECTすると両方に適用されて速くなることがある。（追記により修正）
  • 一般にDBのチューニングはオプティマイザー（を作った人）の気持ちを考えると上手く行く。

　Snowflakeの進化速度は凄いので、このノウハウも1年後には不要になっているかも知れませんね。