The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.