incorrect vertica projection selection

rajatpaliwal86

We have a projection for a table which is order by on a field i.e. application. The below query picks up the correct projection

select application, sum(packets) from f_network_events where event_timestamp > to_timestamp_tz(1582467920) AND event_timestamp_hourid >= get_hour_id(1582467920) AND event_timestamp < to_timestamp_tz(1582554320) AND event_timestamp_hourid <= get_hour_id(1582554320) group by application;

However, to the same query if I append (having application is not null), then Vertica designer picks up the super projection which makes the query slow

select application, sum(packets) from f_network_events where event_timestamp > to_timestamp_tz(1582467920) AND event_timestamp_hourid >= get_hour_id(1582467920) AND event_timestamp < to_timestamp_tz(1582554320) AND event_timestamp_hourid <= get_hour_id(1582554320) group by application having application is not null;

This looks strange to me. Any solution or idea why it is behaving like this?

ChuckB

Could you help us understand what is the "right" and "wrong" projection? How are the projections sorted? I notice that the second query has a predicate on application (it easily gets pushed from HAVING into a WHERE clause), so I wonder if that predicate is influencing the choice of sort order, but without projection details it is just a guess.