ablog

不器用で落着きのない技術者のメモ

Amazon Redshift でテーブルの分散スタイルが AUTO の場合、ALL / EVEN / KEY 分散がどのように決まるか

AWS Redshift

Amazon Redshift でテーブルの分散スタイルが AUTO の場合に、ALL / EVEN / KEY 分散のいずれになるかデータを Insert or Copy しながら検証してみた。

AUTO 分散
AUTO 分散では、Amazon Redshift はテーブルデータのサイズに基づいて最適な分散スタイルを割り当てます。例えば、AUTO 分散スタイルが指定された場合、Amazon Redshift ではまず、ALL 分散スタイルを小さなテーブルに割り当てます。テーブルが大きくなると、Amazon Redshift は分散スタイルを KEY に変更し、プライマリキー (または複合プライマリキーの列) を分散キーとして選択する場合があります。テーブルが大きくなり、分散キーに適した列がない場合、Amazon Redshift は分散スタイルを EVEN に変更します。ディストリビューションスタイルの変更は、ユーザークエリへの影響を最小限に抑え、バックグラウンドで発生します。
分散スタイル - Amazon Redshift

検証結果

分散スタイル AUTO、PKありでテーブルを作成した場合、
- 1件レコードを insert すると ALL 分散になり、その後 COPY でデータロードすると EVEN 分散になる
- いきなり COPY でデータロードすると KEY 分散になる

追加で検証したいポイント

EVEN 分散になったテーブルに対して、PK で結合するクエリを繰り返し実行すると KEY 分散に変わるか

検証シナリオ

分散スタイル AUTO、PK あり（2カラム）でテーブルを作成し、1件 Insert 後に COPY でデータロードする

=# create table lineitem (
  l_orderkey int8 not null,
  l_partkey int8,
  l_suppkey int4,
  l_linenumber int4  not null,
  l_quantity numeric(12,2),
  l_extendedprice numeric(12,2),
  l_discount numeric(12,2),
  l_tax numeric(12,2),
  l_returnflag char(1),
  l_linestatus char(1),
  l_shipdate date,
  l_commitdate date,
  l_receiptdate date,
  l_shipinstruct char(25),
  l_shipmode char(10),
  l_comment varchar(44),
  Primary Key(L_ORDERKEY, L_LINENUMBER)
) diststyle auto sortkey(l_shipdate,l_orderkey)  ;

=# show table lineitem;
Show Table DDL statement
CREATE TABLE public.lineitem (
    l_orderkey bigint NOT NULL ENCODE raw,
    l_partkey bigint ENCODE az64,
    l_suppkey integer ENCODE az64,
    l_linenumber integer NOT NULL ENCODE az64,
    l_quantity numeric(12,2) ENCODE az64,
    l_extendedprice numeric(12,2) ENCODE az64,
    l_discount numeric(12,2) ENCODE az64,
    l_tax numeric(12,2) ENCODE az64,
    l_returnflag character(1) ENCODE lzo,
    l_linestatus character(1) ENCODE lzo,
    l_shipdate date ENCODE raw,
    l_commitdate date ENCODE az64,
    l_receiptdate date ENCODE az64,
    l_shipinstruct character(25) ENCODE lzo,
    l_shipmode character(10) ENCODE lzo,
    l_comment character varying(44) ENCODE lzo,
    PRIMARY KEY (l_orderkey, l_linenumber)
)
DISTSTYLE AUTO
SORTKEY ( l_shipdate, l_orderkey );
(1 row)

=# select relname, relnamespace, reltype, relowner, releffectivediststyle from PG_CLASS_INFO where relname = 'lineitem';
lineitem|2200|1889881|100|10 ★  10: AUTO (ALL)
(1 row)

=# insert into lineitem(l_orderkey, l_linenumber) values(1, 1);

=# select database, schema, "table", diststyle, sortkey1, size, tbl_rows, unsorted, stats_off, vacuum_sort_benefit from svv_table_info where "table" = 'lineitem';
database|schema|table|diststyle|sortkey1|size|tbl_rows|unsorted|stats_off|vacuum_sort_benefit
dev|public|lineitem|AUTO(ALL)|l_shipdate|152|1|0.00|100.00|0.00
(1 row)

=# copy lineitem from 's3://redshift-downloads/TPC-H/2.18/10GB/lineitem.tbl' iam_role 'arn:aws:iam::123456789012:role/redshift-spectrum-s3-fullaccess' delimiter '|' region 'us-east-1';

