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

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

#パフォーマンス
#ビッグデータ
#データベース
#Amazon Redshift
#AWS
春田拓海 | Takumi

春田拓海 | Takumi

facebook logohatena logotwitter logo
Clock Icon2019.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の条件

  1. INNER JOIN と OUTER JOIN のみで使用される。 FULL JOIN では使われない
  2. 両テーブルの結合カラムが 分散キー であり、かつ Compound Sort Keyのプライマリ を含んでいること
  3. 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億件に満たない場合は以下のような方針がベターだと感じました。

  1. Compound Sort Keyベースで設計することを考え、「プライマリ・セカンダリの位置」と「SQL（JOINやWHERE）」の関係性をすり合わせていく
  2. JOINを使用しているテーブルに対しては、分散キーにCompound Sort Keyのプライマリを指定できるかどうか考える　→　Merge Joinできれば大幅なパフォーマンス向上につながる可能性
  3. 実際に運用してみて、明らかにボトルネックがありそうなテーブルに関しては、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はサブクエリでも実行されるが、カラムを加工してしまうと採択されなくなる。

参照

Share this article

facebook logohatena logotwitter logo

関連記事

[レポート] DynamoDB の詳細な設計が学べるビルダーズセッション「Applied data modeling with Amazon DynamoDB [REPEAT]」 #DAT401-R #AWSreInvent

[レポート] DynamoDB の詳細な設計が学べるビルダーズセッション「Applied data modeling with Amazon DynamoDB [REPEAT]」 #DAT401-R #AWSreInvent

User avatar
emi
2024.12.09
[レポート] DocumentDB のエキスパートによるパフォーマンス最適化に関するチョークトーク「Amazon DocumentDB for expert practitioners」 #DAT417-R #AWSreInvent

[レポート] DocumentDB のエキスパートによるパフォーマンス最適化に関するチョークトーク「Amazon DocumentDB for expert practitioners」 #DAT417-R #AWSreInvent

User avatar
emi
2024.12.03
データベースのパフォーマンス対策として手軽にできる対策を施してみる #Notion

データベースのパフォーマンス対策として手軽にできる対策を施してみる #Notion

User avatar
haoyayoi
2024.04.25
フロントエンドのパフォーマンス改善のとっかかりとしてLighthouseとWebpack Bundle Analyzerで現状を調査してみた

フロントエンドのパフォーマンス改善のとっかかりとしてLighthouseとWebpack Bundle Analyzerで現状を調査してみた

User avatar
おーしろ
2022.06.21
Classmethod's white logo
Facebook's logo
X's logo
YouTube's logo

© Classmethod, Inc. All rights reserved.