The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Why does SELECT * run faster than SELECT <column_list> for this query? Is this a bug?

Vertica's queries is known to be faster in accessing data because of its columnar approach. But when we tested this query (multiple times), SELECT * ran in a matter of seconds while SELECT 78_columns_list ran for more than an hour. What could be wrong? Is this a bug?

Table "a" has 1 billion records

select
<78 columns>
from a
join p_temp b
on b.prod_open_dt<=a.last_day_in_mth
left join x_temp x
on a.yr_mth=x.yr_mth
full join ms_temp c
on b.cust_nb=c.cust_nb
and a.yr_mth=c.yr_mth
and b.prod_cd=c.prod_cd_crdt
full join t_temp d
on coalesce(a.yr_mth,c.yr_mth)=d.yr_mth
and coalesce(b.col1,c.col2)=d.cust_nb
and coalesce(b.col1,c.col2)=d.prod_cd
left join ap
on coalesce(b.col1,d.col2,c.col3)=ap.col10
left join ch_temp g
on coalesce(b.col1,c.col2,d.col3)=g.cust_nb
and coalesce(ap.col1,c.col2)=g.col10
left join m_temp e
on coalesce(b.col1,c.col2,d.col3)=e.cust_nb
left join mpy_temp f
on coalesce(b.col1,c.col2,d.col2)=f.cust_nb
left join h
on (e.gender = h.gender)
left join s
on (e.memb_stat = s.memb_stat)

Comments

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file