RELY制約の検証

はじめに

ちょっと前にMediumでSnowflake Query Optimiser Eliminating Redundant Joinsの記事を見かけ、なんかよさそうと思ったので今記事を書いています。Snowflakeのドキュメントも見て内容を確認しながら、私の方で作成したサンプルデータ・クエリを基に挙動を確認したいと思います。

ドキュメント

下記に概要のみを書き出しました。

Snowflakeが冗長な結合を削除する方法を理解する

場合によっては、キー列での結合が、結合に不要なテーブルを参照することがあります。
テーブルにキー列があり、 UNIQUE、 PRIMARY KEY、および FOREIGN KEY 制約を使用および適用している場合、Snowflakeはキー列の不要な結合を削除して、クエリのパフォーマンスを向上させることができます。

これらの最適化は、 RELY 制約プロパティを使用して、テーブル内のデータが主キーと外部キーに関する制約に準拠していることを示す場合にのみ実行されます。

書いてある内容としては、RELYをつけてあげると不要な結合を減らせ、結合の最適化をしてくれる様子です。ただし、テーブルの主キー及び外部キーの制約が適切であることが条件となっています。では次から実際に手を動かして確認してみたいと思います。

前提条件

使用するテーブルと関係図を確認します。

使用テーブル

• countries

code	name
1	japan
2	america

• customers

customer_id	customer_name	status
1	test_1	active
2	test_2	inactive

• products

item_id	item_name	price	date	country_code
1	apple	100	2023-01-01	1
2	orange	200	2023-01-01	2

• purchase

customer_id	item_id	purchase_date
1	1	2023-01-01

• calendar

date	oyasumi
2023-01-01	True

サンプルクエリのCREATE TABLEとINSERT TABLEの範囲を指します。

各テーブルの関係

すべて結合 その1

さて、サンプルデータを作成したところで、サンプルクエリのSELECT文を実行してみましょう。以下の画像が、実行したクエリのプロファイルになります。色々ごちゃごちゃと結合処理をしてますね。

制約

今度は、各テーブルに制約条件を付与していきたいと思います。

// Add PRIMARY KEYS
ALTER TABLE tr_daisuke_harato.demo.countries ADD CONSTRAINT "COUNTRY_PK" PRIMARY KEY (code) RELY;
ALTER TABLE tr_daisuke_harato.demo.customers ADD CONSTRAINT "CUSTOMERS_PK" PRIMARY KEY (customer_id) RELY;
ALTER TABLE tr_daisuke_harato.demo.products  ADD CONSTRAINT "CUSTOMERS_PK" PRIMARY KEY (item_id) RELY;
ALTER TABLE tr_daisuke_harato.demo.calendar  ADD CONSTRAINT "CALENDAR_PK" PRIMARY KEY (date) RELY;

// Add UNIQUE KEYS
ALTER TABLE tr_daisuke_harato.demo.purchase  ADD CONSTRAINT "PURCHASE_UK" UNIQUE (customer_id, item_id, purchase_date) RELY;

// Add FOREIGN KEYS
ALTER TABLE tr_daisuke_harato.demo.products ADD CONSTRAINT "PRODUCTS_COUNTRY_FK"  FOREIGN KEY (country_code) REFERENCES tr_daisuke_harato.demo.countries (code) RELY;
ALTER TABLE tr_daisuke_harato.demo.purchase ADD CONSTRAINT "PURCHASE_ITEM_FK"     FOREIGN KEY (item_id) REFERENCES tr_daisuke_harato.demo.products (item_id) RELY;
ALTER TABLE tr_daisuke_harato.demo.purchase ADD CONSTRAINT "PURCHASE_DATE_FK"     FOREIGN KEY (purchase_date) REFERENCES tr_daisuke_harato.demo.calendar (date) RELY;
ALTER TABLE tr_daisuke_harato.demo.purchase ADD CONSTRAINT "PURCHASE_CUSTOMER_FK" FOREIGN KEY (customer_id) REFERENCES tr_daisuke_harato.demo.customers (customer_id) RELY;

すべて結合 その2

再度、結合処理を行います。SELECTの中身はpurchaseテーブルしか使用していないため、クエリプロファイルにはpurchaseテーブルしか表示されていません。しっかりと出力に不要な結合は削除されています。

参考記事

