Zenn
🔑

BigQuery クエリ性能改善: 内部結合・左外部結合・右外部結合で結合解除される条件の確認

2023/09/29に公開
Google Cloud
BigQuery
tech

本記事は、BigQuery の主キーと外部キーにより、クエリの性能改善が得られるかどうか検証してみたの検証の一部になります。
ここでは、結合方法・PK / FK テーブルの位置・PK / FK テーブルのカラムの取得が変化したとき、内部結合・左外部結合・右外部結合で結合解除されるかどうかを確認します。

1. データセット・テーブルの準備

BigQuery public datasets の wikipedia テーブルの timestamp カラムから TIME 型の time カラムを作成し、Primary key / Foreign key として利用します。
以下 4 つのテーブルを用意します。

  • normal1
  • normal2
  • pk1
  • fk1

ただし、主キーとして利用する normal1 テーブルと pk1 テーブルでは、time カラムが一意になるようにします。

-- データセット作成
CREATE SCHEMA IF NOT EXISTS pk_fk_dataset;

-- 通常テーブル作成
CREATE OR REPLACE TABLE pk_fk_dataset.normal1
(
    title STRING NOT NULL,
    id INTEGER,
    language STRING NOT NULL,
    wp_namespace INTEGER NOT NULL,
    is_redirect BOOLEAN,
    revision_id INTEGER,
    contributor_ip STRING,
    contributor_id INTEGER,
    contributor_username STRING,
    timestamp INTEGER NOT NULL,
    is_minor BOOLEAN,
    is_bot BOOLEAN,
    reversion_id INTEGER,
    comment STRING,
    num_characters INTEGER NOT NULL,
    time TIME NOT NULL,
) AS (
     SELECT
         * except(rn)
     FROM (
         SELECT
            *,
            TIME(TIMESTAMP_SECONDS(timestamp)) as time,
            ROW_NUMBER() OVER (PARTITION BY TIME(TIMESTAMP_SECONDS(timestamp)) ORDER BY timestamp) as rn,
        FROM
            bigquery-public-data.samples.wikipedia
        QUALIFY
            rn = 1
     )
);

CREATE OR REPLACE TABLE pk_fk_dataset.normal2
(
    title STRING NOT NULL,
    id INTEGER,
    language STRING NOT NULL,
    wp_namespace INTEGER NOT NULL,
    is_redirect BOOLEAN,
    revision_id INTEGER,
    contributor_ip STRING,
    contributor_id INTEGER,
    contributor_username STRING,
    timestamp INTEGER NOT NULL,
    is_minor BOOLEAN,
    is_bot BOOLEAN,
    reversion_id INTEGER,
    comment STRING,
    num_characters INTEGER NOT NULL,
    time TIME NOT NULL,
) AS (
     SELECT
         *,
         TIME(TIMESTAMP_SECONDS(timestamp)) as time,
     FROM
         bigquery-public-data.samples.wikipedia
);

-- Primary key, Foreign key を設定したテーブル作成
CREATE OR REPLACE TABLE
pk_fk_dataset.pk1
CLONE pk_fk_dataset.normal1;

CREATE OR REPLACE TABLE
pk_fk_dataset.fk1
CLONE pk_fk_dataset.normal2;

ALTER table pk_fk_dataset.pk1 ADD PRIMARY KEY(time) NOT ENFORCED;

ALTER table pk_fk_dataset.fk1 ADD FOREIGN KEY(time) REFERENCES pk_fk_dataset.pk1(time)  NOT ENFORCED;




2. 内部結合の結合解除確認

2.1. 内部結合 (左: PK, 右: FK) and PK テーブルのカラムのみ取得

以下SQLを実行してみましたが、結合解除されませんでした。

SELECT
    pk1.time
FROM
    pk_fk_dataset.pk1
INNER JOIN
    pk_fk_dataset.fk1
ON
    pk1.time = fk1.time

2.2. 内部結合 (左: PK, 右: FK) and FK テーブルのカラムのみ取得

以下SQLを実行してみると、結合解除されました。

SELECT
    fk1.time
FROM
    pk_fk_dataset.pk1
INNER JOIN
    pk_fk_dataset.fk1
ON
    pk1.time = fk1.time

2.3. 内部結合 (左: FK, 右: PK) and PK テーブルのカラムのみ取得

以下SQLを実行してみましたが、結合解除されませんでした。

SELECT
    pk1.time
FROM
    pk_fk_dataset.fk1
INNER JOIN
    pk_fk_dataset.pk1
ON
    fk1.time = pk1.time

2.4. 内部結合 (左: FK, 右: PK) and FK テーブルのカラムのみ取得

以下SQLを実行してみましたが、結合解除されました。

SELECT
    fk1.time
