svl_query_summary.is_rrscan では判断できない。rows_pre_filter - rows で判断する。
ソートキーがないとき
- テーブルを作成する
CREATE TABLE lineorder ( lo_orderkey integer not null, lo_linenumber integer not null, lo_custkey integer not null, lo_partkey integer not null, lo_suppkey integer not null, lo_orderdate integer not null, lo_orderpriority varchar(15) not null, lo_shippriority varchar(1) not null, lo_quantity integer not null, lo_extendedprice integer not null, lo_ordertotalprice integer not null, lo_discount integer not null, lo_revenue integer not null, lo_supplycost integer not null, lo_tax integer not null, lo_commitdate integer not null, lo_shipmode varchar(10) not null );
- データをロードする
copy lineorder from 's3://awssampledb/ssbgz/lineorder' iam_role 'arn:aws:iam::123456789012:role/redshift-spectrum-s3-fullaccess' gzip compupdate off region 'ap-norteast-1';
- クエリを実行する
select count(*) from lineorder where lo_shipmode = 'AIR' or lo_shipmode = 'SHIP'; count ----------- 171447349 (1 row)
- クエリIDを取得する
SELECT PG_LAST_QUERY_ID(); \gset pg_last_query_id ------------------ 15084118 (1 row)
- svl_query_summary.is_rrscan を確認
- ソートキーを指定していないが、is_rrscan=t(rue) になっている。
- rows（フィルタ後の行数）が 171447349 に対して、rows_pre_filter（ストレージから読んだ行数）が 600037902 と不要な行を 428590553 読んでいる。
select query, stm, seg, step, maxtime, avgtime, rows, bytes, lpad(' ',stm+seg+step) || label as label, is_diskbased, workmem, is_rrscan, is_delayed_scan, rows_pre_filter from svl_query_summary where query = :pg_last_query_id order by stm, seg, step; query | stm | seg | step | maxtime | avgtime | rows | bytes | label | is_diskbased | workmem | is_rrscan | is_delayed_scan | rows_pre_filter ----------+-----+-----+------+---------+---------+-----------+------------+----------------------------------------------+--------------+---------+-----------+-----------------+----------------- 15084118 | 0 | 0 | 0 | 2692090 | 1903949 | 171447349 | 2057368188 | scan tbl=621306 name=lineorder | f | 0 | t | f | 600037902 15084118 | 0 | 0 | 1 | 2692090 | 1903949 | 171447349 | 0 | project | f | 0 | f | f | 0 15084118 | 0 | 0 | 2 | 2692090 | 1903949 | 171447349 | 0 | project | f | 0 | f | f | 0 15084118 | 0 | 0 | 3 | 2692090 | 1903949 | 8 | 64 | aggr tbl=1043 | f | 0 | f | f | 0 15084118 | 1 | 1 | 0 | 441 | 313 | 8 | 64 | scan tbl=1043 name=Internal Worktable | f | 0 | f | f | 0 15084118 | 1 | 1 | 1 | 441 | 313 | 8 | 64 | return | f | 0 | f | f | 0 15084118 | 1 | 2 | 0 | 1219 | 1219 | 8 | 64 | scan tbl=280145 name=Internal Worktable | f | 0 | f | f | 0 15084118 | 1 | 2 | 1 | 1219 | 1219 | 1 | 16 | aggr tbl=1048 | f | 0 | f | f | 0 15084118 | 2 | 3 | 0 | 356 | 356 | 1 | 16 | scan tbl=1048 name=Internal Worktable | f | 0 | f | f | 0 15084118 | 2 | 3 | 1 | 356 | 356 | 1 | 0 | project | f | 0 | f | f | 0 15084118 | 2 | 3 | 2 | 356 | 356 | 1 | 0 | project | f | 0 | f | f | 0 15084118 | 2 | 3 | 3 | 356 | 356 | 1 | 15 | return | f | 0 | f | f | 0
フィルタキーをソートキーに指定した場合
- ソートキーを指定する。
alter table lineorder alter compound sortkey (lo_shipmode);
- VACUUM する。
vacuum full lineorder boost;
- クエリを実行する
select count(*) from lineorder where lo_shipmode = 'AIR' or lo_shipmode = 'SHIP'; count ----------- 171447349 (1 row)
- クエリIDを取得する
SELECT PG_LAST_QUERY_ID(); \gset pg_last_query_id ------------------ 15084299 (1 row)
- svl_query_summary を確認
- is_rrscan=t(rue) になっている。
- rows（フィルタ後の行数）が 171447349 に対して、rows_pre_filter（ストレージから読んだ行数）が 172376110 と不要な行の読み込み（928761）が大幅に減っている。
select query, stm, seg, step, maxtime, avgtime, rows, bytes, lpad(' ',stm+seg+step) || label as label, is_diskbased, workmem, is_rrscan, is_delayed_scan, rows_pre_filter from svl_query_summary where query = :pg_last_query_id order by stm, seg, step; query | stm | seg | step | maxtime | avgtime | rows | bytes | label | is_diskbased | workmem | is_rrscan | is_delayed_scan | rows_pre_filter ----------+-----+-----+------+---------+---------+-----------+------------+----------------------------------------------+--------------+---------+-----------+-----------------+----------------- 15084299 | 0 | 0 | 0 | 182136 | 125954 | 171447349 | 3428946980 | scan tbl=621306 name=lineorder | f | 0 | t | f | 172376110 15084299 | 0 | 0 | 1 | 182136 | 125954 | 171447349 | 0 | project | f | 0 | f | f | 0 15084299 | 0 | 0 | 2 | 182136 | 125954 | 171447349 | 0 | project | f | 0 | f | f | 0 15084299 | 0 | 0 | 3 | 182136 | 125954 | 8 | 64 | aggr tbl=1043 | f | 0 | f | f | 0 15084299 | 1 | 1 | 0 | 452 | 305 | 8 | 64 | scan tbl=1043 name=Internal Worktable | f | 0 | f | f | 0 15084299 | 1 | 1 | 1 | 452 | 305 | 8 | 64 | return | f | 0 | f | f | 0 15084299 | 1 | 2 | 0 | 1156 | 1156 | 8 | 64 | scan tbl=280786 name=Internal Worktable | f | 0 | f | f | 0 15084299 | 1 | 2 | 1 | 1156 | 1156 | 1 | 16 | aggr tbl=1048 | f | 0 | f | f | 0 15084299 | 2 | 3 | 0 | 321 | 321 | 1 | 16 | scan tbl=1048 name=Internal Worktable | f | 0 | f | f | 0 15084299 | 2 | 3 | 1 | 321 | 321 | 1 | 0 | project | f | 0 | f | f | 0 15084299 | 2 | 3 | 2 | 321 | 321 | 1 | 0 | project | f | 0 | f | f | 0 15084299 | 2 | 3 | 3 | 321 | 321 | 1 | 15 |
