【Redshift】クエリパターンとパフォーマンスから学ぶCompound SortKeyとInterleaved SortKeyのユースケース

春田拓海 | Takumi

2019.08.21

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは。DA事業本部の春田です。

先日、RedshiftのAdvisor機能より、こんなアラートが出ていました。同じような経験はございますか？

Initialize Interleaved Sort Keys Recomendation Run VACUUM REINDEX, as a superuser, on tables with inactive interleaved sort keys.

= VACUUM REINDEXを実行して、インターリーブソートキーを初期化してください。

Replace Single Column Interleaved Sort Keys Recomendation Recreate 5 tables to use a single-column compound sort key.

= 一つのカラムにしか設定していないインターリーブソートキーは、複合キーで作り直してください。

どちらも Interleaved Sort Key に関するアラートです。Interleaved Sort Keyは複数のカラムに対して均等な関係のインデックスを貼ることができる便利さがゆえに、とりあえず設定しておくにはもってこいのソートキーですが、結果的に運用上のパフォーマンスに悪影響を及ぼしている場合があります。しかしながら、このAdvisorの Recommendation に記載されている対処法はあくまでも一例であり、実際の運用方針やテーブル設計に沿わない場合もあります。ソートキー設計を根本から見直していく必要があるでしょう。

今回、Compound Sort KeyとInterleaved Sort Keyの特徴をまとめた上で、クエリごとにパフォーマンスにどのような違いが出るのか一つずつ検証してみました。ソートキー設計はかなり複雑で骨の折れる作業だと思います。本記事が一つの指針として参考になれば幸いです。

※2020/01/20: JOINのみ再検証を行いました。内部でパフォーマンスの改善があったからか、ほぼ分散キーが適切に設定しているかどうかが一番影響が大きくなっています。ソートキーはMarge Joinを狙いたい時ぐらいにしか、重要度がなさそうです。

Compound Sort KeyとInterleaved Sort Keyの特徴

Compound Sort KeyとInterleaved Sort Keyには、以下のような細かな（細かすぎる）特徴があります。

Compound Sort Key（複合キー）

Compound Sort KeyはRedshiftのデフォルトのソート形式で、1つ以上のカラムから構成することができます。一般的に ORDER BY 、 GROUP BY やWindow関数の PARTION BY に置いて大きな効果を発揮します。

Compound Sort Keyの設計において最も重要な点は カラムの順番 です。1番目のカラムが プライマリ 、2番目のカラムが セカンダリ と呼ばれ、プライマリは単独でも使えますが、セカンダリ以降はその前のカラムと一緒に使用しなければほとんど効果を発揮することができません。例えば、以下のようなCompound Sort Keyを持つテーブルで ORDER BY をする場合、 dateid は単独で ORDER BY しても問題ありませんが、 eventid は dateid と併用して ORDER BY しなければ大幅に性能が落ちるということです。

CREATE TABLE sales_csk
(
  salesid integer not null,
  listid integer not null,
  sellerid integer not null,
  buyerid integer not null,
  eventid integer not null,
  dateid smallint not null,
  qtysold smallint not null,
  pricepaid decimal(8,2),
  commission decimal(8,2),
  saletime timestamp
)
DISTSTYLE KEY DISTKEY (dateid)
COMPOUND SORTKEY (dateid, eventid);

また、Compound Sort Keyの大きなメリットの一つに、特定の条件下において Merge Join がクエリプランで採択されるという点です。Merge JoinはRedshiftのJOIN演算子の中で最も高速な演算方法であり、今回行った検証でも著しく高いパフォーマンスを発揮しました。ただし、Merge Joinを実行するための条件はかなり難しく、データやテーブルが複雑になるほど達成が困難です。このMerge Joinの条件を満たせない場合は Hash Join が採択され、それゆえ大概の JOIN がHash Joinで行われています。（クロス結合や一部の不等結合では Nested Loop が採択されます）

Merge Joinの条件

INNER JOIN と OUTER JOIN のみで使用される。 FULL JOIN では使われない
両テーブルの結合カラムが 分散キー であり、かつ Compound Sort Keyのプライマリ を含んでいること
80%以上ソート済みのテーブルであること

その他、列の圧縮率を向上させたい場合にもCompound Sort Keyは有効です。

Interleaved Sort Key（インターリーブソートキー）

対するInterleaved Sort Keyも1つ以上のカラムから構成することができますが、 カラムの重要度が等しく、それぞれを単独で使用しても効果を発揮することができる 点が最大の特徴です。また、Interleaved Sort Keyのカラムを WHERE で選択的に制限する場合や、8バイト以上のPrefixのカラムに対してソートキーを設定する場合には、Compound Sort Keyよりも高いパフォーマンスが期待できます。

Interleaved Sort Keyの性能を維持するための条件として、定期的に VACUUM REINDEX を実行していく必要があります。 VACUUM REINDEX では通常の VACUUM FULL 処理の前に、インターリーブソートキー列の分散を分析して行の再調節を行う処理が加わり、その分実行時間が長くなります。そのため日々のバッチ処理やDBの運用方針とバッティングしないよう、よりシビアにスケジュールを組んでいく必要があります。運用上、この VACUUM REINDEX が定期的に実行できない場合は、IDやタイムスタンプといった レコード数、カーディナリティが単調に増加していくカラム に対しては、Interleaved Sort Keyを設定することは避けるべきです。

また、Interleaved Sort Keyは「スライスごとに1MBのブロックを複数必要とする大きさのテーブルで効果的」という特徴があるのですが、裏を返すとデータ量がそこまで大きくないテーブルに対しては顕著な効果を発揮しません。（ WHERE 句が多用される場合は別です。）高々レコード数が1000万件ぐらいのテーブルに対しては、チューニング・テクニックの影響よりもRedshiftのパワーの方が勝ってしまうので、効果が実感しづらいかと思います。個人的に、レコード数1億件を超えた辺りからInterleaved Sort Keyの性能が右肩上がりに上昇していくイメージがあります。

ちなみに VACUUM REINDEX をかけるべきかどうかの判断基準として、SVV_INTERLEAVED_COLUMNSのinterleaved_skewという数値があります。interleaved_skewの値が1.4以上の場合、VACUUM REINDEX によりパフォーマンスが向上すると言われています。

-- interleaved_skewを確認するクエリ例
SELECT DISTINCT
  sic.tbl as tbl_id,
  sti.schema as schema_name,
  stp.name as table_name,
  sti.tbl_rows,
  sic.col,
  sic.interleaved_skew,
  sic.last_reindex
FROM svv_interleaved_columns sic
LEFT JOIN svv_table_info sti ON sic.tbl = sti.table_id
LEFT JOIN stv_tbl_perm stp ON sic.tbl = stp.id
WHERE interleaved_skew > 1.4
ORDER BY schema_name, table_name, col;

Compound Sort KeyもInterleaved Sort Keyも一長一短であり、テーブル間の関係やSQLが複雑になればなるほど、どちらの方が適切なのかわからなくなってきます。そこで今回はシンプルなクエリパターンを通して、２つのソートキーのユースケースを検証していきたいと思います。

検証結果の結論

検証の部分がかなり長いので先に結論から申しますと、バッチ処理等で扱っているデータ量が1億件に満たない場合は以下のような方針がベターだと感じました。

Compound Sort Keyベースで設計することを考え、「プライマリ・セカンダリの位置」と「SQL（JOINやWHERE）」の関係性をすり合わせていく
JOINを使用しているテーブルに対しては、分散キーにCompound Sort Keyのプライマリを指定できるかどうか考える　→　Merge Joinできれば大幅なパフォーマンス向上につながる可能性
実際に運用してみて、明らかにボトルネックがありそうなテーブルに関しては、Interleaved Sort Keyを適用してみる

データ量が1億件を超える場合は、Interleaved Sort Keyの特性も生かせるので、よりシビアなソートキー設計が求められます。こちらに関しては、考慮すべき順番を一概に定められませんでした。

プライマリもセカンダリも平等に扱いたいテーブルに対しては、積極的にInterleaved Sort Keyを設定していくとパフォーマンスの向上が見込める
1億件を超えるテーブルでは VACUUM REINDEX の負荷が重くなるため、全てのテーブルでInterleaved Sort Keyを使うということは避けたい
Interleaved Sort Keyを使うテーブルには、 INSERT ではなく COPY による全件洗替（Deep Copy）の運用を考えてみる　→　Deep Copy中は VACUUM 処理と違って同時更新を実行できないが、ロード時に自動的にインターリーブインデックスが作成されるため、 VACUUM REINDEX の実行を抑えられる
などなど……

