Amazon Redshift で View にクエリの射影が Push down されることを検証した。参照元テーブルの全カラムにアクセスする View（select * form ...）に対して、select 句で特定カラムのみ指定するクエリを実行すると、指定したカラムのブロックのみ読込むことを確認できた。
検証ポイント
- SELECT句でテーブルの特定列のみを指定してクエリした場合、指定した列のブロックのみが読み込まれるが、View（内で SELECT 句で * を指定している）に対してクエリした場合に必要なブロックのみ読まれるかどうか検証する。
検証手順
- customer テーブルの定義を確認する。
dev=# show table dev.public.customer; Show Table DDL statement ----------------------------------------------------------------- CREATE TABLE public.customer ( + c_custkey integer NOT NULL ENCODE az64, + c_name character varying(25) NOT NULL ENCODE lzo, + c_address character varying(25) NOT NULL ENCODE lzo, + c_city character varying(10) NOT NULL ENCODE bytedict, + c_nation character varying(15) NOT NULL ENCODE raw, + c_region character varying(12) NOT NULL ENCODE bytedict, + c_phone character varying(15) NOT NULL ENCODE lzo, + c_mktsegment character varying(10) NOT NULL ENCODE bytedict+ ) + DISTSTYLE EVEN + SORTKEY ( c_nation, c_region ); (1 row)
- View と Late Binding View を作成する
create view dev.public.v_customer as select * from dev.public.customer; create view dev.public.lbv_customer as select * from dev.public.customer with no schema binding;
- テーブルのサイズを確認する
dev=# select tbl_rows,size from svv_table_info where "schema" = 'public' and "table" = 'customer'; tbl_rows | size ----------+------ 40502000 | 2560 (1 row)
- クエリを実行する。
\timing on \pset pager set enable_result_cache_for_session=off; unload ('select * from dev.public.customer') to's3://redshift-unload-az/customer/' iam_role 'arn:aws:iam::542203247656:role/redshift-spectrum-s3-fullaccess' allowoverwrite csv; unload ('select c_custkey from dev.public.customer') to's3://redshift-unload-az/customer_c_custkey/' iam_role 'arn:aws:iam::542203247656:role/redshift-spectrum-s3-fullaccess' allowoverwrite csv; unload ('select * from dev.public.v_customer') to's3://redshift-unload-az/v_customer/' iam_role 'arn:aws:iam::542203247656:role/redshift-spectrum-s3-fullaccess' allowoverwrite csv; unload ('select c_custkey from dev.public.v_customer') to's3://redshift-unload-az/v_customer_c_custkey/' iam_role 'arn:aws:iam::542203247656:role/redshift-spectrum-s3-fullaccess' allowoverwrite csv; unload ('select * from dev.public.lbv_customer') to's3://redshift-unload-az/lbv_customer/' iam_role 'arn:aws:iam::542203247656:role/redshift-spectrum-s3-fullaccess' allowoverwrite csv; unload ('select c_custkey from dev.public.lbv_customer') to's3://redshift-unload-az/lbv_customer_c_custkey/' iam_role 'arn:aws:iam::542203247656:role/redshift-spectrum-s3-fullaccess' allowoverwrite csv;
- 読込みブロック数を確認する。
dev=# select a.query, b.aborted, b.starttime, a.query_execution_time, a.query_blocks_read, substring(b.querytxt, 0, 100) query_text from svl_query_metrics_summary a, stl_query b where a.query = b.query and query_blocks_read > 0 and b.querytxt like '%unload%' order by b.starttime desc; query | aborted | starttime | query_execution_time | query_blocks_read | query_text ----------+---------+----------------------------+----------------------+-------------------+----------------------------------------------------------------------------------------------------- 56163810 | 0 | 2025-09-02 14:15:05.73585 | 3 | 337 | unload ('select c_custkey from dev.public.lbv_customer') to's3://redshift-unload/lbv_customer_c_ 56163798 | 0 | 2025-09-02 14:14:41.024237 | 23 | 2365 | unload ('select * from dev.public.lbv_customer') to's3://redshift-unload/lbv_customer/' iam_role 56163791 | 0 | 2025-09-02 14:14:17.156801 | 3 | 336 | unload ('select c_custkey from dev.public.v_customer') to's3://redshift-unload/v_customer_c_cust 56163777 | 0 | 2025-09-02 14:13:42.468596 | 22 | 2389 | unload ('select * from dev.public.v_customer') to's3://redshift-unload/v_customer/' iam_role '' 56163754 | 0 | 2025-09-02 14:11:29.910357 | 3 | 336 | unload ('select c_custkey from dev.public.customer') to's3://redshift-unload/customer_c_custkey/ 56163721 | 0 | 2025-09-02 14:08:57.289482 | 28 | 2400 | unload ('select * from dev.public.customer') to's3://redshift-unload/customer/' iam_role '' allo (6 rows)
