Amazon Redshift で View に対するクエリのフィルタ条件の push down について検証してみた - ablog
と関連して、時系列テーブルを UNION ALL した View 経由で他のテーブルと結合した場合と View 内のテーブルを直接結合した場合のオペレーションを比較してみた。
- UNION ALL している View 経由で結合
- 必要なテーブルのみ読込んでいる（order1、order2）
- HASH JOIN で結合している
- テーブルを直接結合
- HASH JOIN で結合している
workmem の合計は View 経由で結合したほうが多いということはない。
検証手順・結果
- UNION ALL している View 経由で結合
=# select count(*) from union_view a, lineitem b where a.o_orderdate between '1998-01-01' and '2002-12-31' and a.o_orderkey = b.l_orderkey; count 159065861 (1 row) =# select pg_last_query_id(); \gset pg_last_query_id 8329270 (1 row) =# 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|8329270|0|0|0|1837782|1778388|36152222|289217776|36152222|289217776|scan tbl=1996325502 name=Internal Worktable|f|0|f|f|0 100|8329270|0|0|1|1837782|1778388|36152222|0|36152222|0|project |f|0|f|f|0 100|8329270|0|0|2|1837782|1778388|36152222|289217776|36152222|289217776|bcast |f|0|f|f|0 100|8329270|0|0|3|1837782|1778388|13341863|234406420|13341863|234406420|scan tbl=1865304 name=orders1|f|0|t|f|15812480 100|8329270|0|0|4|1837782|1778388|13341863|0|13341863|0|project |f|0|f|f|0 100|8329270|0|0|5|1837782|1778388|13341863|0|13341863|0|project |f|0|f|f|0 100|8329270|0|0|8|1837782|1778388|22810359|390077580|22810359|390077580|scan tbl=1865308 name=orders2|f|0|t|f|25160160 100|8329270|0|0|9|1837782|1778388|22810359|0|22810359|0|project |f|0|f|f|0 100|8329270|0|0|10|1837782|1778388|22810359|0|22810359|0|project |f|0|f|f|0 100|8329270|0|0|13|1837782|1778388|0|0|0|0|scan tbl=1881688 name=orders3|f|0|t|f|0 100|8329270|0|0|14|1837782|1778388|0|0|0|0|project |f|0|f|f|0 100|8329270|0|0|15|1837782|1778388|0|0|0|0|project |f|0|f|f|0 100|8329270|0|1|0|1912269|470683|144608888|1156871104|144608888|1156871104|scan tbl=1131 name=Internal Worktable|f|0|f|f|0 100|8329270|0|1|1|1912269|470683|144608888|0|144608888|0|project |f|0|f|f|0 100|8329270|0|1|2|1912269|470683|144608888|2313742208|144608888|2313742208|hash tbl=679179479|f|136338997248|f|f|0 100|8329270|1|2|0|1590062|1462358|660023955|10560383280|660023955|10560383280|scan tbl=1889880 name=lineitem|f|0|t|f|660023955 100|8329270|1|2|1|1590062|1462358|660023955|0|660023955|0|project |f|0|f|f|0 100|8329270|1|2|2|1590062|1462358|660023955|0|660023955|0|project |f|0|f|f|0 100|8329270|1|2|3|1590062|1462358|159065861|0|159065861|0|hjoin tbl=679179479|f|0|f|f|0 100|8329270|1|2|4|1590062|1462358|159065861|0|159065861|0|project |f|0|f|f|0 100|8329270|1|2|5|1590062|1462358|159065861|0|159065861|0|project |f|0|f|f|0 100|8329270|1|2|6|1590062|1462358|16|128|16|128|aggr tbl=506910662|f|0|f|f|0 100|8329270|2|3|0|414|302|16|128|||scan tbl=506910662 name=Internal Worktable|f|0|f|f|0 100|8329270|2|3|1|414|302|16|128|||return |f|0|f|f|0 100|8329270|2|4|0|1705|1705|16|128|||scan tbl=1132 name=Internal Worktable|f|0|f|f|0 100|8329270|2|4|1|1705|1705|1|16|||aggr tbl=111969763|f|0|f|f|0 100|8329270|3|5|0|279|279|1|16|||scan tbl=111969763 name=Internal Worktable|f|0|f|f|0 100|8329270|3|5|1|279|279|1|0|||project |f|0|f|f|0 100|8329270|3|5|2|279|279|1|0|||project |f|0|f|f|0 100|8329270|3|5|3|279|279|1|15|||return |f|0|f|f|0 (30 rows)
- View 内のテーブルを直接後結合
=# select count(*) from (select * from orders1 a, lineitem b where a.o_orderdate between '1998-01-01' and '1998-12-31' and a.o_orderkey = b.l_orderkey UNION ALL select * from orders2 c, lineitem d where c.o_orderdate between '2002-01-01' and '2002-12-31' and c.o_orderkey = d.l_orderkey ); count 159065861 (1 row) =# select pg_last_query_id(); \gset pg_last_query_id 8329300 (1 row) =# 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|8329300|0|0|0|798613|585417|13341863|266837260|||scan tbl=1865304 name=orders1|f|0|t|f|15812480 100|8329300|0|0|1|798613|585417|13341863|0|||project |f|0|f|f|0 100|8329300|0|0|2|798613|585417|13341863|106734904|||bcast |f|0|f|f|0 100|8329300|0|1|0|1119515|264335|53367452|426939616|53367452|426939616|scan tbl=1273 name=Internal Worktable|f|0|f|f|0 100|8329300|0|1|1|1119515|264335|53367452|0|53367452|0|project |f|0|f|f|0 100|8329300|0|1|2|1119515|264335|53367452|853879232|53367452|853879232|hash tbl=1276191403|f|136288665600|f|f|0 100|8329300|1|2|0|1001400|777765|65272511|1044360176|65272511|1044360176|scan tbl=1889880 name=lineitem|f|0|t|f|660023955 100|8329300|1|2|1|1001400|777765|65272511|0|65272511|0|project |f|0|f|f|0 100|8329300|1|2|2|1001400|777765|65272511|0|65272511|0|project |f|0|f|f|0 100|8329300|1|2|3|1001400|777765|58699933|0|58699933|0|hjoin tbl=1276191403|f|0|f|f|0 100|8329300|1|2|4|1001400|777765|58699933|0|58699933|0|project |f|0|f|f|0 100|8329300|1|2|5|1001400|777765|58699933|0|58699933|0|project |f|0|f|f|0 100|8329300|1|2|6|1001400|777765|16|128|16|128|aggr tbl=1721182467|f|0|f|f|0 100|8329300|2|3|0|1356|451|16|128|||scan tbl=1721182467 name=Internal Worktable|f|0|f|f|0 100|8329300|2|3|1|1356|451|16|0|||project |f|0|f|f|0 100|8329300|2|3|2|1356|451|16|0|||project |f|0|f|f|0 100|8329300|2|3|4|1356|451|16|256|||save tbl=2078006567|f|136276082688|f|f|0 100|8329300|3|4|0|1221330|1195449|22810359|456207180|22810359|456207180|scan tbl=1865308 name=orders2|f|0|t|f|25160160 100|8329300|3|4|1|1221330|1195449|22810359|0|22810359|0|project |f|0|f|f|0 100|8329300|3|4|2|1221330|1195449|22810359|182482872|22810359|182482872|bcast |f|0|f|f|0 100|8329300|3|5|0|1640567|381783|91241436|729931488|91241436|729931488|scan tbl=1278 name=Internal Worktable|f|0|f|f|0 100|8329300|3|5|1|1640567|381783|91241436|0|91241436|0|project |f|0|f|f|0 100|8329300|3|5|2|1640567|381783|91241436|1459862976|91241436|1459862976|hash tbl=815569906|f|136276082688|f|f|0 100|8329300|4|6|0|1308511|1057518|617877533|9886040528|617877533|9886040528|scan tbl=1889880 name=lineitem|f|0|t|f|660023955 100|8329300|4|6|1|1308511|1057518|617877533|0|617877533|0|project |f|0|f|f|0 100|8329300|4|6|2|1308511|1057518|617877533|0|617877533|0|project |f|0|f|f|0 100|8329300|4|6|3|1308511|1057518|100365928|0|100365928|0|hjoin tbl=815569906|f|0|f|f|0 100|8329300|4|6|4|1308511|1057518|100365928|0|100365928|0|project |f|0|f|f|0 100|8329300|4|6|5|1308511|1057518|100365928|0|100365928|0|project |f|0|f|f|0 100|8329300|4|6|6|1308511|1057518|16|128|16|128|aggr tbl=1233569921|f|0|f|f|0 100|8329300|5|7|0|399|314|16|128|||scan tbl=1233569921 name=Internal Worktable|f|0|f|f|0 100|8329300|5|7|1|399|314|16|0|||project |f|0|f|f|0 100|8329300|5|7|2|399|314|16|0|||project |f|0|f|f|0 100|8329300|5|7|4|399|314|16|256|||save tbl=2078006567|f|136364163072|f|f|0 100|8329300|6|8|0|1084|373|32|512|||scan tbl=2078006567 name=Internal Worktable|f|0|f|f|0 100|8329300|6|8|1|1084|373|32|0|||project |f|0|f|f|0 100|8329300|6|8|2|1084|373|32|0|||project |f|0|f|f|0 100|8329300|6|8|3|1084|373|16|256|||aggr tbl=1058197126|f|0|f|f|0 100|8329300|7|9|0|365|247|16|256|||scan tbl=1058197126 name=Internal Worktable|f|0|f|f|0 100|8329300|7|9|1|365|247|16|256|||return |f|0|f|f|0 100|8329300|7|10|0|1442|1442|16|256|||scan tbl=1279 name=Internal Worktable|f|0|f|f|0 100|8329300|7|10|1|1442|1442|1|16|||aggr tbl=157000364|f|0|f|f|0 100|8329300|8|11|0|239|239|1|16|||scan tbl=157000364 name=Internal Worktable|f|0|f|f|0 100|8329300|8|11|1|239|239|1|0|||project |f|0|f|f|0 100|8329300|8|11|2|239|239|1|0|||project |f|0|f|f|0 100|8329300|8|11|3|239|239|1|15|||return |f|0|f|f|0 (46 rows)