上の方針案は、ドキュメントや検証結果から体感で導き出された一案であり、絶対案ではありません。あらかじめご理解ください。また、この結論は複雑かつ固定されたバッチ処理を行うテーブルに対してのソートキー設計方針であり、クライアントで参照されるような分析用のテーブルでは、よく使うクエリに合わせて設計するのがベストです。

より機械的にソートキーを設計したい場合は、AWSJシバタツさんのスライドがものすごく解りやすいので、合わせてご参照ください。

検証前の予備知識とサンプルテーブル

今回の検証では、ソートキーなし、Compound Sort Key、Interleaved Sort Keyをそれぞれ設定したサンプルテーブルを用意し、クエリ実行時間と以下のクエリプランから比較していきます。

コスト : EXPLAIN のクエリ実行計画の中で見れるクエリのパフォーマンスを測る相対的な指標。2つのピリオドで区切られた最初の数値が「最初の行を返す相対コスト」、末尾の数値が「クエリ完了までの相対コスト」を表す。（例 cost=131.97..133.41 ）

Join演算子 : テーブルをJOINする時にRedshift側で自動的に採択される演算方法。テーブル設計やデータの状態に基づく。Nested Loop、Hash Join (および Hash)、Merge Joinの3つの演算子があり、RedshiftではMerge Joinが一番高速な演算方法であるが、達成条件が厳しい。

集計演算子 : 集計関数および GROUP BY を含むクエリで自動的に採択される演算方法。 GROUP BY ではHashAggregateとGroupAggregateの2つあり、ソート処理を含む集計の場合GroupAggregateが採択される。

データの再分散 : テーブルをJOINする時に各ノード上のデータを他のノードに移動する手法。DS_BCAST_INNER、DS_DIST_ALL_NONE、DS_DIST_NONE、DS_DIST_INNER、DS_DIST_OUTER、DS_DIST_ALL_INNER、DS_DIST_BOTHの7種類存在し、余計なデータの移動がない手法（例えばDS_DIST_NONE）が好ましい。

検証テーブルは、ステップ 6: Amazon S3 のサンプルデータをロードする - Amazon Redshiftとステップ 1: テストデータセットを作成する - Amazon Redshiftのサンプルデータより作成しました。（なお、検証で言及しているレコード数の違いによるソートキー性能の差については、あまり厳密に調査しておりません。）

-- 365rows
CREATE TABLE date(
	dateid smallint not null,
	caldate date not null,
	day character(3) not null,
	week smallint not null,
	month character(5) not null,
	qtr character(5) not null,
	year smallint not null,
	holiday boolean default('N')
)
DISTSTYLE KEY DISTKEY(dateid)
ソートキーなし | COMPOUND SORTKEY (dateid) | INTERLEAVED SORTKEY (dateid)
;

-- 8,798rows
CREATE TABLE event(
	eventid integer not null,
	venueid smallint not null,
	catid smallint not null,
	dateid smallint not null,
	eventname varchar(200),
	starttime timestamp
)
DISTSTYLE KEY DISTKEY(eventid)
ソートキーなし | COMPOUND SORTKEY (eventid) | INTERLEAVED SORTKEY (eventid)
;

-- 172,456rows
CREATE TABLE sales(
	salesid integer not null,
	listid integer not null,
	sellerid integer not null,
	buyerid integer not null,
	eventid integer not null,
	dateid smallint not null,
	qtysold smallint not null,
	pricepaid decimal(8,2),
	commission decimal(8,2),
	saletime timestamp
)
DISTSTYLE KEY DISTKEY(dateid)
ソートキーなし | COMPOUND SORTKEY (dateid, eventid) | COMPOUND SORTKEY2 (eventid, dateid) | INTERLEAVED SORTKEY (dateid, eventid)
;

-- 1,400,000rows
CREATE TABLE part(
	p_partkey     INTEGER NOT NULL,
	p_name        VARCHAR(22) NOT NULL,
	p_mfgr        VARCHAR(6) NOT NULL,
	p_category    VARCHAR(7) NOT NULL,
	p_brand1      VARCHAR(9) NOT NULL,
	p_color       VARCHAR(11) NOT NULL,
	p_type        VARCHAR(25) NOT NULL,
	p_size        INTEGER NOT NULL,
	p_container   VARCHAR(10) NOT NULL
)
DISTSTYLE KEY DISTKEY(p_partkey)
ソートキーなし | COMPOUND SORTKEY (p_partkey, p_size) | INTERLEAVED SORTKEY (p_partkey, p_size)
;

-- 3,000,000rows
CREATE TABLE customer(
	c_custkey      INTEGER NOT NULL,
	c_name         VARCHAR(25) NOT NULL,
	c_address      VARCHAR(25) NOT NULL,
	c_city         VARCHAR(10) NOT NULL,
	c_nation       VARCHAR(15) NOT NULL,
	c_region       VARCHAR(12) NOT NULL,
	c_phone        VARCHAR(15) NOT NULL,
	c_mktsegment   VARCHAR(10) NOT NULL
)
DISTSTYLE KEY DISTKEY(c_custkey)
ソートキーなし | COMPOUND SORTKEY (c_custkey, c_region) | INTERLEAVED SORTKEY (c_custkey, c_region)
;

-- 600,037,902rows
CREATE TABLE lineorder(
	lo_orderkey          INTEGER NOT NULL,
	lo_linenumber        INTEGER NOT NULL,
 	lo_custkey           INTEGER NOT NULL,
	lo_partkey           INTEGER NOT NULL,
	lo_suppkey           INTEGER NOT NULL,
	lo_orderdate         INTEGER NOT NULL,
	lo_orderpriority     VARCHAR(15) NOT NULL,
	lo_shippriority      VARCHAR(1) NOT NULL,
	lo_quantity          INTEGER NOT NULL,
	lo_extendedprice     INTEGER NOT NULL,
	lo_ordertotalprice   INTEGER NOT NULL,
	lo_discount          INTEGER NOT NULL,
	lo_revenue           INTEGER NOT NULL,
	lo_supplycost        INTEGER NOT NULL,
	lo_tax               INTEGER NOT NULL,
	lo_commitdate        INTEGER NOT NULL,
	lo_shipmode          VARCHAR(10) NOT NULL
)
DISTSTYLE KEY DISTKEY(lo_custkey)
ソートキーなし | COMPOUND SORTKEY (lo_custkey, lo_partkey) | COMPOUND SORTKEY2 (lo_partkey, lo_custkey) | INTERLEAVED SORTKEY (lo_custkey, lo_partkey)
;

/*
nsk: ソートキーなし
csk: COMPOUND SORTKEY
csk2: COMPOUND SORTKEY(プライマリとセカンダリがcskと逆) 
ilsk: INTERLEAVED SORTKEY
*/

copy date_nsk from 's3://awssampledbuswest2/tickit/date2008_pipe.txt' 
credentials 'aws_iam_role=<iam-role-arn>' 
delimiter '|' region 'us-west-2';
INSERT INTO date_csk SELECT * FROM date_nsk;
INSERT INTO date_ilsk SELECT * FROM date_nsk;

copy event_nsk from 's3://awssampledbuswest2/tickit/allevents_pipe.txt' 
credentials 'aws_iam_role=<iam-role-arn>'
delimiter '|' timeformat 'YYYY-MM-DD HH:MI:SS' region 'us-west-2';
INSERT INTO event_csk SELECT * FROM event_nsk;
INSERT INTO event_ilsk SELECT * FROM event_nsk;

copy sales_nsk from 's3://awssampledbuswest2/tickit/sales_tab.txt'
credentials 'aws_iam_role=<iam-role-arn>'
delimiter '\t' timeformat 'MM/DD/YYYY HH:MI:SS' region 'us-west-2';
INSERT INTO sales_csk SELECT * FROM sales_nsk;
INSERT INTO sales_csk2 SELECT * FROM sales_nsk;
INSERT INTO sales_ilsk SELECT * FROM sales_nsk;

copy part_nsk from 's3://awssampledbuswest2/ssbgz/part' 
credentials 'aws_iam_role=<iam-role-arn>'
gzip compupdate off region 'us-west-2';
INSERT INTO part_csk SELECT * FROM part_nsk;
INSERT INTO part_ilsk SELECT * FROM part_nsk;

copy customer_nsk from 's3://awssampledbuswest2/ssbgz/customer' 
credentials 'aws_iam_role=<iam-role-arn>'
gzip compupdate off region 'us-west-2';
INSERT INTO customer_csk SELECT * FROM customer_nsk;
INSERT INTO customer_ilsk SELECT * FROM customer_nsk;

copy lineorder_nsk from 's3://awssampledbuswest2/ssbgz/lineorder' 
credentials 'aws_iam_role=<iam-role-arn>'
gzip compupdate off region 'us-west-2';
INSERT INTO lineorder_csk SELECT * FROM lineorder_nsk;
INSERT INTO lineorder_csk2 SELECT * FROM lineorder_nsk;
INSERT INTO lineorder_ilsk SELECT * FROM lineorder_nsk;

