BigQueryの主キー/外部キー制約はJoin Eliminationには使われない

1. はじめに

BigQuery でプレビューながら主キー/外部キー制約が利用可能になったそうです。

以下のドキュメントのように、実際にデータのチェックが行われるわけではありません。これは Snowflake や Amazon Redshift などでも同様なので、驚きはないというか想像通りですね。

Value constraints for primary keys and foreign keys are not enforced. Users need to ensure that values match their respective constraints, otherwise they may get incorrect results.

データのチェックが行われないのであれば主キー/外部キー制約を付与する理由は何なのかというと、（BigQuery ではなく）一般の DBMS ではオプティマイザが実行計画を最適化する際に利用されます。その最適化の手法の一つに不要な結合処理をスキップする Join Elimination という手法があります。以下のページは Snowflake で Join Elimination を解説したページです。

2023/2/25時点で BigQuery で Join Elimination が効くか確認したので、メモとして残しておきます。

2. 確認手順

今回は、２つのテーブルを結合する SQL 文で Join　Elimination が効くか確認します。

2-1. テーブル作成

主キー/外部キー制約を付与した users テーブルと orders テーブルを作成します。制約に対しては not enforced を付与しないとエラーになります。

create or replace table je_test.users (
  user_id int,
  user_name string,
  primary key(user_id) not enforced
);

insert into je_test.users
values
(1, 'Alice'),
(2, 'Bob');

create or replace table je_test.orders (
  order_id int,
  user_id int,
  order_amt int,
  primary key(order_id) not enforced,
  foreign key(user_id) references je_test.users(user_id) not enforced
);

insert into `je_test.orders`
values
(101, 1, 100),
(102, 1, 120),
(103, 2, 150);

2-2. 結合クエリの実行

以下の SQL 文を実行します。

select
  o.user_id,
  sum(o.order_amt) total_order_amt
from
  je_test.orders o
inner join
  je_test.users u on (o.user_id = u.user_id)
group by
  o.user_id
;

orders テーブルと users テーブルを結合しているのですが、以下の理由で実際には users テーブルの結合は不要です。

• SELECT リストに users テーブルのカラムが含まれていない。
• 主キー/外部キー制約から orders テーブルにレコードに該当する users テーブルのレコードは必ず１件のみ存在する。

ただし、実行グラフを確認すると、実際には users テーブルは読み込まれ、結合処理もされているように見えます。

（仕事でほとんど BigQuery を使うことがないので実行グラフの読み方が間違っていたらごめんなさい）

3. さいごに

まぁ、すぐに対応されるんじゃないかと思ってます。

Join Elimination の機能は、ユーザーが複数のテーブルから表示カラムなどを選択してデータを検索するアプリを実装する場合に、アプリ側で生成する SQL 文を FROM 句を固定して（＝アクセスされるテーブルをすべて含めて） SELECT リストのみ可変にするようにしても性能上問題が出ないようにするケースでよく利用されるのですが、バッドノウハウのような気もするんですよね。
（ちゃんとした BI ツールなら不要なテーブルはそもそも FROM 句から除外するはず）

現時点で BigQuery が主キー/外部キーをサポートしている意義は、INFORMATION_SCHEMA を通してサードパーティーツールにメタデータとして情報を提供するということでいいのでしょうか。

4. おまけ（Oracle での Join Elimination）

Oracle だと制約を disable noinvalidate にすると BigQuery と同様に制約によるデータチェックは行われません。

そこで

• rely オプションを制約に付与
• クエリ実行時に初期化パラメーター query_rewrite_integrity を trusted に設定

することで Join Elimination を効かせることができます。

SQL> create table users (
  2     user_id integer,
  3     user_name varchar(100),
  4     constraint users_pk
  5             primary key (user_id)
  6             rely disable novalidate
  7  );

表が作成されました。

SQL> insert into users values (1, 'Alice');

1行が作成されました。

SQL> insert into users values (2, 'Bob');

1行が作成されました。

SQL> create table orders (
  2     order_id integer,
  3     user_id  integer,
  4     order_amt integer,
  5     constraint orders_pk
  6             primary key (order_id)
  7             rely disable novalidate,
  8     constraint orders_fk01
  9             foreign key (user_id)
 10             references users (user_id)
 11             rely disable novalidate
 12  );

表が作成されました。

SQL> insert into orders values (101, 1, 100);

1行が作成されました。

SQL> insert into orders values (102, 1, 120);

1行が作成されました。

SQL> insert into orders values (103, 2, 150);

1行が作成されました。

SQL> set feedback on sql_id
SQL> alter session set query_rewrite_integrity = trusted;

セッションが変更されました。

SQL_ID: 21mtf6rrp45jk
SQL> select
  2    o.user_id,
  3    sum(o.order_amt) total_order_amt
  4  from
  5    orders o
  6  inner join
  7    users u on (o.user_id = u.user_id)
  8  group by
  9    o.user_id
 10  ;

   USER_ID TOTAL_ORDER_AMT
---------- ---------------
         1             220
         2             150

2行が選択されました。

SQL_ID: 4hyz0ygzqnf58
SQL> set pagesize 0
SQL> select * from table(dbms_xplan.display_cursor('4hyz0ygzqnf58'));
SQL_ID  4hyz0ygzqnf58, child number 0
-------------------------------------
select   o.user_id,   sum(o.order_amt) total_order_amt from   orders o
inner join   users u on (o.user_id = u.user_id) group by   o.user_id

Plan hash value: 2183589723

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |       |       |     4 (100)|          |
|   1 |  HASH GROUP BY     |        |     3 |    78 |     4  (25)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| ORDERS |     3 |    78 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("O"."USER_ID" IS NOT NULL)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - rely constraint used for this statement


25行が選択されました。

SQL_ID: d0970urfdrbtz
SQL>