Amazon Redshift テーブル設計詳細ガイド:Part 3 Compound and Interleaved Sort Keys（Compound と Interleaved ソートキー）

著者Zach Christopherson はAmazon Redshiftチームのシニアデータベースエンジニアです。

---

Part 1: Preamble, Prerequisites, and Prioritization（序文、事前準備、優先順位付け）
Part 2: Distribution Styles and Distribution Keys（分散スタイルと分散キー）
Part 3: Compound and Interleaved Sort Keys（Compound と Interleaved ソートキー）（本稿） 
Part 4: Compression Encodings（圧縮エンコーディング）
Part 5: Table Data Durability（テーブルデータの永続性） 

---

この章では、最適なソートキーの選び方を説明したいと思います。また、特定のワークロードに対して、パフォーマンスの為に適切にソートキーを選択する具体的なガイダンスも提供していきます。

 ソートキーを定義したテーブルは、指定した列とソートタイプに基づいて、各スライス内でデータの並び替えを行います。Amazon Redshiftでは「Compound Sort Keys」, 「Interleaved Sort Keys」, 「ソートキーを指定しない」の3つが定義できます。これらのソートキーはワークロードの特性にしたがい選択していきます。実際には、Compound Sort Keysが90%以上のワークロードに最適となります。

Amazon　Redshiftでは、データをソートすることには多くの利点があります。

1.　効率的なゾーンマップによりDisk I/Oを削減できます。
2.　クエリー実行時のソート処理を減らす、もしくは無くす事により余計な処理を削減できます。
3.　マージジョインにより、結合のパフォーマンスを向上できます。

最も基本的な質問から始めて、深掘りしていく事で、ワークロードに対して最適なソートタイプとソート列を決めるフローチャートを作成していきます。

 

クエリーはソートキーによる恩恵を受けていますか？

ほとんどの場合は恩恵をうけることができます。しかし、いくつかの稀な場合は、ソートをしてもパフォーマンスは向上せずにデータを取り込む際、僅かなオーバーヘッドとなります。

先ほど説明したように、ソートキーを使用してゾーンマップを効率的に利用することで、読み取りI/Oが減少させることができます。また、ソートにより、ORDER BY, PARTITION BY,GROUP BYなどのSQL操作時に、必要なソート処理を減らす、もしくは無くすことができます。ソートキーの設定によりMERGE JOINが選択されるようになるという点も重要です。MERGE JOINはAmazon Redshiftでサポートされている3つのJOIN操作の中で最速です。

3つの最適化のいずれも有効でないパターンでアクセスされるテーブルがある場合、ソートキーを定義しても差が出ないことがあります。その際はソートキーを指定する必要はありません。

まとめると、下記のような質問フローチャートが作れます。

Compound Sort KeyまたはInterleaved Sort Keyを使用する場合、どちらのタイプが最適かを決める為に、下記チャートに追加の質問を足しました。

 

ソートはMERGE JOINを有効にしますか？

MERGE JOINは下記の条件に合致した場合に効果的ます。

1. 2つのテーブルが同じ列でソート（Compound)、分散されている場合
2. どちらのテーブルも80%以上ソートされている場合（svv_table_info.unsorted < 20%）
3. JOIN条件で分散キーとソートキーにより、JOINされている場合

下記のクエリーを使用して、DISTKEYとSORTKEY（Compound)の両方の定義をしている列があるテーブルを見つけることができます。

 

 SELECT * FROM admin.v_extended_table_info
 WHERE table_id IN (
     SELECT DISTINCT attrelid FROM pg_attribute
     WHERE attisdistkey = true AND attsortkeyord > 0
     MINUS
     SELECT DISTINCT attrelid FROM pg_attribute
     WHERE attsortkeyord = -1
 );

 

次のクエリーは下記の条件を満たすクエリーの数を出します。

1. 指定したテーブル（Table_id）に対して実行されたクエリー
2. DISTKEYとCompound SORTKEYが定義されたテーブルに対して実行されたクエリー
3. なんらかのJOINが実行されたクエリー