データロード後、 VACUUM FULL または VACUUM REINDEX と ANALYZE を完了させ、 SET enable_result_cache_for_session = off; としてから検証に入っています。

クエリの検証

ORDER BY

-- レコード数1億件未満のテーブル
-- プライマリキーでORDER BY
SELECT dateid FROM sales ORDER BY dateid;

-- EXPLAIN
XN Merge  (cost=1000000016724.67..1000000017155.81 rows=172456 width=2)
  Merge Key: dateid
  ->  XN Network  (cost=1000000016724.67..1000000017155.81 rows=172456 width=2)
        Send to leader
        ->  XN Sort  (cost=1000000016724.67..1000000017155.81 rows=172456 width=2)
              Sort Key: dateid
              ->  XN Seq Scan on sales_nsk  (cost=0.00..1724.56 rows=172456 width=2)
XN Merge  (cost=0.00..1724.56 rows=172456 width=2)
  Merge Key: dateid
  ->  XN Network  (cost=0.00..1724.56 rows=172456 width=2)
        Send to leader
        ->  XN Seq Scan on sales_csk  (cost=0.00..1724.56 rows=172456 width=2)
XN Merge  (cost=1000000016724.67..1000000017155.81 rows=172456 width=2)
  Merge Key: dateid
  ->  XN Network  (cost=1000000016724.67..1000000017155.81 rows=172456 width=2)
        Send to leader
        ->  XN Sort  (cost=1000000016724.67..1000000017155.81 rows=172456 width=2)
              Sort Key: dateid
              ->  XN Seq Scan on sales_ilsk  (cost=0.00..1724.56 rows=172456 width=2)

#	ソートキーなし	COMPOUND SORT KEY	INTERLEAVED SORT KEY
1	398ms	351ms	356ms
2	376ms	350ms	362ms
3	355ms	344ms	354ms
cost	1000000016724.67..1000000017155.81	0.00..1724.56	1000000016724.67..1000000017155.81

実行時間はほぼ横ばい。コストはCOMPOUND SORT KEYが一番低く、ソートキーなしとINTERLEAVED SORT KEYは同じ結果に。

-- レコード数1億件未満のテーブル
-- セカンダリキーでORDER BY
SELECT eventid FROM sales ORDER BY eventid;

-- EXPLAIN
XN Merge  (cost=1000000016724.67..1000000017155.81 rows=172456 width=4)
  Merge Key: eventid
  ->  XN Network  (cost=1000000016724.67..1000000017155.81 rows=172456 width=4)
        Send to leader
        ->  XN Sort  (cost=1000000016724.67..1000000017155.81 rows=172456 width=4)
              Sort Key: eventid
              ->  XN Seq Scan on sales_nsk  (cost=0.00..1724.56 rows=172456 width=4)
XN Merge  (cost=1000000016724.67..1000000017155.81 rows=172456 width=4)
  Merge Key: eventid
  ->  XN Network  (cost=1000000016724.67..1000000017155.81 rows=172456 width=4)
        Send to leader
        ->  XN Sort  (cost=1000000016724.67..1000000017155.81 rows=172456 width=4)
              Sort Key: eventid
              ->  XN Seq Scan on sales_csk  (cost=0.00..1724.56 rows=172456 width=4)
XN Merge  (cost=1000000016724.67..1000000017155.81 rows=172456 width=4)
  Merge Key: eventid
  ->  XN Network  (cost=1000000016724.67..1000000017155.81 rows=172456 width=4)
        Send to leader
        ->  XN Sort  (cost=1000000016724.67..1000000017155.81 rows=172456 width=4)
              Sort Key: eventid
              ->  XN Seq Scan on sales_ilsk  (cost=0.00..1724.56 rows=172456 width=4)

#	ソートキーなし	COMPOUND SORT KEY	INTERLEAVED SORT KEY
1	362ms	346ms	355ms
2	363ms	352ms	349ms
3	355ms	365ms	364ms
cost	1000000016724.67..1000000017155.81	1000000016724.67..1000000017155.81	1000000016724.67..1000000017155.81

実行時間はほぼ横ばい。コストも同じであった。

-- レコード数1億件未満のテーブル
-- プライマリとセカンダリでORDER BY
SELECT dateid, eventid FROM sales ORDER BY dateid, eventid;

-- EXPLAIN
XN Merge  (cost=1000000016724.67..1000000017155.81 rows=172456 width=6)
  Merge Key: dateid, eventid
  ->  XN Network  (cost=1000000016724.67..1000000017155.81 rows=172456 width=6)
        Send to leader
        ->  XN Sort  (cost=1000000016724.67..1000000017155.81 rows=172456 width=6)
              Sort Key: dateid, eventid
              ->  XN Seq Scan on sales_nsk  (cost=0.00..1724.56 rows=172456 width=6)
XN Merge  (cost=0.00..1724.56 rows=172456 width=6)
  Merge Key: dateid, eventid
  ->  XN Network  (cost=0.00..1724.56 rows=172456 width=6)
        Send to leader
        ->  XN Seq Scan on sales_csk  (cost=0.00..1724.56 rows=172456 width=6)
XN Merge  (cost=1000000016724.67..1000000017155.81 rows=172456 width=6)
  Merge Key: dateid, eventid
  ->  XN Network  (cost=1000000016724.67..1000000017155.81 rows=172456 width=6)
        Send to leader
        ->  XN Sort  (cost=1000000016724.67..1000000017155.81 rows=172456 width=6)
              Sort Key: dateid, eventid
              ->  XN Seq Scan on sales_ilsk  (cost=0.00..1724.56 rows=172456 width=6)

#	ソートキーなし	COMPOUND SORT KEY	INTERLEAVED SORT KEY
1	438ms	417ms	424ms
2	437ms	425ms	439ms
3	445ms	431ms	449ms
cost	1000000016724.67..1000000017155.81	0.00..1724.56	1000000016724.67..1000000017155.81

実行時間はほぼ横ばい。コストはCOMPOUND SORT KEYが一番低く、ソートキーなしとINTERLEAVED SORT KEYは同じ結果に。

-- レコード数1億件以上のテーブル
-- プライマリでORDER BY
SELECT lo_custkey FROM lineorder ORDER BY lo_custkey LIMIT 10000;

-- EXPLAIN
XN Merge  (cost=1000093487338.12..1000094987432.84 rows=600037888 width=4)
  Merge Key: lo_custkey
  ->  XN Network  (cost=1000093487338.12..1000094987432.84 rows=600037888 width=4)
        Send to leader
        ->  XN Sort  (cost=1000093487338.12..1000094987432.84 rows=600037888 width=4)
              Sort Key: lo_custkey
              ->  XN Seq Scan on lineorder_nsk  (cost=0.00..6000378.88 rows=600037888 width=4)
XN Merge  (cost=0.00..6000378.88 rows=600037888 width=4)
  Merge Key: lo_custkey
  ->  XN Network  (cost=0.00..6000378.88 rows=600037888 width=4)
        Send to leader
        ->  XN Seq Scan on lineorder_csk  (cost=0.00..6000378.88 rows=600037888 width=4)
XN Merge  (cost=1000093487338.12..1000094987432.84 rows=600037888 width=4)
  Merge Key: lo_custkey
  ->  XN Network  (cost=1000093487338.12..1000094987432.84 rows=600037888 width=4)
        Send to leader
        ->  XN Sort  (cost=1000093487338.12..1000094987432.84 rows=600037888 width=4)
              Sort Key: lo_custkey
              ->  XN Seq Scan on lineorder_ilsk  (cost=0.00..6000378.88 rows=600037888 width=4)

#	ソートキーなし	COMPOUND SORT KEY	INTERLEAVED SORT KEY
1	1.8s	97ms	1.4s
2	1.7s	93ms	1.5s
3	1.7s	56ms	1.5s
cost	1000093487338.12..1000094987432.84	0.00..6000378.88	1000093487338.12..1000094987432.84

実行時間はCOMPOUND SORT KEYが最も短いが、INTERLEAVED SORT KEYもソートキーなしより短い。コストはCOMPOUND SORT KEYが一番低く、ソートキーなしとINTERLEAVED SORT KEYは同じ結果に。

-- レコード数1億件以上のテーブル
-- セカンダリでORDER BY
SELECT lo_partkey FROM lineorder ORDER BY lo_partkey LIMIT 10000;

