計測
EVEN分散
=# select count(l_orderkey) from lineitem join orders on o_orderkey = l_orderkey and l_shipdate > '1996-01-01'; count ------------ 7518747994 (1 row) Time: 150000.148 ms (02:30.000)
- クエリID取得
=# select pg_last_query_id(); =# \gset
=# select * from svl_query_summary where query = :pg_last_query_id order by stm, seg, step; userid | query | stm | seg | step | maxtime | avgtime | rows | bytes | rate_row | rate_byte | label | is_diskbased | workmem | is_rrscan | is_delayed_scan | rows_pre_filter --------+--------+-----+-----+------+-----------+-----------+------------+--------------+-----------+------------+------------------------------------------+--------------+-------------+-----------+-----------------+----------------- 100 | 415448 | 0 | 0 | 0 | 14476986 | 13327871 | 4500000000 | 72000000000 | 321428571 | 5142857142 | scan tbl=129292 name=orders | f | 0 | f | f | 4500000000 100 | 415448 | 0 | 0 | 1 | 14476986 | 13327871 | 4500000000 | 0 | 321428571 | 0 | project | f | 0 | f | f | 0 100 | 415448 | 0 | 0 | 2 | 14476986 | 13327871 | 4500000000 | 36000000000 | 321428571 | 2571428571 | dist | f | 0 | f | f | 0 100 | 415448 | 0 | 1 | 0 | 14480805 | 14480455 | 4500000000 | 36000000000 | 321428571 | 2571428571 | scan tbl=62922 name=Internal Worktable | f | 0 | f | f | 0 100 | 415448 | 0 | 1 | 1 | 14480805 | 14480455 | 4500000000 | 0 | 321428571 | 0 | project | f | 0 | f | f | 0 100 | 415448 | 0 | 1 | 2 | 14480805 | 14480455 | 4500000000 | 72000000000 | 321428571 | 5142857142 | hash tbl=1045 | t | 18937282560 | f | f | 0 100 | 415448 | 1 | 2 | 0 | 57195956 | 47788360 | 7518747994 | 150374959880 | 131907859 | 2638157190 | scan tbl=129288 name=lineitem | f | 0 | t | f | 7529228386 100 | 415448 | 1 | 2 | 1 | 57195956 | 47788360 | 7518747994 | 0 | 131907859 | 0 | project | f | 0 | f | f | 0 100 | 415448 | 1 | 2 | 2 | 57195956 | 47788360 | 7518747994 | 60149983952 | 131907859 | 1055262876 | dist | f | 0 | f | f | 0 100 | 415448 | 1 | 3 | 0 | 132343885 | 127080345 | 7518747994 | 60149983952 | 56960212 | 455681696 | scan tbl=62940 name=Internal Worktable | f | 0 | f | f | 0 100 | 415448 | 1 | 3 | 1 | 132343885 | 127080345 | 7518747994 | 0 | 56960212 | 0 | project | f | 0 | f | f | 0 100 | 415448 | 1 | 3 | 2 | 132343885 | 127080345 | 7518747994 | 0 | 56960212 | 0 | hjoin tbl=1045 | f | 0 | f | f | 0 100 | 415448 | 1 | 3 | 3 | 132343885 | 127080345 | 7518747994 | 0 | 56960212 | 0 | project | f | 0 | f | f | 0 100 | 415448 | 1 | 3 | 4 | 132343885 | 127080345 | 7518747994 | 0 | 56960212 | 0 | project | f | 0 | f | f | 0 100 | 415448 | 1 | 3 | 5 | 132343885 | 127080345 | 16 | 128 | 0 | 0 | aggr tbl=1056 | f | 0 | f | f | 0 100 | 415448 | 2 | 4 | 0 | 579 | 397 | 16 | 128 | | | scan tbl=1056 name=Internal Worktable | f | 0 | f | f | 0 100 | 415448 | 2 | 4 | 1 | 579 | 397 | 16 | 128 | | | return | f | 0 | f | f | 0 100 | 415448 | 2 | 5 | 0 | 1464 | 1464 | 16 | 128 | | | scan tbl=63038 name=Internal Worktable | f | 0 | f | f | 0 100 | 415448 | 2 | 5 | 1 | 1464 | 1464 | 1 | 16 | | | aggr tbl=1044 | f | 0 | f | f | 0 100 | 415448 | 3 | 6 | 0 | 371 | 371 | 1 | 16 | | | scan tbl=1044 name=Internal Worktable | f | 0 | f | f | 0 100 | 415448 | 3 | 6 | 1 | 371 | 371 | 1 | 0 | | | project | f | 0 | f | f | 0 100 | 415448 | 3 | 6 | 2 | 371 | 371 | 1 | 0 | | | project | f | 0 | f | f | 0 100 | 415448 | 3 | 6 | 3 | 371 | 371 | 1 | 16 | | | return | f | 0 | f | f | 0
=# select * from svl_query_metrics where query = :pg_last_query_id order by dimension; userid | query | service_class | dimension | segment | step | step_label | query_cpu_time | query_blocks_read | query_execution_time | query_cpu_usage_percent | query_temp_blocks_to_disk | segment_execution_time | cpu_skew | io_skew | scan_row_count | join_row_count | nested_loop_join_row_count | return_row_count | spectrum_scan_row_count | spectrum_scan_size_mb | query_queue_time | service_class_name --------+--------+---------------+-----------+---------+------+------------+----------------+-------------------+----------------------+-------------------------+---------------------------+------------------------+----------+---------+----------------+----------------+----------------------------+------------------+-------------------------+-----------------------+------------------+------------------------------------------------------------------ 100 | 415448 | 100 | query | | | | 1371 | 121346 | 150 | 42.67 | 157564 | | | | | | | | | | | Default queue 100 | 415448 | 100 | segment | 0 | | | | | 150 | | | 15 | 1.05 | 1.06 | | | | | | | | Default queue 100 | 415448 | 100 | segment | 3 | | | | | 150 | | | 132 | 1.07 | | | | | | | | | Default queue 100 | 415448 | 100 | segment | 2 | | | | | 150 | | | 58 | 1.06 | 1.01 | | | | | | | | Default queue 100 | 415448 | 100 | segment | 1 | | | | | 150 | | | 15 | 1.22 | | | | | | | | | Default queue 100 | 415448 | 100 | step | 2 | 2 | dist | | | 150 | | | | | | | | | | | | | Default queue 100 | 415448 | 100 | step | 0 | 1 | project | | | 150 | | | | | | | | | | | | | Default queue 100 | 415448 | 100 | step | 0 | 2 | dist | | | 150 | | | | | | | | | | | | | Default queue 100 | 415448 | 100 | step | 3 | 1 | project | | | 150 | | | | | | | | | | | | | Default queue 100 | 415448 | 100 | step | 3 | 2 | hjoin | | | 150 | | | | | | | 7451618749 | | | | | | Default queue 100 | 415448 | 100 | step | 1 | 1 | project | | | 150 | | | | | | | | | | | | | Default queue 100 | 415448 | 100 | step | 2 | 1 | project | | | 150 | | | | | | | | | | | | | Default queue 100 | 415448 | 100 | step | 3 | 4 | project | | | 150 | | | | | | | | | | | | | Default queue 100 | 415448 | 100 | step | 3 | 0 | scan | | | 150 | | | | | | 7518747994 | | | | | | | Default queue 100 | 415448 | 100 | step | 3 | 3 | project | | | 150 | | | | | | | | | | | | | Default queue 100 | 415448 | 100 | step | 1 | 0 | scan | | | 150 | | | | | | 4313724928 | | | | | | | Default queue 100 | 415448 | 100 | step | 1 | 2 | hash | | | 150 | | | | | | | | | | | | | Default queue 100 | 415448 | 100 | step | 0 | 0 | scan | | | 150 | | | | | | 4272126596 | | | | | | | Default queue 100 | 415448 | 100 | step | 2 | 0 | scan | | | 150 | | | | | | 7425971431 | | | | | | | Default queue 100 | 415448 | 100 | step | 3 | 5 | aggr | | | 150 | | | | | | | | | | | | | Default queue
=# select * from stl_explain where query = :pg_last_query_id order by 1,2; userid | query | nodeid | parentid | plannode | info --------+--------+--------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 100 | 415448 | 4 | 2 | -> XN Hash (cost=44999997.44..44999997.44 rows=4499999744 width=8) | 100 | 415448 | 3 | 2 | -> XN Seq Scan on lineitem (cost=0.00..94678474.02 rows=7574277922 width=8) | Filter: (l_shipdate > '1996-01-01'::date) 100 | 415448 | 2 | 1 | -> XN Hash Join DS_DIST_BOTH (cost=72336407.04..1448054556889.29 rows=7574277923 width=8) | Outer Dist Key: lineitem.l_orderkey Inner Dist Key: orders.o_orderkey Hash Cond: ("outer".l_orderkey = "inner".o_orderkey) 100 | 415448 | 1 | 0 | XN Aggregate (cost=1448073492584.10..1448073492584.10 rows=1 width=8) | 100 | 415448 | 5 | 4 | -> XN Seq Scan on orders (cost=0.00..44999997.44 rows=4499999744 width=8) |
KEY分散
=# select count(l_orderkey) from lineitem join orders on o_orderkey = l_orderkey and l_shipdate > '1996-01-01'; count ------------ 7518747994 (1 row) Time: 364.442 ms
- クエリID取得
=# select pg_last_query_id(); =# \gset
=# select * from svl_query_summary where query = :pg_last_query_id order by stm, seg, step; select * from svl_query_metrics where query = :pg_last_query_id order by dimension; userid | query | stm | seg | step | maxtime | avgtime | rows | bytes | rate_row | rate_byte | label | is_diskbased | workmem | is_rrscan | is_delayed_scan | rows_pre_filter --------+--------+-----+-----+------+---------+---------+------+-------+----------+-----------+-------------------------------------------------+--------------+---------+-----------+-----------------+----------------- 100 | 415534 | 0 | 0 | 0 | 238662 | 90184 | 1065 | 21300 | | | scan tbl=111386 name=mv_tbl__auto_mv_26366__0 | f | 0 | t | f | 2526 100 | 415534 | 0 | 0 | 1 | 238662 | 90184 | 1065 | 0 | | | project | f | 0 | f | f | 0 100 | 415534 | 0 | 0 | 2 | 238662 | 90184 | 1065 | 0 | | | project | f | 0 | f | f | 0 100 | 415534 | 0 | 0 | 3 | 238662 | 90184 | 16 | 256 | | | aggr tbl=1073 | f | 0 | f | f | 0 100 | 415534 | 1 | 1 | 0 | 563 | 374 | 16 | 256 | | | scan tbl=1073 name=Internal Worktable | f | 0 | f | f | 0 100 | 415534 | 1 | 1 | 1 | 563 | 374 | 16 | 256 | | | return | f | 0 | f | f | 0 100 | 415534 | 1 | 2 | 0 | 1383 | 1383 | 16 | 256 | | | scan tbl=63233 name=Internal Worktable | f | 0 | f | f | 0 100 | 415534 | 1 | 2 | 1 | 1383 | 1383 | 1 | 16 | | | aggr tbl=1078 | f | 0 | f | f | 0 100 | 415534 | 2 | 3 | 0 | 328 | 328 | 1 | 16 | | | scan tbl=1078 name=Internal Worktable | f | 0 | f | f | 0 100 | 415534 | 2 | 3 | 1 | 328 | 328 | 1 | 0 | | | project | f | 0 | f | f | 0 100 | 415534 | 2 | 3 | 2 | 328 | 328 | 1 | 0 | | | project | f | 0 | f | f | 0 100 | 415534 | 2 | 3 | 3 | 328 | 328 | 1 | 16 | | | return | f | 0 | f | f | 0
=# select * from stl_explain where query = :pg_last_query_id order by 1,2; userid | query | nodeid | parentid | plannode | info --------+--------+--------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 100 | 415587 | 1 | 0 | XN Aggregate (cost=34.24..34.24 rows=1 width=8) | 100 | 415587 | 2 | 1 | -> XN Seq Scan on mv_tbl__auto_mv_26366__0 derived_table1 (cost=0.00..31.58 rows=1066 width=8) | Filter: (grvar_1 > '1996-01-01'::date)
事前準備
共通
- ユーザーレベルで結果キャッシュを無効化
alter user awsuser set enable_result_cache_for_session = off;
Cloud-DWB-Derived-from-TPCH をそのまま
- データベース作成
create database tpch_3tb collate case_insensitive isolation level snapshot;
分散キーなし
- データベース作成
create database tpch_3tb_no_distkey collate case_insensitive isolation level snapshot;
- テーブル作成・データロード
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) ) diststyle even sortkey(c_custkey); create table lineitem ( l_orderkey int8 not null , l_partkey int8 not null, l_suppkey int4 not null, l_linenumber int4 not null, l_quantity numeric(12,2) not null, l_extendedprice numeric(12,2) not null, l_discount numeric(12,2) not null, l_tax numeric(12,2) not null, l_returnflag char(1) not null, l_linestatus char(1) not null, l_shipdate date not null , l_commitdate date not null, l_receiptdate date not null, l_shipinstruct char(25) not null, l_shipmode char(10) not null, l_comment varchar(44) not null, Primary Key(L_ORDERKEY, L_LINENUMBER) ) diststyle even sortkey(l_shipdate,l_orderkey) ; create table nation ( n_nationkey int4 not null, n_name char(25) not null , n_regionkey int4 not null, n_comment varchar(152) not null, Primary Key(N_NATIONKEY) ) diststyle even sortkey(n_nationkey) ; create table orders ( o_orderkey int8 not null, o_custkey int8 not null, o_orderstatus char(1) not null, o_totalprice numeric(12,2) not null, o_orderdate date not null, o_orderpriority char(15) not null, o_clerk char(15) not null, o_shippriority int4 not null, o_comment varchar(79) not null, Primary Key(O_ORDERKEY) ) diststyle even sortkey(o_orderdate, o_orderkey) ; create table part ( p_partkey int8 not null , p_name varchar(55) not null, p_mfgr char(25) not null, p_brand char(10) not null, p_type varchar(25) not null, p_size int4 not null, p_container char(10) not null, p_retailprice numeric(12,2) not null, p_comment varchar(23) not null, PRIMARY KEY (P_PARTKEY) ) diststyle even sortkey(p_partkey); create table partsupp ( ps_partkey int8 not null, ps_suppkey int4 not null, ps_availqty int4 not null, ps_supplycost numeric(12,2) not null, ps_comment varchar(199) not null, Primary Key(PS_PARTKEY, PS_SUPPKEY) ) diststyle even sortkey(ps_partkey); create table region ( r_regionkey int4 not null, r_name char(25) not null , r_comment varchar(152) not null, Primary Key(R_REGIONKEY) ) diststyle even sortkey(r_regionkey); create table supplier ( s_suppkey int4 not null, s_name char(25) not null, s_address varchar(40) not null, s_nationkey int4 not null, s_phone char(15) not null, s_acctbal numeric(12,2) not null, s_comment varchar(101) not null, Primary Key(S_SUPPKEY) ) diststyle even sortkey(s_suppkey); copy lineitem from 's3://redshift-downloads/TPC-H/2.18/3TB/lineitem/' iam_role 'arn:aws:iam::123456789012:role/redshift-spectrum-s3-fullaccess' delimiter '|' region 'us-east-1'; copy orders from 's3://redshift-downloads/TPC-H/2.18/3TB/orders/' iam_role 'arn:aws:iam::123456789012:role/redshift-spectrum-s3-fullaccess' delimiter '|' region 'us-east-1'; copy region from 's3://redshift-downloads/TPC-H/2.18/3TB/region/' iam_role 'arn:aws:iam::123456789012:role/redshift-spectrum-s3-fullaccess' delimiter '|' region 'us-east-1'; copy nation from 's3://redshift-downloads/TPC-H/2.18/3TB/nation/' iam_role 'arn:aws:iam::123456789012:role/redshift-spectrum-s3-fullaccess' delimiter '|' region 'us-east-1'; copy supplier from 's3://redshift-downloads/TPC-H/2.18/3TB/supplier/' iam_role 'arn:aws:iam::123456789012:role/redshift-spectrum-s3-fullaccess' delimiter '|' region 'us-east-1'; copy partsupp from 's3://redshift-downloads/TPC-H/2.18/3TB/partsupp/' iam_role 'arn:aws:iam::123456789012:role/redshift-spectrum-s3-fullaccess' delimiter '|' region 'us-east-1'; copy customer from 's3://redshift-downloads/TPC-H/2.18/3TB/customer/' iam_role 'arn:aws:iam::123456789012:role/redshift-spectrum-s3-fullaccess' delimiter '|' region 'us-east-1';
