Snowflake SQL JOIN条件の最適化と検証❄️

WRITER: 金綱 雅也
このエントリーをはてなブックマークに追加

AI事業本部AIオペレーションテクノロジーカンパニーの金綱雅也です。

本記事では、Snowflake の SQL JOIN 条件に OR を含めた場合に発生するパフォーマンス低下の課題を取り上げ、その解決策として等価結合へ正規化する手法を紹介します。

特に以下の論点を掘り下げていますので、同様の課題に直面している方の参考になれば幸いです。

  • JOIN 条件に OR を含めたときに何が起きるのか

  • COALESCE や ARRAY_CONSTRUCT_COMPACT+FLATTEN を使った最適化アプローチの効果

  • クエリチューニングにおける検証の妥当性をどう担保すべきか (キャッシュと Query Profile の読み解き方)

背景

AIオペレーションテクノロジーカンパニーは、弊社で扱う大量の広告データを Snowflake 上で適切に権限管理し、社内のさまざまな部署に対して配信・提供しています。Snowflake上では毎日10万件を超えるクエリが実行されており、大規模クエリのチューニングはパフォーマンス向上だけでなくコスト削減にも大きく寄与します。

今回は、過去2週間で最も実行時間の長かった 15分38秒のクエリ を題材にチューニングを行いました (先に結果だけお伝えすると、こちらは56秒まで短縮されます) 。

クエリ全体の処理時間が15分38秒で、Processingが75.6%、Remote disk I/Oが22.4%を占めていることを示すQuery ProfileのProfile overview

ボトルネックの特定

Snowflake では、Query Profile の Most expensive nodes からどのノードでどの程度の処理時間がかかったかが可視化されます。

このクエリでは LeftOuterJoin ノードに 69.2% (約11分) の時間が費やされていることが分かりました。

一般的に JOIN は、左右のうち小さい方のテーブルをハッシュ化することで効率的に処理されます。JOIN にこれほど時間がかかっているのは、何らかの無駄な処理が発生している可能性を示唆します。

Most expensive nodesの内訳。LeftOuterJoinノードが69.2%と最も時間を消費していることを示す

 

さらに当該のノードを詳細に確認すると、OR を含む JOIN 条件 が設定されていました。

OR を含む JOIN 条件は単一キーの等価結合として最適化できません。結果としてハッシュ結合が効かず、結合候補を広く拾ってから行ごとに条件を評価する処理になりがちです。これが LeftOuterJoin ノードがボトルネックとなっていた理由です。

Left Outer JoinのAttributes。JOIN条件にORが含まれていることが確認できる画面キャプチャ

アプローチの検討

複数の列を結合キー候補として扱いたい場合でも、OR で書かずに等価結合に正規化するのがポイントです。方法は大きく2つあります。

COALESCE

・左優先で1値に正規化する。
・正規化した1値をJOINに使う。
→ 単一キーに収束するためハッシュ結合が効く。
⚠️ 両方の列が非NULLの場合でも片方しかマッチしない。

ARRAY_CONSTRUCT_COMPACT + FLATTEN

・ARRAY_CONSTRUCT_COMPACT: 複数列を配列化し、NULLを除外。
・FLATTEN: 配列を展開するため、複数候補を等価結合として扱える。
→ OR と同じ両取りの挙動を保ちながらハッシュ結合が効く。

使い分け

ケース1: 両列が同時に非NULLになることはない(論理的に相互排他の設計)

→ COALESCE で十分。最もシンプルで速い。

ケース2: 両列が同時に非NULLになり得て、かつ双方の値が結合先に存在し得る

→ OR と同様に両取りする挙動を再現する必要があるため、ARRAY_CONSTRUCT_COMPACT + FLATTENを使う。

今回は両列が同時に非NULLになり得るデータ仕様だったため、後者を採用しました。

検証結果

実際に ARRAY_CONSTRUCT_COMPACT + FLATTEN に書き換えて検証した結果がこちらです。

JOIN 条件を書き換えただけで、処理時間を約15分短縮することができました(画像左)。

また、以前存在した LeftOuterJoin ノードのボトルネックも解消されていることが分かります(画像右)。

※USE_CACHED_RESULT を FALSE に設定し、WH は suspend 状態から起動して使用しているため、可能な限りキャッシュの影響を抑えた同条件で実行しています。

ARRAY_CONSTRUCT_COMPACTとFLATTENに書き換えた後のProfile overview。処理時間が56秒に短縮され、Remote disk I/Oが59.2%を占めていることが示されているARRAY_CONSTRUCT_COMPACTとFLATTENに書き換えた後のMost expensive nodes。TableScanが主要な割合を占め、LeftOuterJoinの負荷が大幅に低下していることを示す

検証時に考慮すべき Snowflake のキャッシュ

ここで注意したいのは、Snowflake でクエリを比較検証する際にキャッシュを完全に排除することはできないという点です。

Snowflake には大きく分けて次の3種類のキャッシュがあります。

  • 結果キャッシュ (Result Cache)
    過去に実行したクエリの結果を保持し、同じクエリを再実行すると即時で返す
    →ALTER SESSION SET USE_CACHED_RESULT = FALSE を設定することで無効化可能

  • メタデータキャッシュ (Metadata Cache)
    マイクロパーティションの統計情報 (min/max 値、null の有無など) を保持しており、パーティションプルーニングに利用される
    ⚠️サービス層で保持されるため、セッションやWHの起動・停止に依存しない

  • ウェアハウスキャッシュ (Virtual Warehouse Cache)
    ストレージから読み込んだマイクロパーティションの列データを実行中の WH のローカル SSD/メモリに保持しており、後続の処理で再利用される
    →WH を SUSPENDからRESUME することでクリア可能