=# select database, schema, "table", diststyle, sortkey1, size, tbl_rows, unsorted, stats_off, vacuum_sort_benefit from svv_table_info where "table" = 'lineitem';
database|schema|table|diststyle|sortkey1|size|tbl_rows|unsorted|stats_off|vacuum_sort_benefit
dev|public|lineitem|AUTO(EVEN)|l_shipdate|2833|59986053|100.00|100.00|0.00
(1 row)

=# copy lineitem from 's3://redshift-downloads/TPC-H/2.18/100GB/lineitem/' iam_role 'arn:aws:iam::123456789012:role/redshift-spectrum-s3-fullaccess' delimiter '|' region 'us-east-1';

=# select database, schema, "table", diststyle, sortkey1, size, tbl_rows, unsorted, stats_off, vacuum_sort_benefit from svv_table_info where "table" = 'lineitem';
database|schema|table|diststyle|sortkey1|size|tbl_rows|unsorted|stats_off|vacuum_sort_benefit
dev|public|lineitem|AUTO(EVEN)|l_shipdate|30100|660023955|100.00|100.00|0.00
(1 row)

分散スタイル AUTO、PK あり（1カラム）でテーブルを作成し、1件 Insert 後に COPY でデータロード

=# create table orders (
  o_orderkey int8 not null,
  o_custkey int8,
  o_orderstatus char(1),
  o_totalprice numeric(12,2),
  o_orderdate date,
  o_orderpriority char(15),
  o_clerk char(15),
  o_shippriority int4,
  o_comment varchar(79),
  Primary Key(O_ORDERKEY)
) diststyle auto sortkey(o_orderdate, o_orderkey) ;

=# show table orders;
Show Table DDL statement
CREATE TABLE public.orders (
    o_orderkey bigint NOT NULL ENCODE raw,
    o_custkey bigint ENCODE az64,
    o_orderstatus character(1) ENCODE lzo,
    o_totalprice numeric(12,2) ENCODE az64,
    o_orderdate date ENCODE raw,
    o_orderpriority character(15) ENCODE lzo,
    o_clerk character(15) ENCODE lzo,
    o_shippriority integer ENCODE az64,
    o_comment character varying(79) ENCODE lzo,
    PRIMARY KEY (o_orderkey)
)
DISTSTYLE AUTO
SORTKEY ( o_orderdate, o_orderkey );
(1 row)

=# select relname, relnamespace, reltype, relowner, releffectivediststyle from PG_CLASS_INFO where relname = 'orders';
relname|relnamespace|reltype|relowner|releffectivediststyle
orders|2200|1889887|100|10 ★  10: AUTO (ALL)
(1 row)

=# insert into orders(O_ORDERKEY) values(1);

=# select database, schema, "table", diststyle, sortkey1, size, tbl_rows, unsorted, stats_off, vacuum_sort_benefit from svv_table_info where "table" = 'orders';
database|schema|table|diststyle|sortkey1|size|tbl_rows|unsorted|stats_off|vacuum_sort_benefit
dev|public|orders|AUTO(ALL)|o_orderdate|96|1|0.00|100.00|0.00
(1 row)

=# copy orders from 's3://redshift-downloads/TPC-H/2.18/10GB/orders.tbl' iam_role 'arn:aws:iam::123456789012:role/redshift-spectrum-s3-fullaccess' delimiter '|' region 'us-east-1';
database|schema|table|diststyle|sortkey1|size|tbl_rows|unsorted|stats_off|vacuum_sort_benefit
dev|public|orders|AUTO(EVEN)|o_orderdate|832|15000001|100.00|100.00|0.00
(1 row)

=#  select database, schema, "table", diststyle, sortkey1, size, tbl_rows, unsorted, stats_off, vacuum_sort_benefit from svv_table_info where "table" = 'orders';
database|schema|table|diststyle|sortkey1|size|tbl_rows|unsorted|stats_off|vacuum_sort_benefit
dev|public|orders|AUTO(EVEN)|o_orderdate|7969|165000001|100.00|100.00|0.00
(1 row)

分散スタイル AUTO、PK ありでテーブルを作成し、COPY でデータロードする

=# create table customer (
  c_custkey int8 not null ,
  c_name varchar(25) not null,
  c_address varchar(40) not null,
  c_nationkey int4 not null,
  c_phone char(15) not null,
  c_acctbal numeric(12,2) not null,
  c_mktsegment char(10) not null,
  c_comment varchar(117) not null,
  Primary Key(C_CUSTKEY)
) distkey(c_custkey) sortkey(c_custkey);