-- EXPLAIN
XN Merge  (cost=1000093487338.12..1000094987432.84 rows=600037888 width=4)
  Merge Key: lo_partkey
  ->  XN Network  (cost=1000093487338.12..1000094987432.84 rows=600037888 width=4)
        Send to leader
        ->  XN Sort  (cost=1000093487338.12..1000094987432.84 rows=600037888 width=4)
              Sort Key: lo_partkey
              ->  XN Seq Scan on lineorder_nsk  (cost=0.00..6000378.88 rows=600037888 width=4)
XN Merge  (cost=1000093487338.12..1000094987432.84 rows=600037888 width=4)
  Merge Key: lo_partkey
  ->  XN Network  (cost=1000093487338.12..1000094987432.84 rows=600037888 width=4)
        Send to leader
        ->  XN Sort  (cost=1000093487338.12..1000094987432.84 rows=600037888 width=4)
              Sort Key: lo_partkey
              ->  XN Seq Scan on lineorder_csk  (cost=0.00..6000378.88 rows=600037888 width=4)
XN Merge  (cost=1000093487338.12..1000094987432.84 rows=600037888 width=4)
  Merge Key: lo_partkey
  ->  XN Network  (cost=1000093487338.12..1000094987432.84 rows=600037888 width=4)
        Send to leader
        ->  XN Sort  (cost=1000093487338.12..1000094987432.84 rows=600037888 width=4)
              Sort Key: lo_partkey
              ->  XN Seq Scan on lineorder_ilsk  (cost=0.00..6000378.88 rows=600037888 width=4)

#	ソートキーなし	COMPOUND SORT KEY	INTERLEAVED SORT KEY
1	1.9s	1.4s	1.5s
2	1.7s	1.4s	1.4s
3	1.8s	1.5s	1.5s
cost	1000093487338.12..1000094987432.84	1000093487338.12..1000094987432.84	1000093487338.12..1000094987432.84

COMPOUND SORT KEYとINTERLEAVED SORT KEY間に差はなく、どちらもソートキーなしより実行時間は短かい。コストは同じ。

-- レコード数1億件以上のテーブル
-- プライマリとセカンダリでORDER BY
SELECT lo_custkey, lo_partkey FROM lineorder ORDER BY lo_custkey, lo_partkey LIMIT 10000;

-- EXPLAIN
XN Merge  (cost=1000093487338.12..1000094987432.84 rows=600037888 width=8)
  Merge Key: lo_custkey, lo_partkey
  ->  XN Network  (cost=1000093487338.12..1000094987432.84 rows=600037888 width=8)
        Send to leader
        ->  XN Sort  (cost=1000093487338.12..1000094987432.84 rows=600037888 width=8)
              Sort Key: lo_custkey, lo_partkey
              ->  XN Seq Scan on lineorder_nsk  (cost=0.00..6000378.88 rows=600037888 width=8)
XN Merge  (cost=0.00..6000378.88 rows=600037888 width=8)
  Merge Key: lo_custkey, lo_partkey
  ->  XN Network  (cost=0.00..6000378.88 rows=600037888 width=8)
        Send to leader
        ->  XN Seq Scan on lineorder_csk  (cost=0.00..6000378.88 rows=600037888 width=8)
XN Merge  (cost=1000093487338.12..1000094987432.84 rows=600037888 width=8)
  Merge Key: lo_custkey, lo_partkey
  ->  XN Network  (cost=1000093487338.12..1000094987432.84 rows=600037888 width=8)
        Send to leader
        ->  XN Sort  (cost=1000093487338.12..1000094987432.84 rows=600037888 width=8)
              Sort Key: lo_custkey, lo_partkey
              ->  XN Seq Scan on lineorder_ilsk  (cost=0.00..6000378.88 rows=600037888 width=8)

#	ソートキーなし	COMPOUND SORT KEY	INTERLEAVED SORT KEY
1	2.3s	69ms	1.7s
2	2.4s	66ms	1.8s
3	2.3s	61ms	1.7s
cost	1000093487338.12..1000094987432.84	0.00..6000378.88	1000093487338.12..1000094987432.84

プライマリキーのみのORDER BYと同じ結果になったが、よりその差が顕著となった。コストはCOMPOUND SORT KEYが一番低く、ソートキーなしとINTERLEAVED SORT KEYは同じ結果に。

GROUP BY

-- レコード数1億件未満のテーブル
-- プライマリキーでGROUP BY
SELECT dateid, count(*) FROM sales GROUP BY dateid;

-- EXPLAIN
XN HashAggregate  (cost=2586.84..2587.75 rows=364 width=2)
  ->  XN Seq Scan on sales_nsk  (cost=0.00..1724.56 rows=172456 width=2)
XN GroupAggregate  (cost=0.00..2587.71 rows=350 width=2)
  ->  XN Seq Scan on sales_csk  (cost=0.00..1724.56 rows=172456 width=2)
XN HashAggregate  (cost=2586.84..2587.75 rows=363 width=2)
  ->  XN Seq Scan on sales_ilsk  (cost=0.00..1724.56 rows=172456 width=2)

#	ソートキーなし	COMPOUND SORT KEY	INTERLEAVED SORT KEY
1	22ms	21ms	23ms
2	24ms	22ms	24ms
3	22ms	21ms	22ms
cost	2586.84..2587.75	0.00..2587.71	2586.84..2587.75
集計演算子	HashAggregate	GroupAggregate	HashAggregate

実行時間はほぼ横ばい。コストはCOMPOUND SORT KEYの初期コストが他と比べて低いが、トータルコストはほぼ同じになっている。

-- レコード数1億件未満のテーブル
-- セカンダリキーでGROUP BY
SELECT eventid, count(*) FROM sales GROUP BY eventid;

-- EXPLAIN
XN HashAggregate  (cost=2586.84..2606.31 rows=7787 width=4)
  ->  XN Seq Scan on sales_nsk  (cost=0.00..1724.56 rows=172456 width=4)
XN HashAggregate  (cost=2586.84..2606.42 rows=7834 width=4)
  ->  XN Seq Scan on sales_csk  (cost=0.00..1724.56 rows=172456 width=4)
XN HashAggregate  (cost=2586.84..2606.27 rows=7772 width=4)
  ->  XN Seq Scan on sales_ilsk  (cost=0.00..1724.56 rows=172456 width=4)

#	ソートキーなし	COMPOUND SORT KEY	INTERLEAVED SORT KEY
1	61ms	62ms	53ms
2	53ms	57ms	60ms
3	48ms	48ms	49ms
cost	2586.84..2606.31	2586.84..2606.42	2586.84..2606.27
集計演算子	HashAggregate	HashAggregate	HashAggregate

実行時間はほぼ横ばい。微妙にバラつきがあるが、コストもほぼ同じ。

-- レコード数1億件未満のテーブル
-- プライマリとセカンダリキーでGROUP BY
SELECT dateid, eventid, count(*) FROM sales GROUP BY dateid, eventid;

-- EXPLAIN
XN HashAggregate  (cost=3017.98..3061.09 rows=17246 width=6)
  ->  XN Seq Scan on sales_nsk  (cost=0.00..1724.56 rows=172456 width=6)
XN GroupAggregate  (cost=0.00..3061.09 rows=17246 width=6)
  ->  XN Seq Scan on sales_csk  (cost=0.00..1724.56 rows=172456 width=6)
XN HashAggregate  (cost=3017.98..3061.09 rows=17246 width=6)
  ->  XN Seq Scan on sales_ilsk  (cost=0.00..1724.56 rows=172456 width=6)

#	ソートキーなし	COMPOUND SORT KEY	INTERLEAVED SORT KEY
1	551ms	437ms	430ms
2	683ms	411ms	586ms
3	439ms	659ms	438ms
cost	3017.98..3061.09	0.00..3061.09	3017.98..3061.09
集計演算子	HashAggregate	GroupAggregate	HashAggregate

実行時間はほぼ横ばい。コストはCOMPOUND SORT KEYの初期コストが他と比べて低いが、トータルコストはほぼ同じになっている。

-- レコード数1億件以上のテーブル
-- プライマリキーでGROUP BY
SELECT lo_custkey, count(*) FROM lineorder GROUP BY lo_custkey;

-- EXPLAIN
XN HashAggregate  (cost=9000568.32..9004948.86 rows=1752214 width=4)
  ->  XN Seq Scan on lineorder_nsk  (cost=0.00..6000378.88 rows=600037888 width=4)
XN GroupAggregate  (cost=0.00..9005054.16 rows=1794335 width=4)
  ->  XN Seq Scan on lineorder_csk  (cost=0.00..6000378.88 rows=600037888 width=4)
XN HashAggregate  (cost=9000568.32..9005161.02 rows=1837079 width=4)
  ->  XN Seq Scan on lineorder_ilsk  (cost=0.00..6000378.88 rows=600037888 width=4)