Snowflake のキャッシュ構造を示す図。Cloud Services 層に Result Cache（クエリ結果を24時間保持）、Metadata Cache（コンパイル時間短縮）、Virtual Warehouse 層に Warehouse Cache（読み込んだデータをローカル SSD に格納）を持ち、基盤のデータは Amazon S3 に保存されることを示している。

なぜ「WH 再起動 & USE_CACHED_RESULT=FALSE」でも Percentage scanned from cache が高く出るのか

USE_CACHED_RESULT=FALSE は結果キャッシュだけを無効化する設定であり、WH キャッシュには影響しません。さらに、WH を SUSPEND → RESUME してローカルキャッシュをクリアしても、単一のクエリ実行の中で以下の理由により Percentage scanned from cache が高く計上され得ます。

  • クエリ内/分散実行での再参照
    1回のクエリ実行の中でも、ストレージから読み込まれた列データは即座に WH のローカルSSD/メモリに保持されます。これにより、同じテーブルに対して複数の TableScan ノードが読み込みを行ったり、並列タスク間で再利用やリトライが発生したりする場合、ストレージではなくローカルのコピーが使われ、ここでのキャッシュアクセスはすべて Percentage scanned from cache に計上されることになります (同じテーブルをスキャンするノードが複数ある場合、あるノードでは0%、別のノードでは高い値が出ることは往々にしてあり得ます) 。

  • メタデータキャッシュは常に有効
    メタデータキャッシュにより読むべき範囲自体が減るため、そもそもの I/O 対象が軽くなる (＝ストレージ I/O が減る)ことがあります。これは Percentage scanned from cache の分母に影響し、ゼロに近づけにくい要因になります。

つまり、完全な「キャッシュなし」条件で比較することは実質不可能ですので、実務上は以下のような工夫によってできる限り公平な条件に近づけます。

  1. USE_CACHED_RESULT=FALSE を設定し、結果キャッシュを無効化する
  2. 使用するWHを一度 SUSPEND → RESUME してローカルキャッシュをクリアする
  3. それでも Percentage scanned from cache が高く出る場合は、Bytes scanned やキャッシュ利用率について両者でどの程度の差があるかを確認し、比較対象として適切かどうかを判断する

妥当性評価

上記を踏まえて、検証の妥当性を評価するにあたって両者の Query Profile から得られる Statistics を参照しました。

OR使用時

Snowflake Query Profile の Statistics。OR 条件を使った場合の結果で、Bytes scanned は35.00GB、キャッシュ利用率は57.47%、Partitions scanned は20,817。

 

ARRAY_CONSTRUCT_COMPACT+FLATTEN使用時

Snowflake Query Profile の Statistics。ARRAY_CONSTRUCT_COMPACT+FLATTEN を使った場合の結果で、Bytes scanned は29.98GB、キャッシュ利用率は54.05%、Partitions scanned は17,025。

 

Bytes scanned、Percentage scanned from cache、Bytes written to resultが同程度であることから、論理的に同規模のデータに対して同程度の結果を返していることが分かります。

一方で Partitions scanned は、OR 使用時に対して ARRAY_CONSTRUCT_COMPACT + FLATTEN 使用時の方が Join Filter によるパーティションプルーニングが効き、スキャン範囲が大きく削減されています。

また、Processing の割合は CPU が条件判定や結合処理に費やした時間を示します。今回、同条件でProcessingの割合が大きく減少しており、JOIN 条件の書き換えが効果を発揮したとみなせます。

以上のことから、今回の短縮効果は CPU 負荷軽減 と I/O 削減 の両方が寄与しており、いずれも JOIN 条件を等値化したことによって Snowflake の最適化が効いた結果であり、OR 条件からの書き換え効果の検証として妥当と評価できます。

まとめ

  • JOIN 条件に OR を含めるとハッシュ結合が効かず、広い結合候補を拾ったうえで1行ずつ条件を判定する処理になるため遅くなります。
  • 複数列キーを扱うときは、両方が非NULLになることがない設計であれば COALESCE、そうでない場合は ARRAY_CONSTRUCT_COMPACT + FLATTEN に書き換えることで、クエリ実行時間を大幅に改善できることが分かりました。

補足

  • 今回の提案には含みませんでしたが、実はORを代替するアプローチとしてはUNION ALLも検討されます。こちらも等価結合になるため、OR やARRAY_CONSTRUCT_COMPACT + FLATTEN と同じ挙動になりますが、右テーブルのスキャン回数が一回増えてしまう点で最適化の文脈でいうと後者には劣ることになります。もちろんそれでも OR と比較すると大幅な改善が可能であるため、プロダクトの SLI・SLO や実装方針によっては多少スキャンが増えてもコードの可読性や既存実装との整合性を優先するといった判断で UNION ALL を採用する選択肢も妥当です。
YANS参加とポスター発表：LLMを用いた広告文生成における「制作意図」の有効性検証
次の記事
ABEMAリアルタイム基盤サーバー認証システムの設計および実装 大規模トラフィック環境を経験して成長した話
前の記事