RedshiftのPrimary KeyおよびUnique制約が実行計画に与える影響

こんにちは。データアナリティクス事業本部の松村です。
早いものでもう入社から1年が経ちましたが、これまでの投稿ペースは平均して月1本、そして間隔にはかなりばらつきがあります。今後はコンスタントに月2本を達成したいなあと思う次第からの、2年目初の投稿です。

RedshiftにおけるPrimary Key制約の役割

Redshiftが一般的なRDBMSとは異なっていることのひとつとして、Primary Key制約が実際には制約として機能しない、というものがあります。かなり有名なのでご存じの方も多いと思います。
しかしながらマニュアルによると、実行計画の作成時には利用されるそうです。

CREATE TABLE

Important
Primary key constraints are informational only. They aren't enforced by the system, but they are used by the planner.

ただ、Primary Key制約があることで実際に実行計画にどのように影響するかまではここには書かれていません。というわけで調べてみました。

結論

実際にあれこれ試してみる前に、軽くGoogle先生に聞いてみたところ、わりとあっさり答えが見つかってしまいました。

Defining table constraints

The planner leverages these key relationships, but it assumes that all keys in Amazon Redshift tables are valid as loaded. If your application allows invalid foreign keys or primary keys, some queries could return incorrect results. For example, a SELECT DISTINCT query might return duplicate rows if the primary key is not unique. Do not define key constraints for your tables if you doubt their validity. On the other hand, you should always declare primary and foreign keys and uniqueness constraints when you know that they are valid.

なるほど、主キーに対してDISTINCTを指定した場合、一意であるという定義を信じて、改めて値の集約はしないというわけですね。だから定義に反して値の重複があったとしても、それをDISTINCTで排除することはできないので、Primary Key制約の付与には慎重になることを読者に求めています。
これを実証した記事も見つけることができました。

Amazon Redshift – What you need to think before defining primary key

しかし、ただこれを紹介して終わりではあまりに寂しすぎるので、もう少し調べてみることにしました。

追加の検証

準備

実際にPrimary Key制約を付与したテーブルを作ります。Unique制約だったらどうなるかというのも見てみたいので、それも付与しておきます。

CREATE TABLE users (
    id          INTEGER,
    mailaddress VARCHAR(100),
    name        VARCHAR(100),
    PRIMARY KEY(id),
    UNIQUE(mailaddress)
);

さらに、重複したデータを投入しておきます。

INSERT INTO users
    (id, mailaddress, name)
VALUES
    (1, 'foo@example.com', 'foo'),
    (2, 'bar@example.com', 'bar'),
    (2, 'bar@example.com', 'bar');

一応統計情報も更新しておきます。

ANALYZE users;

制約のない列でDISTINCTした場合

まずはPrimary Key制約もUnique制約もない列でDISTINCTしてみます。

SELECT DISTINCT name FROM users;

name|
----|
foo |
bar |

ちゃんとDISTINCTが効いて、barが1行だけ出力されていますね。実行計画も見てみましょう。

EXPLAIN SELECT DISTINCT name FROM users;

QUERY PLAN                                                  |
------------------------------------------------------------|
XN Unique  (cost=0.00..0.04 rows=2 width=7)                 |
  ->  XN Seq Scan on users  (cost=0.00..0.03 rows=3 width=7)|

Uniqueという操作が表示されています。これで重複を排除してくれているわけです。

主キーでDISTINCTした場合

主キーでDISTINCTしてみます。

SELECT DISTINCT id FROM users;

id|
--|
 1|
 2|
 2|

DISTINCTが効かず、重複がそのまま出力されています。実行計画はこちら。

EXPLAIN SELECT DISTINCT id FROM users;

QUERY PLAN                                            |
------------------------------------------------------|
XN Seq Scan on users  (cost=0.00..0.03 rows=3 width=4)|

先程はあったUnique操作がなくなっています。重複が排除されないのはこのためです。

ユニークキーでDISTINCTした場合

ユニークキーでDISTINCTしてみます。

SELECT DISTINCT mailaddress FROM users;

mailaddress    |
---------------|
foo@example.com|
bar@example.com|
bar@example.com|

これもDISTINCTが効かず、重複がそのまま出力されています。実行計画はこちら。

EXPLAIN SELECT DISTINCT mailaddress FROM users;

QUERY PLAN                                             |
-------------------------------------------------------|
XN Seq Scan on users  (cost=0.00..0.03 rows=3 width=19)|

やはりUnique操作がありません。
マニュアルでは明言されていませんが、Unique制約もやはり実行計画に影響が及ぶことがわかりました。

CREATE TABLE

Important
Unique constraints are informational and aren't enforced by the system.

主キーやユニークキー＋他の列でDISTINCTした場合

最後に、主キーやユニークキーにさらに他の列を加えてDISTINCTしてみます。
主キーやユニークキーが含まれている時点で定義上は一意と期待されるので、これも重複排除してくれそうにないですが、果たしてどうでしょうか。

SELECT DISTINCT id, name FROM users;

id|name|
--|----|
 1|foo |
 2|bar |

SELECT DISTINCT mailaddress, name FROM users;

mailaddress    |name|
---------------|----|
foo@example.com|foo |
bar@example.com|bar |

予想に反して、これはちゃんと重複排除してくれていますね。実行計画はこちら。

EXPLAIN SELECT DISTINCT id, name FROM users;

QUERY PLAN                                                   |
-------------------------------------------------------------|
XN Unique  (cost=0.00..0.04 rows=3 width=11)                 |
  ->  XN Seq Scan on users  (cost=0.00..0.03 rows=3 width=11)|

EXPLAIN SELECT DISTINCT mailaddress, name FROM users;

QUERY PLAN                                                   |
-------------------------------------------------------------|
XN Unique  (cost=0.00..0.04 rows=3 width=26)                 |
  ->  XN Seq Scan on users  (cost=0.00..0.03 rows=3 width=26)|

SELECT結果を見れば明らかですが、ちゃんとUniqueをやってくれていますね。

まとめ

RedshiftのPrimary Key制約およびUnique制約は、実際には制約として機能しない一方で、オプティマイザは実際に一意であることを信用して実行計画を作成すること、その結果、制約に反して重複データがある場合はクエリ結果に問題が生じうることがわかりました。
これらの制約を付与する場合は、他の何らかの手段で実際に値が一意になることを担保する必要がある、と言えるでしょう。