#	ソートキーなし	COMPOUND SORT KEY	INTERLEAVED SORT KEY
1	13.2s	4.6s	8.5s
2	13.3s	4.6s	8.3s
3	13.3s	4.6s	8.4s
cost	9000568.32..9004948.86	0.00..9005054.16	9000568.32..9005161.02
集計演算子	HashAggregate	GroupAggregate	HashAggregate

実行速度はCOMPOUND SORT KEYが最も速く、ついでINTERLEAVED SORT KEYが速い。トータルコストで見るとINTERLEAVED SORT KEY > COMPOUND SORT KEY > ソートキーなしとなっており、コスパが高いのはCOMPOUND SORT KEY。

-- レコード数1億件以上のテーブル
-- セカンダリキーでGROUP BY
SELECT lo_partkey, count(*) FROM lineorder GROUP BY lo_partkey;

-- EXPLAIN
XN HashAggregate  (cost=9000568.32..9002981.02 rows=965078 width=4)
  ->  XN Seq Scan on lineorder_nsk  (cost=0.00..6000378.88 rows=600037888 width=4)
XN HashAggregate  (cost=9000568.32..9003137.57 rows=1027699 width=4)
  ->  XN Seq Scan on lineorder_csk  (cost=0.00..6000378.88 rows=600037888 width=4)
XN HashAggregate  (cost=9000568.32..9003314.96 rows=1098655 width=4)
  ->  XN Seq Scan on lineorder_ilsk  (cost=0.00..6000378.88 rows=600037888 width=4)

#	ソートキーなし	COMPOUND SORT KEY	INTERLEAVED SORT KEY
1	17.4s	15.8s	7.4s
2	17.2s	15.3s	7.3s
3	16.3s	16.1s	7.3s
cost	9000568.32..9002981.02	9000568.32..9003137.57	9000568.32..9003314.96
集計演算子	HashAggregate	HashAggregate	HashAggregate

レコード数1億件以上のテーブルかつ、セカンダリのGROUP BYでようやくINTERLEAVED SORT KEYの長所が出た。コストは一番かかっているものの、実行時間が一番短い結果となった。

-- レコード数1億件以上のテーブル
-- プライマリとセカンダリキーでGROUP BY
SELECT lo_custkey, lo_partkey, count(*) FROM lineorder GROUP BY lo_custkey, lo_partkey LIMIT 10000;

-- EXPLAIN
XN HashAggregate  (cost=10500663.04..10650672.51 rows=60003789 width=8)
  ->  XN Seq Scan on lineorder_nsk  (cost=0.00..6000378.88 rows=600037888 width=8)
XN GroupAggregate  (cost=0.00..10650672.51 rows=60003789 width=8)
  ->  XN Seq Scan on lineorder_csk  (cost=0.00..6000378.88 rows=600037888 width=8)
XN HashAggregate  (cost=10500663.04..10650672.51 rows=60003789 width=8)
  ->  XN Seq Scan on lineorder_ilsk  (cost=0.00..6000378.88 rows=600037888 width=8)

#	ソートキーなし	COMPOUND SORT KEY	INTERLEAVED SORT KEY
1	37.7s	60ms	33.7s
2	36.8s	67ms	35.1s
3	38.0s	62ms	34.6s
cost	10500663.04..10650672.51	0.00..10650672.51	10500663.04..10650672.51
集計演算子	HashAggregate	GroupAggregate	HashAggregate

INTERLEAVED SORT KEYで2つのカラムでGROUP BYすると極端に遅くなった。逆にCOMPOUND SORT KEYは非常にパフォーマンスが高い。初期コストはCOMPOUND SORT KEYが一番低いが、トータルコストは同じである。

WHERE

-- レコード数1億件未満のテーブル
-- プライマリキーでWHERE
SELECT dateid FROM sales WHERE dateid IN ('2000', '2050', '1850');

-- EXPLAIN
XN Seq Scan on sales_nsk  (cost=0.00..3017.98 rows=1395 width=2)
  Filter: ((dateid = 1850::smallint) OR (dateid = 2000::smallint) OR (dateid = 2050::smallint))
XN Seq Scan on sales_csk  (cost=0.00..24.44 rows=1397 width=2)
  Filter: ((dateid = 1850::smallint) OR (dateid = 2000::smallint) OR (dateid = 2050::smallint))
XN Seq Scan on sales_ilsk  (cost=0.00..3017.98 rows=1398 width=2)
  Filter: ((dateid = 1850::smallint) OR (dateid = 2000::smallint) OR (dateid = 2050::smallint))

#	ソートキーなし	COMPOUND SORT KEY	INTERLEAVED SORT KEY
1	32ms	35ms	30ms
2	25ms	33ms	27ms
3	27ms	27ms	30ms
cost	0.00..3017.98	0.00..24.44	0.00..3017.98

速度はほぼ横ばい。コストはCOMPOUND SORT KEYが一番低い。

-- レコード数1億件未満のテーブル
-- セカンダリキーでWHERE
SELECT eventid FROM sales WHERE eventid BETWEEN 10 AND 1000;

-- EXPLAIN
XN Seq Scan on sales_nsk  (cost=0.00..2586.84 rows=19865 width=4)
  Filter: ((eventid <= 1000) AND (eventid >= 10))
XN Seq Scan on sales_csk  (cost=0.00..2586.84 rows=19710 width=4)
  Filter: ((eventid <= 1000) AND (eventid >= 10))
XN Seq Scan on sales_ilsk  (cost=0.00..2586.84 rows=19153 width=4)
  Filter: ((eventid <= 1000) AND (eventid >= 10))

#	ソートキーなし	COMPOUND SORT KEY	INTERLEAVED SORT KEY
1	73ms	73ms	75ms
2	72ms	70ms	69ms
3	65ms	63ms	67ms
cost	0.00..2586.84	0.00..2586.84	0.00..2586.84

速度もコストも横ばいの結果となった。

-- レコード数1億件未満のテーブル
-- プライマリキーとセカンダリキーでWHERE
SELECT dateid, eventid FROM sales WHERE dateid BETWEEN 1000 AND 2000 AND eventid BETWEEN 10 AND 1000;

-- EXPLAIN
XN Seq Scan on sales_nsk  (cost=0.00..3449.12 rows=9480 width=6)
  Filter: ((eventid <= 1000) AND (eventid >= 10) AND (dateid <= 2000) AND (dateid >= 1000))
XN Seq Scan on sales_csk  (cost=0.00..1639.04 rows=9366 width=6)
  Filter: ((eventid <= 1000) AND (eventid >= 10) AND (dateid <= 2000) AND (dateid >= 1000))
XN Seq Scan on sales_ilsk  (cost=0.00..3449.12 rows=9093 width=6)
  Filter: ((eventid <= 1000) AND (eventid >= 10) AND (dateid <= 2000) AND (dateid >= 1000))

#	ソートキーなし	COMPOUND SORT KEY	INTERLEAVED SORT KEY
1	64ms	48ms	65ms
2	53ms	49ms	51ms
3	46ms	55ms	54ms
cost	0.00..3449.12	0.00..1639.04	0.00..3449.12

速度はほぼ横ばい。コストはCOMPOUND SORT KEYが一番低い。

-- レコード数1億件以上のテーブル
-- プライマリキーでWHERE
SELECT lo_custkey FROM lineorder WHERE lo_custkey IN ('123434', '123', '3415678');

-- EXPLAIN
XN Seq Scan on lineorder_nsk  (cost=0.00..10500663.04 rows=1028 width=4)
  Filter: ((lo_custkey = 123) OR (lo_custkey = 123434) OR (lo_custkey = 3415678))
XN Seq Scan on lineorder_csk  (cost=0.00..17.56 rows=1004 width=4)
  Filter: ((lo_custkey = 123) OR (lo_custkey = 123434) OR (lo_custkey = 3415678))
XN Seq Scan on lineorder_ilsk  (cost=0.00..10500663.04 rows=980 width=4)
  Filter: ((lo_custkey = 123) OR (lo_custkey = 123434) OR (lo_custkey = 3415678))

#	ソートキーなし	COMPOUND SORT KEY	INTERLEAVED SORT KEY
1	1.4s	22ms	143ms
2	1.3s	25ms	135m
3	1.3s	24ms	134ms
cost	0.00..10500663.04	0.00..17.56	0.00..10500663.04

速度はCOMPOUND SORT KEYが最も速く、次いでINTERLEAVED SORT KEYが速い。コストはCOMPOUND SORT KEYが一番低い。

-- レコード数1億件以上のテーブル
-- セカンダリキーでWHERE
SELECT lo_partkey FROM lineorder WHERE lo_partkey BETWEEN 500 AND 1000;

-- EXPLAIN
XN Seq Scan on lineorder_nsk  (cost=0.00..9000568.32 rows=309378 width=4)
  Filter: ((lo_partkey <= 1000) AND (lo_partkey >= 500))
