Zenn
⏱️

BigQueryで集合演算クエリのパフォーマンスを比較してみた

2021/09/01に公開
SQL
BigQuery
tech

BigQueryで集合演算のクエリを比較してみました。
具体的には、aとbというテーブルを利用して、aからbを除いたユーザーIDのユニークな数をカウントします。
SQL的には複数の書き方ができるので、それぞれのパフォーマンスを比較してみました。

SQL比較

  • テーブルaのレコード数は4,874,255
  • テーブルbのレコード数は4,905,549
  • a,bは同一のテーブルから異なる条件でSELECTして、WITH句で宣言しています
  • クエリの実行の詳細のS00,S01はWITH句でのSELECTです

LEFT JOIN

SELECT COUNT(1) FROM (
    SELECT 
        DISTINCT a.user_id
    FROM a LEFT JOIN b
    ON a.user_id = b.user_id
    WHERE b.user_id IS NULL
)

NOT EXISTS

SELECT COUNT(1) FROM (
    SELECT 
        DISTINCT user_id
    FROM a 
    WHERE NOT EXISTS (
        SELECT 1 FROM b
        WHERE a.user_id = b.user_id
    )
)

EXCEPT

SELECT COUNT(1) FROM (
    SELECT user_id FROM a
    EXCEPT DISTINCT
    SELECT user_id FROM b
)

NOT IN

SELECT COUNT(1) FROM (
    SELECT 
        DISTINCT a.user_id
    FROM a
    WHERE user_id NOT IN (
        SELECT user_id FROM b
    )
)


結果

  • NOT INだけ明らかにパフォーマンスが悪いです
  • LEFT JOIN, NOT EXISTS, EXCEPTはほぼ一緒です
    • キャプチャはありませんが、S02のJOINのタイプが違っています
      • LEFT JOIN: LEFT OUTER HASH JOIN EACH WITH EACH ON
      • NOT EXISTS: ANTI HASH JOIN EACH WITH EACH ON
      • EXCEPT: ANTI HASH JOIN EACH WITH EACH ON

INTERSECTの場合

a,bに重複しているユーザー数をカウントする場合についても検証してみました。
LEFT JOIN → INNER JOIN, NOT EXISTS → EXISTS, EXCEPT → INTERSECT, NOT IN → INとすればよいです。

INNER JOIN

 SELECT COUNT(1) FROM (
    SELECT 
        DISTINCT a.user_id
    FROM a INNER JOIN b
    ON a.user_id = b.user_id
)

EXISTS

SELECT COUNT(1) FROM (
    SELECT 
        DISTINCT user_id
    FROM a 
    WHERE EXISTS (
        SELECT 1 FROM b
        WHERE a.user_id = b.user_id
    )
)

INTERSECT

SELECT COUNT(1) FROM (
    SELECT user_id FROM a
    INTERSECT DISTINCT
    SELECT user_id FROM b
)

IN

SELECT COUNT(1) FROM (
    SELECT 
        DISTINCT a.user_id
    FROM a
    WHERE user_id IN (
        SELECT user_id FROM b
    )
)

結果

  • 結果はINも含めて、ステージ数、シャッフルされたバイト数は一緒になった
    • INのJOINタイプはSEMI HASH JOIN EACH WITH EACH ON

まとめ

  • 集合演算のクエリを比較してみた
  • NOT INはあまり使わないほうがよさそう
  • NOT IN以外であれば、他はお好みで
  • 個人的にはやりたいことと、クエリが一致していて、クエリもシンプルなINTERSECT, EXCEPが好き
  • BQのJOINのタイプに関するドキュメントはどこですか？

Discussion