incorrect vertica projection selection

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?

Answers

  • ChuckBChuckB Employee

    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.

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.