• Snowflake Query Optimiser Eliminating Redundant Joins

• Snowflake 公式ドキュメント【Snowflakeが冗長な結合を削除する方法を理解する】

サンプルクエリ

(データベース名は適宜修正をお願いします)

-- サンプルデータ作成
-- サンプルデータ作成
create or replace table tr_daisuke_harato.demo.countries (
    code  number
    ,name varchar
);

insert into tr_daisuke_harato.demo.countries (code, name)
values
    (1, 'japan'),
    (2, 'america')
;

create or replace table tr_daisuke_harato.demo.customers (
    customer_id    number
    ,customer_name varchar
    ,status        varchar
);


insert into tr_daisuke_harato.demo.customers (customer_id, customer_name, status)
values
    (1, 'test_1', 'active'),
    (2, 'test_2', 'inactive')
;


create or replace table tr_daisuke_harato.demo.products (
    item_id       number
    ,item_name    varchar
    ,price        number
    ,date         date
    ,country_code number
);


insert into tr_daisuke_harato.demo.products (item_id, item_name, price, date, country_code)
values
    (1, 'apple', 100, '2023-01-01', 1),
    (2, 'orange', 200, '2023-01-01', 2)
;

create or replace table tr_daisuke_harato.demo.purchase (
    customer_id    number
    ,item_id       number
    ,purchase_date date
);


insert into tr_daisuke_harato.demo.purchase (customer_id, item_id, purchase_date)
values
    (1, 1,'2023-01-01')
;

create or replace table tr_daisuke_harato.demo.calendar (
    date     date
    ,oyasumi boolean
);


insert into tr_daisuke_harato.demo.calendar (date, oyasumi)
values
    ('2023-01-01', TRUE)
;


-- 全部結合
with all_products as (

    select
        *
    from
        tr_daisuke_harato.demo.products as p
    left join
        tr_daisuke_harato.demo.countries as c
    on
        p.country_code = c.code

)

select
    p.customer_id
    ,count(p.item_id)
from
    tr_daisuke_harato.demo.purchase as p
left join
    tr_daisuke_harato.demo.customers as c
on
    p.customer_id = c.customer_id

left join
    tr_daisuke_harato.demo.calendar as cal
on
    p.purchase_date = cal.date

left join
    all_products as ap
on
    p.item_id = ap.item_id

group by
    p.customer_id
;


// Add PRIMARY KEYS
ALTER TABLE tr_daisuke_harato.demo.countries ADD CONSTRAINT "COUNTRY_PK" PRIMARY KEY (code) RELY;
ALTER TABLE tr_daisuke_harato.demo.customers ADD CONSTRAINT "CUSTOMERS_PK" PRIMARY KEY (customer_id) RELY;
ALTER TABLE tr_daisuke_harato.demo.products  ADD CONSTRAINT "CUSTOMERS_PK" PRIMARY KEY (item_id) RELY;
ALTER TABLE tr_daisuke_harato.demo.calendar  ADD CONSTRAINT "CALENDAR_PK" PRIMARY KEY (date) RELY;

// Add UNIQUE KEYS
ALTER TABLE tr_daisuke_harato.demo.purchase  ADD CONSTRAINT "PURCHASE_UK" UNIQUE (customer_id, item_id, purchase_date) RELY;

// Add FOREIGN KEYS
ALTER TABLE tr_daisuke_harato.demo.products ADD CONSTRAINT "PRODUCTS_COUNTRY_FK"  FOREIGN KEY (country_code) REFERENCES tr_daisuke_harato.demo.countries (code) RELY;
ALTER TABLE tr_daisuke_harato.demo.purchase ADD CONSTRAINT "PURCHASE_ITEM_FK"     FOREIGN KEY (item_id) REFERENCES tr_daisuke_harato.demo.products (item_id) RELY;
ALTER TABLE tr_daisuke_harato.demo.purchase ADD CONSTRAINT "PURCHASE_DATE_FK"     FOREIGN KEY (purchase_date) REFERENCES tr_daisuke_harato.demo.calendar (date) RELY;
ALTER TABLE tr_daisuke_harato.demo.purchase ADD CONSTRAINT "PURCHASE_CUSTOMER_FK" FOREIGN KEY (customer_id) REFERENCES tr_daisuke_harato.demo.customers (customer_id) RELY;