Redshiftで分散スタイル&分散キーごとの実行計画を比較してみた

2016.12.20

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

こんにちは。三上です。

河原にどんと焼き(さいとう焼き?)ができてました。昔より小さく感じるのは、時代の流れでしょうか?(しみじみ。。

やりたいこと

  • Redshiftのテーブル設計を学びたい!(チューニングもできるようになりたい
  • 分散スタイルと分散キーの違いで何が変わるか確認したい

分散スタイルって・・・?

Redshiftの構成

aws_bb_rs_p7

  • Redshiftは、クラスタ > ノード > スライス の構成
  • 各ノードは並列で動く
  • データは各スライスに分割して持つ
  • データが偏っちゃうと働かないノードがでてくるのでもったいない
  • 別のノードが持っているデータが必要になると、データを再分散しないといけないので時間がかかる

分散スタイルの種類

  • EVEN:均等分散
  • KEY:指定キーはまとめて同じスライスに分散
  • ALL:全ノード(先頭スライス)に同じテーブルをコピー

AWS Documentation 分散スタイル

確認してみよう

確認環境

以下のRedshift(1ノード2スライス)で確認しました。 rs_cluster_prop

確認データ

以下から、使いたいカラムデータのみ抽出したCSVを作成しました。

テーブル構成

下記テーブル構成で、populationテーブルにすべての分散スタイルを指定 → 実行計画を比較しました。

er_rs_dist_test

データ準備

Redshiftに以下のテーブルを作り、データをロードします。

【住所マスタ】

create table m_addr(
    pref_cd varchar(255),
    city_cd varchar(255),
    pref varchar(255),
    city varchar(255),
    primary key(city_cd)
)diststyle even;

【地域種別マスタ】

create table m_area_kind(
    kind varchar(255),
    name varchar(255),
    primary key(kind)
)diststyle even;

【人口テーブル(×3)】

create table pop_even(
    area_cd varchar(255),
    area_kind varchar(255),
    population int,
    width double precision,
    density double precision,
    house int,
    foreign key(area_cd) references m_addr(city_cd),
    foreign key(area_kind) references m_area_kind(kind)
)diststyle even;

create table pop_dist(
    area_cd varchar(255),
    area_kind varchar(255),
    population int,
    width double precision,
    density double precision,
    house int,
    foreign key(area_cd) references m_addr(city_cd),
    foreign key(area_kind) references m_area_kind(kind)
)diststyle key distkey (area_cd);

create table pop_all(
    area_cd varchar(255),
    area_kind varchar(255),
    population int,
    width double precision,
    density double precision,
    house int,
    foreign key(area_cd) references m_addr(city_cd),
    foreign key(area_kind) references m_area_kind(kind)
)diststyle all;

分散スタイルを確認してみます。

select 
  relname, 
  reldiststyle 
from 
  pg_class 
where 
  relname like 'pop_%' 
  or relname = 'm_addr' 
  or relname = 'm_area_kind' 
order by 
  relname;
   relname   | reldiststyle
-------------+--------------
 m_addr      |            0
 m_area_kind |            0
 pop_all     |            8
 pop_dist    |            1
 pop_even    |            0
(5 rows)

※reldiststyleは以下の通りです。

  • 0:EVEN
  • 1:KEY
  • 8:ALL

AWS Documentation 分散スタイルの表示

※pop_distの分散キーは、area_cd を指定しました。

実際にデータが分散されているか確認してみます。

select
  name, slice, col, num_values as rows, minvalue, maxvalue
from
  svv_diskusage
where
  (name like 'pop_%' or name = 'm_addr' or name = 'm_area_kind') and col = 0 and rows > 0
order by
  name, slice, col;
                                   name                                   | slice | col | rows |   minvalue   |      maxvalue
--------------------------------------------------------------------------+-------+-----+------+--------------+---------------------
 m_addr                                                                   |     0 |   0 | 2776 |            0 | 2314885530818459444
 m_addr                                                                   |     1 |   0 | 2777 |            0 | 2314885530818459444
 m_area_kind                                                              |     0 |   0 |    5 |           48 | 2314885530818453603
 m_area_kind                                                              |     1 |   0 |    4 |           49 | 2314885530818453604
 pop_all                                                                  |     0 |   0 | 5553 | 206966894640 | 3472328296237087727
 pop_dist                                                                 |     0 |   0 | 2757 | 211261861936 | 2314885604236801844
 pop_dist                                                                 |     1 |   0 | 2796 | 206966894640 | 3472328296237087727
 pop_even                                                                 |     0 |   0 | 2777 | 215556829232 | 3472328296237087727
 pop_even                                                                 |     1 |   0 | 2776 | 206966894640 | 2314885604236801844
(9 rows)

AWS Documentation 分散の例

rows の列を見ると、ALLの pop_all は先頭スライスに全データが格納され、それ以外は指定の分散スタイルで各スライスにデータが格納されました。

実行計画

実行計画のポイント

実行計画では

  • DS_DIST_NONE
  • DS_DIST_ALL_NONE

以外はダメ!(再分散されるから

※ひとまず、他は後回しで。。

実行計画をとる

各分散スタイルのテーブルに対する、以下のクエリの実行計画をとってみます。

※住所マスタ(m_addr)とJOINして、都道府県ごとの人口を取得するクエリです。

explain 
select 
  a.pref, 
  sum(p.population) as population 
from 
  [populationテーブル] p 
  inner join m_addr a on p.area_cd = a.city_cd 
group by 
  a.pref 
order by 
  population desc;

実行結果↓

【EVEN】

                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 XN Merge  (cost=1001332721190.19..1001332721190.29 rows=39 width=14)
   Merge Key: sum(p.population)
   ->  XN Network  (cost=1001332721190.19..1001332721190.29 rows=39 width=14)
         Send to leader
         ->  XN Sort  (cost=1001332721190.19..1001332721190.29 rows=39 width=14)
               Sort Key: sum(p.population)
               ->  XN HashAggregate  (cost=1332721189.06..1332721189.16 rows=39 width=14)
                     ->  XN Hash Join DS_BCAST_INNER  (cost=69.41..1332721153.65 rows=7082 width=14)
                           Hash Cond: (("outer".city_cd)::text = ("inner".area_cd)::text)
                           ->  XN Seq Scan on m_addr a  (cost=0.00..55.53 rows=5553 width=19)
                           ->  XN Hash  (cost=55.53..55.53 rows=5553 width=13)
                                 ->  XN Seq Scan on pop_even p  (cost=0.00..55.53 rows=5553 width=13)
(12 rows)

【KEY】

                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 XN Merge  (cost=1001332721190.19..1001332721190.29 rows=39 width=14)
   Merge Key: sum(p.population)
   ->  XN Network  (cost=1001332721190.19..1001332721190.29 rows=39 width=14)
         Send to leader
         ->  XN Sort  (cost=1001332721190.19..1001332721190.29 rows=39 width=14)
               Sort Key: sum(p.population)
               ->  XN HashAggregate  (cost=1332721189.06..1332721189.16 rows=39 width=14)
                     ->  XN Hash Join DS_BCAST_INNER  (cost=69.41..1332721153.65 rows=7082 width=14)
                           Hash Cond: (("outer".city_cd)::text = ("inner".area_cd)::text)
                           ->  XN Seq Scan on m_addr a  (cost=0.00..55.53 rows=5553 width=19)
                           ->  XN Hash  (cost=55.53..55.53 rows=5553 width=13)
                                 ->  XN Seq Scan on pop_dist p  (cost=0.00..55.53 rows=5553 width=13)
(12 rows)

【ALL】

                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 XN Merge  (cost=1000000001190.19..1000000001190.29 rows=39 width=14)
   Merge Key: sum(p.population)
   ->  XN Network  (cost=1000000001190.19..1000000001190.29 rows=39 width=14)
         Send to leader
         ->  XN Sort  (cost=1000000001190.19..1000000001190.29 rows=39 width=14)
               Sort Key: sum(p.population)
               ->  XN HashAggregate  (cost=1189.06..1189.16 rows=39 width=14)
                     ->  XN Hash Join DS_DIST_ALL_NONE  (cost=69.41..1153.65 rows=7082 width=14)
                           Hash Cond: (("outer".city_cd)::text = ("inner".area_cd)::text)
                           ->  XN Seq Scan on m_addr a  (cost=0.00..55.53 rows=5553 width=19)
                           ->  XN Hash  (cost=55.53..55.53 rows=5553 width=13)
                                 ->  XN Seq Scan on pop_all p  (cost=0.00..55.53 rows=5553 width=13)
(12 rows)

→ALLのテーブルは DS_DIST_ALL_NONE なのでOK(その分ストレージ食ってますが。。)ですが、

EVENとKEYのテーブルに DS_BCAST_INNER が出てます。。

AWS Documentation クエリプランの評価

DS_BCAST_INNER 内部テーブル全体のコピーがすべてのコンピューティングノードにブロードキャストされます。

だそうで、よろしくありません。。

分散スタイルを見直す(1)

JOINのキー = 分散キー ならば、再分散なしで済むはずなので、

JOINする住所マスタ(m_addr)の分散スタイルを変えてみることにします。

テーブルを作り直し、データを入れなおしました。

※分散キー(city_cd)= JOIN句の結合キー です。

drop table m_addr;

create table m_addr(
    pref_cd varchar(255),
    city_cd varchar(255),
    pref varchar(255),
    city varchar(255),
    primary key(city_cd)
)diststyle key distkey (city_cd);
defaultdb=# select relname, reldiststyle from pg_class where relname = 'm_addr';
 relname | reldiststyle
---------+--------------
 m_addr  |            1
(1 row)


defaultdb=# select name, slice, col, num_values as rows, minvalue, maxvalue from svv_diskusage where name = 'm_addr' and col = 0 and rows > 0 order by name, slice, col;
                                   name                                   | slice | col | rows | minvalue |      maxvalue
--------------------------------------------------------------------------+-------+-----+------+----------+---------------------
 m_addr                                                                   |     0 |   0 | 2757 |        0 | 2314885530818459444
 m_addr                                                                   |     1 |   0 | 2796 |        0 | 2314885530818459444
(2 rows)

再度、EVENとKEYのテーブルの実行計画を取ってみます。

【EVEN】

                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 XN Merge  (cost=1001332721190.19..1001332721190.29 rows=39 width=14)
   Merge Key: sum(p.population)
   ->  XN Network  (cost=1001332721190.19..1001332721190.29 rows=39 width=14)
         Send to leader
         ->  XN Sort  (cost=1001332721190.19..1001332721190.29 rows=39 width=14)
               Sort Key: sum(p.population)
               ->  XN HashAggregate  (cost=1332721189.06..1332721189.16 rows=39 width=14)
                     ->  XN Hash Join DS_BCAST_INNER  (cost=69.41..1332721153.65 rows=7082 width=14)
                           Hash Cond: (("outer".city_cd)::text = ("inner".area_cd)::text)
                           ->  XN Seq Scan on m_addr a  (cost=0.00..55.53 rows=5553 width=19)
                           ->  XN Hash  (cost=55.53..55.53 rows=5553 width=13)
                                 ->  XN Seq Scan on pop_even p  (cost=0.00..55.53 rows=5553 width=13)
(12 rows)

【KEY】

                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 XN Merge  (cost=1000000001190.19..1000000001190.29 rows=39 width=14)
   Merge Key: sum(p.population)
   ->  XN Network  (cost=1000000001190.19..1000000001190.29 rows=39 width=14)
         Send to leader
         ->  XN Sort  (cost=1000000001190.19..1000000001190.29 rows=39 width=14)
               Sort Key: sum(p.population)
               ->  XN HashAggregate  (cost=1189.06..1189.16 rows=39 width=14)
                     ->  XN Hash Join DS_DIST_NONE  (cost=69.41..1153.65 rows=7082 width=14)
                           Hash Cond: (("outer".city_cd)::text = ("inner".area_cd)::text)
                           ->  XN Seq Scan on m_addr a  (cost=0.00..55.53 rows=5553 width=19)
                           ->  XN Hash  (cost=55.53..55.53 rows=5553 width=13)
                                 ->  XN Seq Scan on pop_dist p  (cost=0.00..55.53 rows=5553 width=13)
(12 rows)

→KEYのテーブルが DS_DIST_NONE に変わりました!

分散スタイルを見直す(2)

もうひとつ、別のクエリの実行計画を取ってみます。

※地域種別マスタ(m_area_kind)とJOINして、地域種別ごとの人口を取得するクエリです。

explain
select
  k.name,
  sum(p.population) as population
from
  [populationテーブル] p 
  inner join m_area_kind k on p.area_kind = k.kind
group by
  k.name
order by
  population desc;

実行結果↓

【EVEN】

                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 XN Merge  (cost=1000003960208.52..1000003960208.54 rows=9 width=32)
   Merge Key: sum(p.population)
   ->  XN Network  (cost=1000003960208.52..1000003960208.54 rows=9 width=32)
         Send to leader
         ->  XN Sort  (cost=1000003960208.52..1000003960208.54 rows=9 width=32)
               Sort Key: sum(p.population)
               ->  XN HashAggregate  (cost=3960208.35..3960208.37 rows=9 width=32)
                     ->  XN Hash Join DS_BCAST_INNER  (cost=0.11..3960180.58 rows=5553 width=32)
                           Hash Cond: (("outer".area_kind)::text = ("inner".kind)::text)
                           ->  XN Seq Scan on pop_even p  (cost=0.00..55.53 rows=5553 width=9)
                           ->  XN Hash  (cost=0.09..0.09 rows=9 width=33)
                                 ->  XN Seq Scan on m_area_kind k  (cost=0.00..0.09 rows=9 width=33)
(12 rows)

【KEY】

                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 XN Merge  (cost=1000003960208.52..1000003960208.54 rows=9 width=32)
   Merge Key: sum(p.population)
   ->  XN Network  (cost=1000003960208.52..1000003960208.54 rows=9 width=32)
         Send to leader
         ->  XN Sort  (cost=1000003960208.52..1000003960208.54 rows=9 width=32)
               Sort Key: sum(p.population)
               ->  XN HashAggregate  (cost=3960208.35..3960208.37 rows=9 width=32)
                     ->  XN Hash Join DS_BCAST_INNER  (cost=0.11..3960180.58 rows=5553 width=32)
                           Hash Cond: (("outer".area_kind)::text = ("inner".kind)::text)
                           ->  XN Seq Scan on pop_dist p  (cost=0.00..55.53 rows=5553 width=9)
                           ->  XN Hash  (cost=0.09..0.09 rows=9 width=33)
                                 ->  XN Seq Scan on m_area_kind k  (cost=0.00..0.09 rows=9 width=33)
(12 rows)

先ほどと同じく、DS_BCAST_INNER 出てます。。

※同じ結果なので、ALLは省略しました。

分散キーは1つしか指定できないため、別のカラムをキーにしたJOINが必要になると、困ります。。

今度は、地域種別マスターの分散スタイルをALLに変更してみます。

drop table m_area_kind;

create table m_area_kind(
    kind varchar(255),
    name varchar(255),
    primary key(kind)
)diststyle all;
defaultdb=# select relname, reldiststyle from pg_class where relname = 'm_area_kind';
   relname   | reldiststyle
-------------+--------------
 m_area_kind |            8
(1 row)


defaultdb=# select name, slice, col, num_values as rows, minvalue, maxvalue from svv_diskusage where name = 'm_area_kind' and col = 0 and rows > 0 order by name, slice, col;
                                   name                                   | slice | col | rows | minvalue |      maxvalue
--------------------------------------------------------------------------+-------+-----+------+----------+---------------------
 m_area_kind                                                              |     0 |   0 |    9 |       48 | 2314885530818453604
(1 row)

実行計画を取ってみると・・・

【EVEN】

                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 XN Merge  (cost=1000000000208.52..1000000000208.54 rows=9 width=32)
   Merge Key: sum(p.population)
   ->  XN Network  (cost=1000000000208.52..1000000000208.54 rows=9 width=32)
         Send to leader
         ->  XN Sort  (cost=1000000000208.52..1000000000208.54 rows=9 width=32)
               Sort Key: sum(p.population)
               ->  XN HashAggregate  (cost=208.35..208.37 rows=9 width=32)
                     ->  XN Hash Join DS_DIST_ALL_NONE  (cost=0.11..180.59 rows=5553 width=32)
                           Hash Cond: (("outer".area_kind)::text = ("inner".kind)::text)
                           ->  XN Seq Scan on pop_even p  (cost=0.00..55.53 rows=5553 width=9)
                           ->  XN Hash  (cost=0.09..0.09 rows=9 width=33)
                                 ->  XN Seq Scan on m_area_kind k  (cost=0.00..0.09 rows=9 width=33)
(12 rows)

【KEY】

                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 XN Merge  (cost=1000000000208.52..1000000000208.54 rows=9 width=32)
   Merge Key: sum(p.population)
   ->  XN Network  (cost=1000000000208.52..1000000000208.54 rows=9 width=32)
         Send to leader
         ->  XN Sort  (cost=1000000000208.52..1000000000208.54 rows=9 width=32)
               Sort Key: sum(p.population)
               ->  XN HashAggregate  (cost=208.35..208.37 rows=9 width=32)
                     ->  XN Hash Join DS_DIST_ALL_NONE  (cost=0.11..180.59 rows=5553 width=32)
                           Hash Cond: (("outer".area_kind)::text = ("inner".kind)::text)
                           ->  XN Seq Scan on pop_dist p  (cost=0.00..55.53 rows=5553 width=9)
                           ->  XN Hash  (cost=0.09..0.09 rows=9 width=33)
                                 ->  XN Seq Scan on m_area_kind k  (cost=0.00..0.09 rows=9 width=33)
(12 rows)

→両方とも、DS_DIST_ALL_NONE になってくれました!(めでたしめでたしv

まとめ(わかったこと)

  • テーブルの分散スタイルは要検討(JOINが必要な場合は特に
  • スタイル指定しない場合のデフォルトはEVEN
  • スタイルはKEY(DISTKEY)指定がベスト KEY(DISTKEY)指定でパフォーマンスチューニングできる(2017/09/01 訂正)
  • JOINのキーを分散キーに指定するといい
  • カーディナリティの低い(バリエーションの少ない)カラムを分散キーに指定しちゃダメ(データが偏るから
  • テーブルサイズが小さい場合はALL指定でもOK
  • 分散スタイルでJOINに対応できない場合は、テーブルの非正規化も検討する

参照ドキュメント