XN Seq Scan on lineorder_csk  (cost=0.00..9000568.32 rows=308579 width=4)
  Filter: ((lo_partkey <= 1000) AND (lo_partkey >= 500))
XN Seq Scan on lineorder_ilsk  (cost=0.00..9000568.32 rows=305233 width=4)
  Filter: ((lo_partkey <= 1000) AND (lo_partkey >= 500))

#	ソートキーなし	COMPOUND SORT KEY	INTERLEAVED SORT KEY
1	1.3s	2.3s	575ms
2	1.3s	1.8s	579ms
3	1.3s	2.3s	594ms
cost	0.00..9000568.32	0.00..9000568.32	0.00..9000568.32

GROUP BYの時と同じく、レコード数1億件以上のテーブルかつ、セカンダリキーでINTERLEAVED SORT KEYの長所が出た。COMPOUND SORT KEYに関しては、ソートなしよりも遅い結果となった。コストは同じである。

-- レコード数1億件以上のテーブル
-- プライマリキーとセカンダリキーでWHERE
SELECT lo_custkey, lo_partkey FROM lineorder WHERE lo_custkey BETWEEN 500 AND 2000 AND lo_partkey BETWEEN 500 AND 1000;

-- EXPLAIN
XN Seq Scan on lineorder_nsk  (cost=0.00..12000757.76 rows=157 width=8)
  Filter: ((lo_custkey <= 2000) AND (lo_custkey >= 500) AND (lo_partkey <= 1000) AND (lo_partkey >= 500))
XN Seq Scan on lineorder_csk  (cost=0.00..6017.71 rows=155 width=8)
  Filter: ((lo_custkey <= 2000) AND (lo_custkey >= 500) AND (lo_partkey <= 1000) AND (lo_partkey >= 500))
XN Seq Scan on lineorder_ilsk  (cost=0.00..12000757.76 rows=151 width=8)
  Filter: ((lo_custkey <= 2000) AND (lo_custkey >= 500) AND (lo_partkey <= 1000) AND (lo_partkey >= 500))

#	ソートキーなし	COMPOUND SORT KEY	INTERLEAVED SORT KEY
1	1.6s	25ms	24ms
2	1.6s	23ms	23ms
3	1.7s	23ms	22ms
cost	0.00..12000757.76	0.00..6017.71	0.00..12000757.76

COMPOUND SORT KEYもINTERLEAVED SORT KEYもほぼ同じくらい高速。GROUP BYと違って、WHEREはINTERLEAVED SORT KEYでも複数のカラムを選択してもパフォーマンスは落ちない。コストはCOMPOUND SORT KEYが一番低い。

JOIN

-- レコード数1億件未満のテーブル
-- プライマリキーでJOIN
SELECT s.dateid, s.eventid, s.salesid, d.caldate FROM sales_nsk s LEFT JOIN date d ON s.dateid = d.dateid;

-- EXPLAIN
XN Hash Left Join DS_DIST_NONE  (cost=4.56..5609.38 rows=172456 width=14)
  Hash Cond: ("outer".dateid = "inner".dateid)
  ->  XN Seq Scan on sales_nsk s  (cost=0.00..1724.56 rows=172456 width=10)
  ->  XN Hash  (cost=3.65..3.65 rows=365 width=6)
        ->  XN Seq Scan on date_nsk d  (cost=0.00..3.65 rows=365 width=6)
XN Merge Left Join DS_DIST_NONE  (cost=0.00..21.21 rows=740 width=14)
  Merge Cond: ("outer".dateid = "inner".dateid)
  ->  XN Seq Scan on sales_csk s  (cost=0.00..7.40 rows=740 width=10)
  ->  XN Seq Scan on date_csk d  (cost=0.00..3.65 rows=365 width=6)
XN Hash Left Join DS_DIST_NONE  (cost=4.56..28.61 rows=740 width=14)
  Hash Cond: ("outer".dateid = "inner".dateid)
  ->  XN Seq Scan on sales_csk2 s  (cost=0.00..7.40 rows=740 width=10)
  ->  XN Hash  (cost=3.65..3.65 rows=365 width=6)
        ->  XN Seq Scan on date_csk d  (cost=0.00..3.65 rows=365 width=6)
XN Hash Left Join DS_DIST_NONE  (cost=4.56..29.26 rows=760 width=14)
  Hash Cond: ("outer".dateid = "inner".dateid)
  ->  XN Seq Scan on sales_ilsk s  (cost=0.00..7.60 rows=760 width=10)
  ->  XN Hash  (cost=3.65..3.65 rows=365 width=6)
        ->  XN Seq Scan on date_ilsk d  (cost=0.00..3.65 rows=365 width=6)

#	ソートキーなし	COMPOUND SORT KEY	COMPOUND SORT KEY2	INTERLEAVED SORT KEY
1	681ms	655ms	687ms	670ms
2	682ms	679ms	685ms	683ms
3	665ms	684ms	677ms	678ms
cost	4.56..5609.38	0.00..21.21	4.56..28.61	4.56..29.26
JOIN演算子	Hash Left Join	Merge Left Join	Hash Left Join	Hash Left Join
データ再分散	DS_DIST_NONE	DS_DIST_NONE	DS_DIST_NONE	DS_DIST_NONE

速度は横ばいだが、演算の効率性にはかなり差が出ている。ソートキーなしにコストが最もかかっており、Merge Left Joinが採択されたCOMPOUND SORT KEYはコストが最も小さい。

-- レコード数1億件未満のテーブル
-- セカンダリキーでJOIN
SELECT s.dateid, s.eventid, s.salesid, e.eventname FROM sales s LEFT JOIN event e ON s.eventid = e.eventid;

-- EXPLAIN
XN Hash Left Join DS_BCAST_INNER  (cost=2.50..664036693.70 rows=172456 width=328)
  Hash Cond: ("outer".eventid = "inner".eventid)
  ->  XN Seq Scan on sales_nsk s  (cost=0.00..1724.56 rows=172456 width=10)
  ->  XN Hash  (cost=2.00..2.00 rows=200 width=322)
        ->  XN Seq Scan on event_nsk e  (cost=0.00..2.00 rows=200 width=322)
XN Hash Right Join DS_DIST_INNER  (cost=9.50..76000014.20 rows=760 width=328)
  Inner Dist Key: s.eventid
  Hash Cond: ("outer".eventid = "inner".eventid)
  ->  XN Seq Scan on event_csk e  (cost=0.00..2.00 rows=200 width=322)
  ->  XN Hash  (cost=7.60..7.60 rows=760 width=10)
        ->  XN Seq Scan on sales_csk s  (cost=0.00..7.60 rows=760 width=10)
XN Hash Right Join DS_DIST_INNER  (cost=9.50..76000014.20 rows=760 width=328)
  Inner Dist Key: s.eventid
  Hash Cond: ("outer".eventid = "inner".eventid)
  ->  XN Seq Scan on event_csk e  (cost=0.00..2.00 rows=200 width=322)
  ->  XN Hash  (cost=7.60..7.60 rows=760 width=10)
        ->  XN Seq Scan on sales_csk s  (cost=0.00..7.60 rows=760 width=10)
XN Hash Right Join DS_DIST_INNER  (cost=9.50..76000014.20 rows=760 width=328)
  Inner Dist Key: s.eventid
  Hash Cond: ("outer".eventid = "inner".eventid)
  ->  XN Seq Scan on event_ilsk e  (cost=0.00..2.00 rows=200 width=322)
  ->  XN Hash  (cost=7.60..7.60 rows=760 width=10)
        ->  XN Seq Scan on sales_ilsk s  (cost=0.00..7.60 rows=760 width=10)

#	ソートキーなし	COMPOUND SORT KEY	COMPOUND SORT KEY2	INTERLEAVED SORT KEY
1	699ms	669ms	676ms	720ms
2	752ms	694ms	689ms	680ms
3	691ms	706ms	696ms	675ms
cost	2.50..664036693.70	9.50..76000014.20	9.50..76000014.20	9.50..76000014.20
JOIN演算子	Hash Left Join	Hash Right Join	Hash Right Join	Hash Right Join
データ再分散	DS_BCAST_INNER	DS_DIST_INNER	DS_DIST_INNER	DS_DIST_INNER

速度は横ばいだが、唯一のMerge Left JoinかつDS_BCAST_INNERのソートキーなしのコストが高い。他は同じクエリ計画だった。

-- レコード数1億件未満のテーブル
-- プライマリとセカンダリキーでJOIN
SELECT s.dateid, s.eventid, s.salesid, e.eventname FROM sales s LEFT JOIN event e ON s.dateid = e.dateid AND s.eventid = e.eventid;

