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

abtsantosbentenabtsantosbenten Registered User
edited May 25 in Vertica Forum

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

<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)


Leave a Comment

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