BigQuery最適化: 相関サブクエリはスカラーサブクエリを配列サブクエリにするとフィルタされる

この記事はBigQuery Advent Calendar 2025の記事です。

はじめに

手書きで単独のクエリを書くときは、不要なカラムをSQL中に書くことはありません。
しかし、BIツールからクエリを実行すると、BIツール側の条件に応じて実行されるクエリが変更されます。
この際、BigQueryでは明らかに不要なテーブル・カラムは取得しなくなります。

しかし、スカラー相関サブクエリでは、不要なテーブル・カラムへアクセスされていましたので、状況を調査して回避策を調べました。

BigQueryの不要テーブル・カラムの最適化について

BigQueryではクエリ上で明らかに不要なテーブル・カラムは取得しなくなります。またプッシュダウンの機能により、BIツール側の条件をソース側に波及させ、不要なカラム・テーブルの取得を回避することができます。

例えば、以下のようなクエリではhogeテーブルは明らかに不要なので取得することはありません。

    select
        if(true, 1, (select .. from hoge))

また、BigQueryではプライマリクエリを利用したフィルタの最適化が行われます。例えば以下のようなクエリでは、tbl2.idがprimary keyの場合、不要なtbl2は取得しません。

declare TARGET_GROUP default 'tbl1.hoge';

select
    case TARGET_GROUP
        'tbl1.hoge' then tbl1.hoge
        'tbl2.hoge' then tbl2.hoge
    end
from tbl1
left outer join tbl2 using(id)

BigQueryの相関サブクエリについて

BigQueryでは相関サブクエリを利用できます。しかし、RDBのように行ごとにサブクエリを実行することはなく、相関サブクエリはJOINに変換されます。

例えば以下のような相関サブクエリを考えます。

select
  (select group_name from grps where grps.group_id = tbl1.group_id) as grp,
  count(*) as cnt,
from tbl1
group by grp

この相関サブクエリは以下のようなJOINに変換されて実行されます。(実際には重複を省く処理などが入ります。

select
  grps.group_name as grp,
  count(*) as cnt,
from tbl1
left outer join grps on tbl1.group_id = grps.group_id
group by grp

スカラー相関サブクエリのCASE文でのフィルタについて

さて、クエリを実行して気づいたのですが、スカラー相関サブクエリをCASE文中で利用した場合、明らかに利用されないテーブルも読み込まれていました。なぜか最適化が行われず、JOINがされているようでした。

例えば以下のクエリではgrpsテーブルの読み込みは不要なはずですが、読み込まれていました。

declare TARGET_GROUP default 'all';

create temp table tbl1 as (
  select 1 as group_id, 'hoge' as text
  union all
  select 2 as group_id, 'fuga' as text
);

create temp table grps as (
  select 1 as group_id, 'grp1' as group_name
  union all
  select 2 as group_id, 'grp2' as group_name
);

select
  case TARGET_GROUP
    when "all" then "all"
    when "group" then (select group_name from grps where grps.group_id = tbl1.group_id)
  end as grp,
  count(*) as cnt,
from tbl1
group by grp

このクエリは、実質的には以下のように相関サブクエリがJOINに変換されて実行されているようです。

create temp table tbl1 as (
  select 1 as group_id, 'hoge' as text
  union all
  select 2 as group_id, 'fuga' as text
);

create temp table grps as (
  select 1 as group_id, 'grp1' as group_name
  union all
  select 2 as group_id, 'grp2' as group_name
);

select
  case "all"
    when "all" then "all"
    when "group" then grps.group_name
  end as grp,
  count(*) as cnt,
from tbl1
left outer join grps on tbl1.group_id = grps.group_id
group by grp

スカラー相関サブクエリを配列相関サブクエリにして最適化

そこで、スカラー相関サブクエリを以下のように配列相関サブクエリに変換すると、不要なテーブルへのアクセスがなくなりました。

select ARRAY(select ....)[SAFE_OFFSET(0)]

create temp table tbl1 as (
  select 1 as group_id, 'hoge' as text
  union all
  select 2 as group_id, 'fuga' as text
);

create temp table grps as (
  select 1 as group_id, 'grp1' as group_name
  union all
  select 2 as group_id, 'grp2' as group_name
);

select
  case "all"
    when "all" then "all"
    when "group" then grps.group_name
  end as grp,
  count(*) as cnt,
from tbl1
left outer join grps on tbl1.group_id = grps.group_id
group by grp

配列サブクエリのデメリット

配列サブクエリにしてOFFSETで取り出すと、結果が２つ以上帰ってきた場合のエラーチェックはなくなります。

まとめ

スカラー相関サブクエリを配列相関サブクエリにすることで、不要なテーブル・カラムの取得を回避して最適化できることを確認しました。