-- EXPLAIN
XN Hash Left Join DS_BCAST_INNER  (cost=3.00..664071572.37 rows=172456 width=328)
  Hash Cond: (("outer".eventid = "inner".eventid) AND ("outer".dateid = "inner".dateid))
  ->  XN Seq Scan on sales_nsk s  (cost=0.00..1724.56 rows=172456 width=10)
  ->  XN Hash  (cost=2.00..2.00 rows=200 width=324)
        ->  XN Seq Scan on event_nsk e  (cost=0.00..2.00 rows=200 width=324)
XN Hash Right Join DS_DIST_OUTER  (cost=11.40..332000018.41 rows=760 width=328)
  Outer Dist Key: e.dateid
  Hash Cond: (("outer".eventid = "inner".eventid) AND ("outer".dateid = "inner".dateid))
  ->  XN Seq Scan on event_csk e  (cost=0.00..2.00 rows=200 width=324)
  ->  XN Hash  (cost=7.60..7.60 rows=760 width=10)
        ->  XN Seq Scan on sales_csk s  (cost=0.00..7.60 rows=760 width=10)
XN Hash Right Join DS_DIST_OUTER  (cost=11.40..332000018.41 rows=760 width=328)
  Outer Dist Key: e.dateid
  Hash Cond: (("outer".eventid = "inner".eventid) AND ("outer".dateid = "inner".dateid))
  ->  XN Seq Scan on event_csk e  (cost=0.00..2.00 rows=200 width=324)
  ->  XN Hash  (cost=7.60..7.60 rows=760 width=10)
        ->  XN Seq Scan on sales_csk s  (cost=0.00..7.60 rows=760 width=10)
XN Hash Right Join DS_DIST_OUTER  (cost=11.40..332000018.41 rows=760 width=328)
  Outer Dist Key: e.dateid
  Hash Cond: (("outer".eventid = "inner".eventid) AND ("outer".dateid = "inner".dateid))
  ->  XN Seq Scan on event_csk e  (cost=0.00..2.00 rows=200 width=324)
  ->  XN Hash  (cost=7.60..7.60 rows=760 width=10)
        ->  XN Seq Scan on sales_csk s  (cost=0.00..7.60 rows=760 width=10)

#	ソートキーなし	COMPOUND SORT KEY	COMPOUND SORT KEY2	INTERLEAVED SORT KEY
1	557ms	575ms	574ms	568ms
2	565ms	577ms	561ms	572ms
3	592ms	603ms	603ms	570ms
cost	3.00..664071572.37	11.40..332000018.41	11.40..332000018.41	11.40..332000018.41
JOIN演算子	Hash Left Join	Hash Right Join	Hash Right Join	Hash Right Join
データ再分散	DS_BCAST_INNER	DS_DIST_OUTER	DS_DIST_OUTER	DS_DIST_OUTER

1つ前の結果とほぼ同じだが、プライマリのみよりもプライマリとセカンダリキーでJOINした方がコストが高くなってる。

-- レコード数1億件以上のテーブル
-- プライマリキーでJOIN
SELECT l.lo_custkey, l.lo_orderdate, c.c_name FROM lineorder l LEFT JOIN customer c ON l.lo_custkey = c.c_custkey LIMIT 10000;

-- EXPLAIN
XN Hash Left Join DS_DIST_NONE  (cost=2.88..145509958.27 rows=600037888 width=63)
  Hash Cond: ("outer".lo_custkey = "inner".c_custkey)
  ->  XN Seq Scan on lineorder_nsk l  (cost=0.00..6000378.88 rows=600037888 width=8)
  ->  XN Hash  (cost=2.30..2.30 rows=230 width=59)
        ->  XN Seq Scan on customer_nsk c  (cost=0.00..2.30 rows=230 width=59)
XN Merge Left Join DS_DIST_NONE  (cost=0.00..7501322.73 rows=600037888 width=63)
  Merge Cond: ("outer".lo_custkey = "inner".c_custkey)
  ->  XN Seq Scan on lineorder_csk l  (cost=0.00..6000378.88 rows=600037888 width=8)
  ->  XN Seq Scan on customer_csk c  (cost=0.00..2.30 rows=230 width=59)
XN Hash Left Join DS_DIST_NONE  (cost=2.88..145509965.91 rows=600037888 width=63)
  Hash Cond: ("outer".lo_custkey = "inner".c_custkey)
  ->  XN Seq Scan on lineorder_csk2 l  (cost=0.00..6000378.88 rows=600037888 width=8)
  ->  XN Hash  (cost=2.30..2.30 rows=230 width=59)
        ->  XN Seq Scan on customer_csk c  (cost=0.00..2.30 rows=230 width=59)
XN Hash Left Join DS_DIST_NONE  (cost=2.88..145509981.34 rows=600037888 width=63)
  Hash Cond: ("outer".lo_custkey = "inner".c_custkey)
  ->  XN Seq Scan on lineorder_ilsk l  (cost=0.00..6000378.88 rows=600037888 width=8)
  ->  XN Hash  (cost=2.30..2.30 rows=230 width=59)
        ->  XN Seq Scan on customer_ilsk c  (cost=0.00..2.30 rows=230 width=59)

#	ソートキーなし	COMPOUND SORT KEY	COMPOUND SORT KEY2	INTERLEAVED SORT KEY
1	89ms	58ms	65ms	65ms
2	73ms	64ms	75ms	78ms
3	84ms	65ms	63ms	58ms
cost	2.88..145509958.27	0.00..7501322.73	2.88..145509965.91	2.88..145509981.34
JOIN演算子	Hash Left Join	Merge Left Join	Hash Left Join	Hash Left Join
データ再分散	DS_DIST_NONE	DS_DIST_NONE	DS_DIST_NONE	DS_DIST_NONE

レコード数が1億件を超えると顕著に差が出る。速度はCOMPOUND SORT KEYが最も速く、ソートキーなしとINTERLEAVED SORT KEYが同率、COMPOUND SORT KEY2は唯一DS_BCAST_INNERが採択され、ソートキーなしより3倍近く遅くなった。

再検証ではなんと、レコード数1億件以上の方が1億件未満のテーブルよりも高速になっていた。内部のアルゴリズムが変わったのか、どのカラムを分散キーに指定すべきかがより重要になったのか……？ソートキーはもはやJOINに関係なさそうである。

-- レコード数1億件以上のテーブル
-- セカンダリキーでJOIN
SELECT l.lo_custkey, l.lo_orderdate, p.p_name FROM lineorder l LEFT JOIN part p ON l.lo_partkey = p.p_partkey LIMIT 10000;

-- EXPLAIN
XN Hash Left Join DS_BCAST_INNER  (cost=3.25..329911925.59 rows=600037888 width=59)
  Hash Cond: ("outer".lo_partkey = "inner".p_partkey)
  ->  XN Seq Scan on lineorder_nsk l  (cost=0.00..6000378.88 rows=600037888 width=12)
  ->  XN Hash  (cost=2.60..2.60 rows=260 width=55)
        ->  XN Seq Scan on part_nsk p  (cost=0.00..2.60 rows=260 width=55)
XN Hash Left Join DS_BCAST_INNER  (cost=3.25..329911904.27 rows=600037888 width=59)
  Hash Cond: ("outer".lo_partkey = "inner".p_partkey)
  ->  XN Seq Scan on lineorder_csk l  (cost=0.00..6000378.88 rows=600037888 width=12)
  ->  XN Hash  (cost=2.60..2.60 rows=260 width=55)
        ->  XN Seq Scan on part_csk p  (cost=0.00..2.60 rows=260 width=55)
XN Hash Left Join DS_BCAST_INNER  (cost=3.25..329912008.52 rows=600037888 width=59)
  Hash Cond: ("outer".lo_partkey = "inner".p_partkey)
  ->  XN Seq Scan on lineorder_csk2 l  (cost=0.00..6000378.88 rows=600037888 width=12)
  ->  XN Hash  (cost=2.60..2.60 rows=260 width=55)
        ->  XN Seq Scan on part_csk p  (cost=0.00..2.60 rows=260 width=55)
XN Hash Left Join DS_BCAST_INNER  (cost=3.25..329911898.94 rows=600037888 width=59)
  Hash Cond: ("outer".lo_partkey = "inner".p_partkey)
  ->  XN Seq Scan on lineorder_ilsk l  (cost=0.00..6000378.88 rows=600037888 width=12)
  ->  XN Hash  (cost=2.60..2.60 rows=260 width=55)
        ->  XN Seq Scan on part_ilsk p  (cost=0.00..2.60 rows=260 width=55)