=# show table orders;
Show Table DDL statement
CREATE TABLE public.orders (
    o_orderkey bigint NOT NULL ENCODE raw,
    o_custkey bigint ENCODE az64,
    o_orderstatus character(1) ENCODE lzo,
    o_totalprice numeric(12,2) ENCODE az64,
    o_orderdate date ENCODE raw,
    o_orderpriority character(15) ENCODE lzo,
    o_clerk character(15) ENCODE lzo,
    o_shippriority integer ENCODE az64,
    o_comment character varying(79) ENCODE lzo,
    PRIMARY KEY (o_orderkey)
)
DISTSTYLE AUTO
SORTKEY ( o_orderdate, o_orderkey );
(1 row)

=# copy customer from 's3://redshift-downloads/TPC-H/2.18/100GB/customer/' iam_role 'arn:aws:iam::542203247656:role/redshift-spectrum-s3-fullaccess' delimiter '|' region 'us-east-1';

=# select database, schema, "table", diststyle, sortkey1, size, tbl_rows, unsorted, stats_off, vacuum_sort_benefit from svv_table_info where "table" = 'customer';
database|schema|table|diststyle|sortkey1|size|tbl_rows|unsorted|stats_off|vacuum_sort_benefit
dev|public|customer|KEY(c_custkey)|c_custkey|1597|15000000|0.00|0.00|0.00
(1 row)

EVEN 分散になったテーブルに対して、PK で結合するクエリを繰り返し実行すると KEY 分散に変わるか

pgbench をインストールする

$ sudo dnf install -y postgresql15-contrib

SQL スクリプト（join_lineitem_orders.sql）を作成する

set enable_result_cache_for_session=off;
select sum(l_quantity) from lineitem, orders where l_orderkey = o_orderkey;

pgbench で1万回クエリを実行する

$ nohup pgbench -r -c 2 -j 2 -n -t 10000 -f join_lineitem_orders.sql -U awsuser -h rs-ra3-4xl-4n.********.us-east-1.redshift.amazonaws.com -d dev -p 5439 &

クエリが実行されたことを確認する

=#  select generic_query_hash, min(query_text), count(query_id), min(start_time) start_time_min, max(start_time) start_time_max  from sys_query_history where generic_query_hash = 'w9k2tvZlyUQ=' group by generic_query_hash;
generic_query_hash|min|count|start_time_min|start_time_max
w9k2tvZlyUQ=                            |select sum(l_quantity) from lineitem, orders where l_orderkey = o_orderkey;|9144|2025-07-18 16:57:05.797432|2025-07-19 15:00:56.211421
(1 row)

分散スタイルは EVEN のまま

=# select database, schema, "table", diststyle, sortkey1, size, tbl_rows, unsorted, stats_off, vacuum_sort_benefit from svv_table_info where "table" in ('orders', 'lineitem');
database|schema|table|diststyle|sortkey1|size|tbl_rows|unsorted|stats_off|vacuum_sort_benefit
dev|public|lineitem|AUTO(EVEN)|l_shipdate|30100|660023955|100.00|100.00|0.00
dev|public|orders|AUTO(EVEN)|o_orderdate|7969|165000001|100.00|100.00|0.00
(2 rows)

分散スタイルの自動的な推奨や変更がないか確認する。

=# select * from SVV_ALTER_TABLE_RECOMMENDATIONS;
type|database|table_id|group_id|ddl|auto_eligible
(0 rows)

=# select * from svv_alter_table_recommendations a, svv_table_info b
where a.table_id = b. table_id
and b."table" in ('orders', 'lineitem');
type|database|table_id|group_id|ddl|auto_eligible|database|schema|table_id|table|encoded|diststyle|sortkey1|max_varchar|sortkey1_enc|sortkey_num|size|pct_used|empty|unsorted|stats_off|tbl_rows|skew_sortkey1|skew_rows|estimated_visible_rows|risk_event|vacuum_sort_benefit|create_time
(0 rows)

参考

テーブルの分散スタイルを表示するには、PG_CLASS_INFO ビューまたは SVV_TABLE_INFO ビューに対してクエリを実行します。
テーブルの現在の分散スタイルは、PG_CLASS_INFO の RELEFFECTIVEDISTSTYLE 列に示されます。テーブルが自動分散を使用する場合、RELEFFECTIVEDISTSTYLE は 10、11、または 12 です。これは、効率的な分散スタイルが AUTO (ALL)、AUTO (EVEN)、または AUTO (KEY) のどれであるかを示します。テーブルが自動分散を使用する場合、分散スタイルには当初 AUTO (ALL) が表示され、その後テーブルが大きくなると AUTO (EVEN) または AUTO (KEY) に変更される場合があります
分散スタイルの表示 - Amazon Redshift