WHERE query IN (
   SELECT DISTINCT query FROM stl_scan
   WHERE tbl = [table_id]  type = 2 AND userid > 1
   INTERSECT
   SELECT DISTINCT query FROM stl_scan
   WHERE tbl <> [table_id] AND type = 2 AND userid > 1
   AND tbl IN (
     SELECT DISTINCT attrelid FROM pg_attribute
     WHERE attisdistkey = true AND attsortkeyord > 0
     MINUS
     SELECT DISTINCT attrelid FROM pg_attribute
     WHERE attsortkeyord = -1)
   INTERSECT
   (SELECT DISTINCT query FROM stl_hashjoin WHERE userid > 1
   UNION
   SELECT DISTINCT query FROM stl_nestloop WHERE userid > 1
   UNION
   SELECT DISTINCT query FROM stl_mergejoin WHERE userid > 1)
 );

上記のクエリによって結果が返された場合は、他のテーブルを変更せずに既存のクエリがMERGE JOINを使用することができるでしょう。 もしそうなら、これらの特定のクエリをより深く掘り下げて、JOIN条件にDISTKEY列とSORTKEY列が含まれているかどうかを確認していきます。 STL_EXPLAINテーブルに対して、JOIN条件をチェックする下記クエリーを使いこの調査を迅速化します。

 

SELECT query, TRIM(info)
 FROM stl_explain
 WHERE info LIKE '% Cond:%'
 AND userid > 1 AND query IN (
   SELECT DISTINCT query FROM stl_scan
   WHERE tbl = [table_id] AND type = 2 AND userid > 1
   INTERSECT
   SELECT DISTINCT query FROM stl_scan
   WHERE tbl <> [table_id] AND type = 2 AND userid > 1
   AND tbl IN (
     SELECT DISTINCT attrelid FROM pg_attribute
     WHERE attisdistkey = true AND attsortkeyord > 0
     MINUS
     SELECT DISTINCT attrelid FROM pg_attribute
     WHERE attsortkeyord = -1
   )
   INTERSECT
   (SELECT DISTINCT query FROM stl_hashjoin WHERE userid > 1
    UNION
    SELECT DISTINCT query FROM stl_nestloop WHERE userid > 1
    UNION
    SELECT DISTINCT query FROM stl_mergejoin WHERE userid > 1))
 ORDER BY query;

上記クエリーの結果が無い場合、MERGE JOINにする為には、複数のテーブルを同時に変更する必要があります。

注：複数のテーブルを見直し、MERGE JOINが実施されるようにするには、複雑な変更が必要である為、本記事の範囲を超えております。このような最適化に興味がある場合は、JOIN操作に関するドキュメントを確認してこのブログ記事のコメントに具体的な質問をしてください。

 

MERGE JOINを使用したいですか？

MERGEJOINは3つのJOINの中で最速であるので、多くの場合有益です。しかし、特定のテーブルとクエリーパターンでは、結合条件ではなく、フィルタ条件に効果的なSORTKEYを指定し、HASH JOINを使用したほうが速い場合があります。例えば、JOIN条件とは無関係のソートされた列で、大部分のブロックを使わないように絞り込める述部（WHERE句）があるとします。

これを説明する為に、2つの異なるスキーマーのケーススタディを考えます。

• lineitemとordersテーブルは、orderkeyで関係づけられています。
• 以下の基準を満たす全ての商品の合計売り上げを計算します。
        ・104,500ドル以上で販売
        ・注文された日に出荷されたる

下記 SQLで条件を満たす結果を取得できます。

 

SELECT SUM(l_price) 
 FROM lineitem l 
 JOIN orders o ON o.orderkey = l.orderkey 
 AND o.o_orderdate = l.l_shipdate 
 AND l.l_price > 104500;

• アプローチ1:　MERGEJOINを使用する場合
  　・lineitemはorderkeyで分散されて、l_shipdate、oderkeyでソートされている。
  　・ordersはorderkeyで分散されて、o_orderdate, orderkey でソートされている。
• アプローチ2:　HASH JOINとフィルターを使用する場合
  　・lineitemはorderkeyで分散されて、l_priceでソートされている。
  　・ordersはorderkeyで分散されて、o_orderdate, orderkey でソートされている。