#	ソートキーなし	COMPOUND SORT KEY	COMPOUND SORT KEY2	INTERLEAVED SORT KEY
1	69ms	86ms	69ms	80ms
2	70ms	96ms	65ms	75ms
3	64ms	65ms	60ms	68ms
cost	3.25..329911925.59	3.25..329911904.27	3.25..329912008.52	3.25..329911898.94
JOIN演算子	Hash Left Join	Hash Left Join	Hash Left Join	Hash Left Join
データ再分散	DS_BCAST_INNER	DS_BCAST_INNER	DS_BCAST_INNER	DS_BCAST_INNER

~~Merge Left JoinしているCOMPOUND SORT KEY2が速度・コストともにダントツのパフォーマンスで、他はあまり差がない。~~

上と同様、やはり分散キーが適切ならJOINが速くなる模様。

-- レコード数1億件以上のテーブル
-- プライマリとセカンダリキーでJOIN
SELECT l.lo_custkey, l.lo_orderdate FROM lineorder l LEFT JOIN lineorder l2 ON l.lo_custkey = l2.lo_custkey AND l.lo_partkey = l2.lo_partkey LIMIT 10000;

-- EXPLAIN
XN Hash Left Join DS_DIST_NONE  (cost=9000568.32..7230458601.30 rows=600037888 width=8)
  Hash Cond: (("outer".lo_custkey = "inner".lo_custkey) AND ("outer".lo_partkey = "inner".lo_partkey))
  ->  XN Seq Scan on lineorder_nsk l  (cost=0.00..6000378.88 rows=600037888 width=12)
  ->  XN Hash  (cost=6000378.88..6000378.88 rows=600037888 width=8)
        ->  XN Seq Scan on lineorder_nsk l2  (cost=0.00..6000378.88 rows=600037888 width=8)
XN Merge Left Join DS_DIST_NONE  (cost=0.00..18003367.66 rows=600037888 width=8)
  Merge Cond: (("outer".lo_custkey = "inner".lo_custkey) AND ("outer".lo_partkey = "inner".lo_partkey))
  ->  XN Seq Scan on lineorder_csk l  (cost=0.00..6000378.88 rows=600037888 width=12)
  ->  XN Seq Scan on lineorder_csk l2  (cost=0.00..6000378.88 rows=600037888 width=8)
XN Hash Left Join DS_DIST_NONE  (cost=9000568.32..7230458594.08 rows=600037888 width=8)
  Hash Cond: (("outer".lo_custkey = "inner".lo_custkey) AND ("outer".lo_partkey = "inner".lo_partkey))
  ->  XN Seq Scan on lineorder_csk2 l  (cost=0.00..6000378.88 rows=600037888 width=12)
  ->  XN Hash  (cost=6000378.88..6000378.88 rows=600037888 width=8)
        ->  XN Seq Scan on lineorder_csk l2  (cost=0.00..6000378.88 rows=600037888 width=8)
XN Hash Left Join DS_DIST_NONE  (cost=9000568.32..7230458627.72 rows=600037888 width=8)
  Hash Cond: (("outer".lo_custkey = "inner".lo_custkey) AND ("outer".lo_partkey = "inner".lo_partkey))
  ->  XN Seq Scan on lineorder_ilsk l  (cost=0.00..6000378.88 rows=600037888 width=12)
  ->  XN Hash  (cost=6000378.88..6000378.88 rows=600037888 width=8)
        ->  XN Seq Scan on lineorder_ilsk l2  (cost=0.00..6000378.88 rows=600037888 width=8)

#	ソートキーなし	COMPOUND SORT KEY	COMPOUND SORT KEY2	INTERLEAVED SORT KEY
1	26.0s	69ms	25.3s	24.7s
2	25.9s	65ms	25.1s	25.2s
3	26.9s	54ms	25.2s	25.0s
cost	9000568.32..7230458601.30	0.00..18003367.66	9000568.32..7230458594.08	9000568.32..7230458627.72
JOIN演算子	Hash Left Join	Merge Left Join	Hash Left Join	Hash Left Join
データ再分散	DS_DIST_NONE	DS_DIST_NONE	DS_DIST_OUTER	DS_DIST_NONE

いい感じのテーブルがなかったため、同じテーブルをJOINしている。Merge Left Joinが採択されたCOMPOUND SORT KEYのみ100msを切った。

Merge Joinの条件

-- レコード数1億件以上のテーブル
-- プライマリキーでJOIN
SELECT l.lo_custkey, l.lo_orderdate, c.c_name FROM lineorder_csk l LEFT JOIN customer_csk c ON l.lo_custkey = c.c_custkey LIMIT 10000;
SELECT l.lo_custkey, l.lo_orderdate, c.c_name FROM lineorder_csk l LEFT JOIN (SELECT c_custkey, c_name FROM customer_csk ) c ON l.lo_custkey = c.c_custkey LIMIT 10000;
SELECT l.lo_custkey, l.lo_orderdate, c.c_name FROM lineorder_csk l LEFT JOIN customer_csk c ON l.lo_custkey::text = c.c_custkey::text LIMIT 10000;

-- EXPLAIN
XN Merge Left Join DS_DIST_NONE  (cost=0.00..13538352.48 rows=600037888 width=30)
  Merge Cond: ("outer".lo_custkey = "inner".c_custkey)
  ->  XN Seq Scan on lineorder_csk l  (cost=0.00..6000378.88 rows=600037888 width=8)
  ->  XN Seq Scan on customer_csk c  (cost=0.00..30000.00 rows=3000000 width=26)
XN Merge Left Join DS_DIST_NONE  (cost=0.00..13538352.48 rows=600037888 width=30)
  Merge Cond: ("outer".lo_custkey = "inner".c_custkey)
  ->  XN Seq Scan on lineorder_csk l  (cost=0.00..6000378.88 rows=600037888 width=8)
  ->  XN Seq Scan on customer_csk  (cost=0.00..30000.00 rows=3000000 width=26)
XN Hash Left Join DS_BCAST_INNER  (cost=37500.00..6570354213173.60 rows=9000568320000 width=30)
  Hash Cond: ((("outer".lo_custkey)::character varying)::text = (("inner".c_custkey)::character varying)::text)
  ->  XN Seq Scan on lineorder_csk l  (cost=0.00..6000378.88 rows=600037888 width=8)
  ->  XN Hash  (cost=30000.00..30000.00 rows=3000000 width=26)
        ->  XN Seq Scan on customer_csk c  (cost=0.00..30000.00 rows=3000000 width=26)

#	加工なし	サブクエリ	データ型変更
1	62ms	82ms	1.2s
2	99ms	81ms	1.2s
3	80ms	81ms	1.2s
cost	0.00..13538352.48	0.00..13538352.48	37500.00..6570354213173.60
JOIN演算子	Merge Left Join	Merge Left Join	Hash Left Join

Merge Left Joinはサブクエリでも実行されるが、カラムを加工してしまうと採択されなくなる。

【Redshift】クエリパターンとパフォーマンスから学ぶCompound SortKeyとInterleaved SortKeyのユースケース

目次

Compound Sort KeyとInterleaved Sort Keyの特徴

Compound Sort Key（複合キー）

Interleaved Sort Key（インターリーブソートキー）

検証結果の結論

検証前の予備知識とサンプルテーブル

クエリの検証

ORDER BY

GROUP BY

WHERE

JOIN

Merge Joinの条件

参照

イベント

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【4/18リモート】クラスメソッドのフリーランスエンジニア会社説明会〜AWS/システムアーキテクト/Android案件特集〜を開催します

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/19（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

EVENT【4/30（火）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/9（木）】組織的なクラウド統制はじめの一歩

EVENT【4/24（水）リモート】会社員からフリーランスエンジニアへ、変化の時の体験談 [パネルトーク] フリーランストーク#10

【Redshift】クエリパターンとパフォーマンスから学ぶCompound SortKeyとInterleaved SortKeyのユースケース

目次

Compound Sort KeyとInterleaved Sort Keyの特徴

Compound Sort Key（複合キー）

Interleaved Sort Key（インターリーブソートキー）

検証結果の結論

検証前の予備知識とサンプルテーブル

クエリの検証

ORDER BY

GROUP BY

WHERE

JOIN

Merge Joinの条件

参照

イベント

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【4/18リモート】クラスメソッドのフリーランスエンジニア会社説明会 〜AWS/システムアーキテクト/Android案件特集〜 を開催します

EVENT【5/10（金） 名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/19（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

EVENT【4/30（火）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/9（木）】組織的なクラウド統制 はじめの一歩

EVENT【4/24（水）リモート】会社員からフリーランスエンジニアへ、変化の時の体験談 [パネルトーク] フリーランストーク#10

EVENT【4/18リモート】クラスメソッドのフリーランスエンジニア会社説明会〜AWS/システムアーキテクト/Android案件特集〜を開催します

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【5/9（木）】組織的なクラウド統制はじめの一歩