ablog

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

Amazon Redshift で View に対するクエリのフィルタ条件の push down について検証してみた

AWS Redshift

English:
Similarly, as you load new time periods into new tables, add the new tables to the view. To signal the optimizer to skip the scan on tables that don't match the query filter, your view definition filters for the date range that corresponds to each table.
日本語:
同様に、新しい期間を新しいテーブルにロードするとき、新しいテーブルをこのビューに追加します。クエリフィルタと一致しないテーブルでスキャンをスキップするようオプティマイザにシグナルを送信するには、ビュー定義で各テーブルに対応する日付範囲をフィルタします。
Use time-series tables - Amazon Redshift

の意味について検証してみた。

結論

ビュー定義で各テーブルの日付列の最小値と最大値しておくと、クエリで日付列でフィルタ条件を指定すると、該当しないテーブルのブロックの読込みをスキップできることを確認できた。注意点としては、ビュー定義でテーブルの実データの最小値と最大値以外のフィルタ条件を記述すると不要なテーブルへのアクセスをスキップしてくれない。

検証ポイント

以下の View に対して、

create view union_view as 
select * from orders1 where o_orderdate between  '1992-01-01' and '1998-08-02'
union all
select * from orders2 where o_orderdate  between '2002-01-01' and '2008-08-02'
union all
select * from orders3 where o_orderdate  between '2012-01-01' and '2018-08-02';

次のようなクエリを発行した場合に、必要なテーブル以外へのアクセスをスキップするかどうか。

# orders1 のみへのアクセスを期待 → 期待通りの結果
select count(*) from union_view where o_orderdate between  '1992-04-01' and '1993-03-31';
# orders2 のみへのアクセスを期待 → 期待通りの結果
select count(*) from union_view where o_orderdate = '2006-12-17';

検証結果

select count(*) from union_view where o_orderdate between '1992-04-01' and '1993-03-31'

クエリを実行する

select count(*) from union_view where o_orderdate between  '1992-04-01' and '1993-03-31';
count
22754854

query_id を取得する

select pg_last_query_id();
\gset
pg_last_query_id
7517779
(1 row)

select distinct b.query_id as sys_query_id
from stl_query a, sys_query_history b
where a.xid = b.transaction_id
and a.query = :pg_last_query_id;
\gset
sys_query_id
7517777
(1 row)

SVL_QUERY_SUMMARY でアクセステーブルを確認する。

select * from SVL_QUERY_SUMMARY where query = :pg_last_query_id and label like '%orders%' ;

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|7517779|4|4|0|629|443|0|0|||scan   tbl=1881688 name=orders3|f|0|t|f|0 ★ rows_pre_filter=0 でブロックを読み込んでいない
100|7517779|0|0|0|3389|2423|22754854|273058248|||scan   tbl=1865304 name=orders1|f|0|t|f|25160160
100|7517779|2|2|0|593|503|0|0|||scan   tbl=1865308 name=orders2|f|0|t|f|0 ★ rows_pre_filter=0 でブロックを読み込んでいない
(3 rows)

select count(*) from union_view where o_orderdate = '2006-12-17'

クエリを実行する

select count(*) from union_view where o_orderdate = '2006-12-17';
count
62108
(1 row)

query_id を取得する

select pg_last_query_id();
\gset
pg_last_query_id
7517820
(1 row)

select distinct b.query_id as sys_query_id
from stl_query a, sys_query_history b
where a.xid = b.transaction_id
and a.query = :pg_last_query_id;
\gset
sys_query_id
7517818
(1 row)

SVL_QUERY_SUMMARY でアクセステーブルを確認する。

select * from SVL_QUERY_SUMMARY where query = :pg_last_query_id and label like '%orders%';

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|7517820|2|2|0|848|725|62108|745296|||scan   tbl=1865308 name=orders2|f|0|t|f|4193360
100|7517820|0|0|0|1154|1003|0|0|||scan   tbl=1865304 name=orders1|f|0|t|f|0 ★ rows_pre_filter=0 でブロックを読み込んでいない
100|7517820|4|4|0|521|413|0|0|||scan   tbl=1881688 name=orders3|f|0|t|f|0 ★ rows_pre_filter=0 でブロックを読み込んでいない
(3 rows)

準備手順

テーブルを作成する

create table orders1 (
  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)
) distkey(o_orderkey) sortkey(o_orderdate) ;

create table orders2 (
  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)
) distkey(o_orderkey) sortkey(o_orderdate) ;

create table orders3 (
  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)
) distkey(o_orderkey) sortkey(o_orderdate) ;

データをロードする

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

insert into orders2 (
        select
                o_orderkey,
                o_custkey,
                o_orderstatus,
                o_totalprice,
                dateadd(year, 10, o_orderdate),
                o_orderpriority,
                o_clerk,
                o_shippriority,
                o_comment
        from orders1
);

insert into orders3 (
        select
                o_orderkey,
                o_custkey,
                o_orderstatus,
                o_totalprice,
                dateadd(year, 20, o_orderdate),
                o_orderpriority,
                o_clerk,
                o_shippriority,
                o_comment
        from orders1
);

orders1-3 の o_orderdate がかぶってないことを確認する

select min(o_orderdate), max(o_orderdate) from orders1;
min|max
1992-01-01|1998-08-02
(1 row)

select min(o_orderdate), max(o_orderdate) from orders2;
min|max
2002-01-01|2008-08-02
(1 row)

select min(o_orderdate), max(o_orderdate) from orders3;
min|max
2012-01-01|2018-08-02

View を作成する

create view union_view as 
select * from orders1 where o_orderdate between  '1992-01-01' and '1998-08-02'
union all
select * from orders2 where o_orderdate  between '2002-01-01' and '2008-08-02'
union all
select * from orders3 where o_orderdate  between '2012-01-01' and '2018-08-02';

ANALYZE する

analyze orders1;
analyze orders2;
analyze orders3;

テーブルの状態を確認する。

select database, schema, "table", diststyle, sortkey1, size, tbl_rows, unsorted, stats_off, vacuum_sort_benefit from svv_table_info where "table" in ('orders1', 'orders2', 'orders3');

database|schema|table|diststyle|sortkey1|size|tbl_rows|unsorted|stats_off|vacuum_sort_benefit
dev|public|orders2|KEY(o_orderkey)|o_orderdate|6833|150000000|0.00|0.00|0.00
dev|public|orders3|KEY(o_orderkey)|o_orderdate|6833|150000000|0.00|0.00|0.00
dev|public|orders1|KEY(o_orderkey)|o_orderdate|6833|150000000|0.00|0.00|0.00
(3 rows)