どのアプローチが最も効率的かは、ordersテーブルのサイズと、l_priceでどれだけ絞り込みができるかによって決まります。

クエリーがアプローチ1で高速化される場合、MERGE JOINがスキャン中にフィルターによりI/Oを減らすよりも効果があることを意味します。しかし、アプローチ2が速い場合は、スキャン中のI/O削減が効いて、MERGE JOINの最適化よりも効果的であったこということになります。

それぞれのアプローチの意味を理解することで、MERGE JOINでクエリーがより最適化される場合や、ソートキーによるフィルター条件を最適化することにフォーカスするべきかなどの判断を行えます。

 

ソートはゾーンマップを改善していますか？

ゾーンマップは、1MBブロック毎に、ブロック内の最小値と最大値をメモリー内にメタデータとして保存します。このメタデータは、どのブロックがクエリーに必要とされているかを識別する為に、ディスクスキャンの前にアクセスされます。ソートされていない列の場合、最小-最大範囲は、ブロック毎に重複する可能性があり、ゾーンマップの有効性を低下させます。ソートキーは、各スライス内の各列（及び各ブロック）を昇順でデータを物理的に並び変えることにより、ほとんどの場合ゾーンマップの有効性を向上させます。しかしながら、SORTKEYでゾーンマップが改善されない場合があります。

• 各スライスにブロックが1つしか無い場合
  　・1スライスあたり、1列のブロックが1つしか無い場合、そのデータがソートされていても、いなくても、最小値と最大値は変わりません。
  　・例：数千行未満の非常に小さなディメンジョンテーブル、各列の値が各スライスの1MB block内に収まるような場合。
• 8バイト以上の、Prefixed文字列がある場合
  　・長い文字列の場合、最初の8文字を使用します。
  　・例：URLのような、常に「https://」が接頭文字としてある場合は、URLの後の部分とは関係なく、常に最小値、最大値が同じになります。
• 列に、1つの値のみが含まれている場合
  　・ソート順に関わらず最小値、最大値が一致している場合、ソートする必要はありません。
  　・例：DATE_PART(’month',’timestamp')のような、限定的な値となる場合

これらのシナリオはまれですが、ソートキーでゾーンマップの有効性が向上しないこともあるということを理解しておいてください。

 

クエリーはゾーンマップを利用しますか?

ゾーンマップの恩恵を受ける為には、ソートキーで指定した列にてクエリーを実行する必要あります。下記にゾーンマップが効かないパターンを記しました。