FROM
    pk_fk_dataset.fk1
INNER JOIN
    pk_fk_dataset.pk1
ON
    fk1.time = pk1.time

3. 左外部結合の結合解除確認

3.1. 左外部結合 (左: PK, 右: FK) and PK テーブルのカラムのみ取得

以下SQLを実行してみましたが、結合解除されませんでした。

SELECT
    pk1.time
FROM
    pk_fk_dataset.pk1
LEFT JOIN
    pk_fk_dataset.fk1
ON
    pk1.time = fk1.time

3.2. 左外部結合 (左: PK, 右: FK) and FK テーブルのカラムのみ取得

以下SQLを実行してみましたが、結合解除されませんでした。

SELECT
    fk1.time
FROM
    pk_fk_dataset.pk1
LEFT JOIN
    pk_fk_dataset.fk1
ON
    pk1.time = fk1.time

3.3. 左外部結合 (左: FK, 右: PK) and PK テーブルのカラムのみ取得

以下SQLを実行してみましたが、結合解除されませんでした。

SELECT
    pk1.time
FROM
    pk_fk_dataset.fk1
INNER JOIN
    pk_fk_dataset.pk1
ON
    fk1.time = pk1.time

3.4. 左外部結合 (左: FK, 右: PK) and FK テーブルのカラムのみ取得

以下SQLを実行してみると、結合解除されました。

SELECT
    fk1.time
FROM
    pk_fk_dataset.fk1
LEFT JOIN
    pk_fk_dataset.pk1
ON
    fk1.time = pk1.time

4. 右外部結合の結合解除確認

4.1. 右外部結合 (左: PK, 右: FK) and PK テーブルのカラムのみ取得

以下SQLを実行してみましたが、結合解除されませんでした。

SELECT
    pk1.time
FROM
    pk_fk_dataset.pk1
RIGHT JOIN
    pk_fk_dataset.fk1
ON
    pk1.time = fk1.time

4.2. 右外部結合 (左: PK, 右: FK) and FK テーブルのカラムのみ取得

以下SQLを実行してみると、結合解除されました。

SELECT
    fk1.time
FROM
    pk_fk_dataset.pk1
RIGHT JOIN
    pk_fk_dataset.fk1
ON
    pk1.time = fk1.time

4.3. 右外部結合 (左: FK, 右: PK) and PK テーブルのカラムのみ取得

以下SQLを実行してみましたが、結合解除されませんでした。

SELECT
    pk1.time
FROM
    pk_fk_dataset.fk1
RIGHT JOIN
    pk_fk_dataset.pk1
ON
    fk1.time = pk1.time

4.4. 右外部結合 (左: FK, 右: PK) and FK テーブルのカラムのみ取得

以下SQLを実行してみましたが、結合解除されませんでした。

SELECT
    pk1.time
FROM
    pk_fk_dataset.fk1
RIGHT JOIN
    pk_fk_dataset.pk1
ON
    fk1.time = pk1.time

5. まとめ

5.1. 結合解除されたかどうかの確認方法

  • 結合解除とは、テーブルの結合を行う SQL を実行したとき、クエリ オプティマイザーが結合を不要と判断し、内部的に無駄な結合を排除することを指します。
  • 結合解除されると、GUI上の実行グラフ内からJoinステージが無くなります。
    • 結合解除なし
    • 結合解除あり

5.2. 結合解除が起きる条件

  • 内部結合の場合、以下のとき結合解除されます。
    • 「FK テーブルのカラムのみ取得」
  • 左外部結合の場合、以下のとき結合解除されます。
    • 「左側に FK、右側に PK」 and 「FK テーブルのカラムのみ取得」
  • 右外部結合の場合、以下のとき結合解除されます。
    • 「左側に PK、右側に FK」 and 「FK テーブルのカラムのみ取得」
  • 結合解除と、結合方法・PK / FK テーブルの位置・PK / FK テーブルのカラムの取得の関係は、次の表の通りです。
結合方法 左テーブル 右テーブル PK テーブルのカラムを取得 (o: あり、 -: なし) FK テーブルのカラムを取得 (o: あり、 -: なし) 結合解除 (o: あり、 -: なし)
内部結合 PK テーブル FK テーブル o - -
PK テーブル FK テーブル - o o
FK テーブル PK テーブル o - -
FK テーブル PK テーブル - o o
左外部結合 PK テーブル FK テーブル o - -
PK テーブル FK テーブル - o -
FK テーブル PK テーブル o - -
FK テーブル PK テーブル - o o
右外部結合 PK テーブル FK テーブル o - -
PK テーブル FK テーブル - o o
FK テーブル PK テーブル o - -
FK テーブル PK テーブル - o -

Discussion