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!

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