CREATE TABLE IF NOT EXISTS "public"."orders"
 (...
     ,"o_totalprice" NUMERIC(12,2) NOT NULL
     ,"o_ordertime" TIMESTAMP NOT NULL   
     ,"o_orderdate" DATE NOT NULL
  ...) DISTSTYLE ...
  SORTKEY ("o_ordertime", "o_totalprice");

 -- 述語(WHERE句でソートキーを使って絞り込みをしていない場合
SELECT * FROM orders;
 SELECT * FROM orders WHERE o_shippriority = 5;
 
 -- Functionを使っている場合
SELECT * FROM orders
 WHERE DATEADD('d',5,o_ordertime) > '2016-09-09 01:30:00';
 
 -- CASTしている場合
SELECT * FROM orders WHERE o_ordertime::date='2016-09-09'; -- Explicit
 SELECT * FROM orders WHERE o_ordertime = '2016-09-09';     　-- Implicit
 
 -- 範囲を絞り込めていない場合
root@redshift/tpch=# SELECT MIN(o_ordertime) FROM orders;
     min
------------ 
　1993-01-01
(1 row)
SELECT * FROM orders WHERE o_ordertime > '1992-01-01 1:30:00';

 

上記のようなパターンを避ければ、レンジスキャンの場合ゾーンマップの恩恵を受けることができます。

 

クエリーは、同様の複数の列に対して、繰り返し絞り込みを行いますか？

各テーブルは、ソートキーを1つ（単独の列、もしくは組み合わせの列）指定できます。異なるソートキーで使って、複数テーブルを作るようなケース除いて、ワークロード毎に、もっとも効率的なテーブルアクセス方法を知っておく必要あります。自動的に保存されているクエリー履歴テーブル（Amazon Redshiftのシステムテーブル）を確認することにより、テーブルフィルターパターンを確認できます。
下記クエリーでどのようにフィルターされているかを知ることが可能です。

SELECT
     ti.schema||'.'||ti."table" AS tablename,
     COUNT(DISTINCT i.query) AS number_queries,
     MAX(i.query) AS example_query, TRIM(info) AS filter
 FROM stl_explain p
 JOIN stl_plan_info i USING (userid,query,nodeid)
 JOIN stl_scan s USING (userid,query,segment,step)
 JOIN svv_table_info ti ON ti.table_id = s.tbl
 WHERE s.type = 2 AND s.userid > 1
 AND p.info LIKE 'Filter:%' AND p.nodeid > 0
 AND s.tbl = [table_id]
 GROUP BY 1,4 ORDER BY 2 DESC, 1;

同様の組み合わせの列にてフィルタリングしていることが分かれば、どの列の組み合わせをソートキーで指定すれば良いかのヒントとなります。もしパターンが無くランダムであれば、単一のCompound Sort Keyでは効果が低いことがわかります。

場合によって、様々なフィルタ条件でテーブルにアクセスします。これらの中には、フィルタが相互に関係していて別の列のゾーンマップが役に立つ場合があります。例えば、txn_tsは2016−09−09 10:00:00でdxn_dtは2016−09−09である場合、dxn_dtは明らかに、txn_tsと直接関係してます。これらは異なるフィルター条件ですが、フィルターが相互に利益をもたらすので、片方は考慮する必要が無くなります。

より重要なのは、2つの部門が、異なる目的で同じテーブルにアクセスする場合です。例えば、マーケティング部門は、販売日と顧客層に興味がありoradesテーブルにアクセスするとします。かたやもう1つのビジネス部門は様々な市場セグメントで、複雑な分析を実行する必要があり、ほぼリアルタイムでターゲット広告キャンペーンの効果を評価しています。物流センターの業務部長は、出荷されたパッケージを追跡するたに毎週の分析結果に興味があるかもしれません。この例では、1つのユースケースが、他のユースケースよりも、はるかに多くのビジネス価値があるということになります。

注：システムテーブルからワークロードの履歴を調査する場合、ビジネスにとって重要なクエリーや作業に注目してください。スケジュール化されているジョブや、既知のワークロードによって頻繁に繰り返される処理の最適化を優先度高く行い、Ad Hoc（探索的）なクエリーの最適化よりも優先します。Ad Hocなクエリーは、しばしばビジネスにとって価値がない最適化されていないSQLコードで実行されていることがあるからです。

 

必要に応じて、VACUUM REINDEXをできますか？

INTERLEAVED SORTKEYを使っている場合、このWikipediaにあるz-inedxes及び、z-compressorsというメタデータに基づいてソートされています。このメタデータは、空のテーブルに最初にCOPYコマンドを使用してロードした際に作られ、VACUUM REINDEXコマンドにより再構築が可能です。もし、INSERT INTO SELECTやCTASで初期データロードを実施して、COPYを使用しないような場合は、INTERLEAVED SORTKEYの恩恵を受けるためには、VACUUM  REINDEXをする必要があります。

VACUUM REINDEXは、テーブルのサイズによっては、とても重たい処理です。COPYコマンドでロードせず、VACUUM REINDEXの重たい処理を許容できない場合はINTERLEAVED SORTKEYを使用しないでください。

 

ソートは実行時のソート処理を減らしますか？

上記のフローチャートの中で、「ソートはソート処理を削減しますか？」まで進むの珍しいケースです。しかし、テーブルフルスキャン時に、ORDER BY句、GROUP BY句を使用する場合、読み取り時のソート処理を減らすために、Compound Sort Keyを指定する価値はあります。
これにより、特定のクエリ処理をスキップしたり、処理コストを削減が可能です。例えば、ORDER BY、GROUP BY,　Window関数内のPARTITION BY, ORDER BYなどが含まれます。

 

最適なソートキー列の決定

Compound,またはInterleaved Sort Keyが効果的と判断したら、次に指定する列を決めます。この手順は簡単です。既に絞り込みで使う列（フィルタ）の頻度を特定し、「クエリーは、同様の複数の列に対して、繰り返し絞り込みを行いますか？」という質問に答えているからです。あなたのフィルタ条件を理解するために下記クエリーを使ってみてください。

SELECT
    ti.schema||'.'||ti."table" AS tablename,
     COUNT(DISTINCT i.query) AS number_queries,
     MAX(i.query) AS example_query, TRIM(info) AS filter
 FROM stl_explain p
 JOIN stl_plan_info i USING (userid,query,nodeid)
 JOIN stl_scan s USING (userid,query,segment,step)
 JOIN svv_table_info ti ON ti.table_id = s.tbl
 WHERE s.type = 2 AND s.userid > 1
 AND p.info LIKE 'Filter:%' AND p.nodeid > 0
 AND s.tbl = [table_id]
 GROUP BY 1,4 ORDER BY 2 DESC, 1;

このクエリの結果と、前のクエリー結果より、どの列の優先度が高いか明らかにできます。

SELECT
     ti."table", ti.diststyle, RTRIM(a.attname) column_name,
     COUNT(DISTINCT s.query ||'-'|| s.segment ||'-'|| s.step) as num_scans,
     COUNT(DISTINCT CASE WHEN TRANSLATE(TRANSLATE(info,')',' '),'(',' ') LIKE ('Filter:% '|| a.attname ||' %') THEN s.query ||'-'|| s.segment ||'-'|| s.step END) AS column_filters
 FROM stl_explain p
 JOIN stl_plan_info i ON ( i.userid=p.userid AND i.query=p.query AND i.nodeid=p.nodeid  )
 JOIN stl_scan s ON (s.userid=i.userid AND s.query=i.query AND s.segment=i.segment AND s.step=i.step)
 JOIN svv_table_info ti ON ti.table_id=s.tbl
 JOIN pg_attribute a ON (a.attrelid=s.tbl AND a.attnum > 0)
 WHERE s.tbl = [table_id]
 GROUP BY 1,2,3,a.attnum
 ORDER BY attnum;

 上記により、テーブルがどのよう絞り込みされているかを見える化して、ワークロードに最適なソート列を決めることができます。

次のステップ

最適なソートキーを選択することにより、ゾーンマップの有効性を改善しDisk I/O減らしことが出来ます。またソート作業を減らす、もしくは無くすことにより、余計なI/Oと計算を無くせます。さらに、MERGE JOINを可能にすることもできます。それらのメリットは、複雑なワークロード、大量のデータセットに対するパフォーマンス改善に役立ちます。
前述のプロセスに従って、適切なソートタイプ、ソート列を決めることができます。最後のステップはこれらの最適化を適用するために、テーブルを再構築することです。この再構築はいつでも実行できます。しかし、他のAdvanced Table Design Playbookの他の章も読み進める場合は、テーブルの再構築はまだ待つことをお勧めします。
4章では、圧縮エンコードに関して解説し、パフォーマンスの大幅な向上と、ストレージの削減を行う方法について説明します。

---

Part 1: Preamble, Prerequisites, and Prioritization（序文、事前準備、優先順位付け）
Part 2: Distribution Styles and Distribution Keys（分散スタイルと分散キー）
Part 3: Compound and Interleaved Sort Keys（Compound と Interleaved ソートキー）（本稿） 
Part 4: Compression Encodings（圧縮エンコーディング）
Part 5: Table Data Durability（テーブルデータの永続性

---

著者について

Zach Christopherson はパロアルトを拠点とするAWSのシニアデータベースエンジニアです

彼は、全ての業界において、Amazon Redshiftのユーザーが最適なパフォーマンスを得るためのワークロードをチューニングすることを支援しています。 Amazon Redshiftサービスチームの一員として、彼は新しいサービスや既存のサービスの開発にも影響を与えており、貢献しています。休暇の際は妻のメアリーと一緒に新しいレストランにトライし、生まれたばかりの娘ソフィアの世話を楽しんでいます。

---

 ※日本語訳はパートナSA 相澤が担当しました。原文は Amazon Redshift Engineering’s Advanced Table Design Playbook: Compound and Interleaved Sort Keys