Hi,
We are analyzing performance of vertica external tables, we have used parquet file format with snappy compression and we have around 119353 s3 partition objects having total 23.2 GB+ data, we are facing serious performance issues even when we are passing all the partition columns in where clause.
below is our external table script:
simple select statement with ~20 cols with all the partition cols in where clause takes around 30+ secs of time.
Select record_code,station_code, dim_date_time_id,hh_hut_put_impression,hh_universe_estimate,hh_impression,c25_hut_put_impression,c25_universe_estimate ,c25_impression,c611_hut_put_impression,c611_universe_estimate,c611_impression,m1214_hut_put_impression from common.fact_rating_quarter_hour_20181_qa where station_code=1001 and reporting_year=2018 and reporting_month=12 and market_code=202 and reporting_service=1 and sample_type_int=1 and sub_sample_indicator=2 and rating_stream_int=7 ;
create external table common.fact_rating_quarter_hour_20181_qa
(
record_code varchar(500),
playback_type varchar(500),
metroahousehold int ,
metrobhousehold int ,
hh_share float,
hh_hut_put float,
hh_hut_put_impression float,
hh_rating float,
hh_universe_estimate int ,
hh_impression float,
c25_share float,
c25_hut_put float,
c25_hut_put_impression float,
c25_rating float,
c25_universe_estimate int ,
c25_impression float,
c611_share float,
c611_hut_put float,
c611_hut_put_impression float,
c611_rating float,
c611_universe_estimate int ,
c611_impression float,
m1214_share float,
m1214_hut_put float,
m1214_hut_put_impression float,
m1214_rating float,
m1214_universe_estimate int ,
m1214_impression float,
m1517_share float,
m1517_hut_put float,
m1517_hut_put_impression float,
m1517_rating float,
m1517_universe_estimate int ,
m1517_impression float,
m1820_share float,
m1820_hut_put float,
m1820_hut_put_impression float,
m1820_rating float,
m1820_universe_estimate int ,
m1820_impression float,
m2124_share float,
m2124_hut_put float,
m2124_hut_put_impression float,
m2124_rating float,
m2124_universe_estimate int ,
m2124_impression float,
m2534_share float,
m2534_hut_put float,
m2534_hut_put_impression float,
m2534_rating float,
m2534_universe_estimate int ,
m2534_impression float,
m3549_share float,
m3549_hut_put float,
m3549_hut_put_impression float,
m3549_rating float,
m3549_universe_estimate int ,
m3549_impression float,
m5054_share float,
m5054_hut_put float,
m5054_hut_put_impression float,
m5054_rating float,
m5054_universe_estimate int ,
m5054_impression float,
m5564_share float,
m5564_hut_put float,
m5564_hut_put_impression float,
m5564_rating float,
m5564_universe_estimate int ,
m5564_impression float,
m65plus_share float,
m65plus_hut_put float,
m65plus_hut_put_impression float,
m65plus_rating float,
m65plus_universe_estimate int ,
m65plus_impression float,
f1214_share float,
f1214_hut_put float,
f1214_hut_put_impression float,
f1214_rating float,
f1214_universe_estimate int ,
f1214_impression float,
f1517_share float,
f1517_hut_put float,
f1517_hut_put_impression float,
f1517_rating float,
f1517_universe_estimate int ,
f1517_impression float,
f1820_share float,
f1820_hut_put float,
f1820_hut_put_impression float,
f1820_rating float,
f1820_universe_estimate int ,
f1820_impression float,
f2124_share float,
f2124_hut_put float,
f2124_hut_put_impression float,
f2124_rating float,
f2124_universe_estimate int ,
f2124_impression float,
f2534_share float,
f2534_hut_put float,
f2534_hut_put_impression float,
f2534_rating float,
f2534_universe_estimate int ,
f2534_impression float,
f3549_share float,
f3549_hut_put float,
f3549_hut_put_impression float,
f3549_rating float,
f3549_universe_estimate int ,
f3549_impression float,
f5054_share float,
f5054_hut_put float,
f5054_hut_put_impression float,
f5054_rating float,
f5054_universe_estimate int ,
f5054_impression float,
f5564_share float,
f5564_hut_put float,
f5564_hut_put_impression float,
f5564_rating float,
f5564_universe_estimate int ,
f5564_impression float,
f65plus_share float,
f65plus_hut_put float,
f65plus_hut_put_impression float,
f65plus_rating float,
f65plus_universe_estimate int ,
f65plus_impression float,
ww_share float,
ww_hut_put float,
ww_hut_put_impression float,
ww_rating float,
ww_universe_estimate int ,
ww_impression float,
daily_average_group varchar(500),
daily_group varchar(500),
daily_normal_group varchar(500),
daily_telecast_group varchar(500),
dow int,
excluded varchar(500),
monthly_average_group varchar(500),
monthly_group varchar(500),
monthly_normal_group varchar(500),
monthly_telecast_group varchar(500),
nielsenDow int,
program_code int ,
program_group varchar(500),
program_group_airing_time varchar(500),
program_name varchar(500),
programday_group varchar(500),
swing_break_type varchar(500),
week_number int,
weekly_average_group varchar(500),
weekly_group varchar(500),
weekly_normal_group varchar(500),
weekly_telecast_group varchar(500),
source_creation_epoch int ,
etl_timestamp TIMESTAMPTZ,
sample_type varchar(500),
dim_date_time_id int ,
job_run_id varchar(500),
rating_stream varchar(500),
half_hr_grain varchar(500),
hr_grain varchar(500),
dow_str varchar(500),
day_type varchar(500),
hour_minute int,
station_code int,
reporting_year integer,
reporting_month integer,
market_code integer,
reporting_service integer,
sample_type_int int,
sub_sample_indicator int,
rating_stream_int integer
) AS COPY FROM 's3://bucketname/1/nielsen/fact_rating_quarter_hour/////////*'
parquet(hive_partition_cols='station_code,reporting_year,reporting_month,market_code,reporting_service,sample_type_int,sub_sample_indicator,rating_stream_int');
