I have a table with the following schema
utc_time timestamptz, -- this is always stored at the hourly grain in UTC dim1 int, dim2 varchar(150), metric int
Data notes
- Dim 2 is very high cardinality
Use cases
- Frequently summarizing data for Dim1 by day at arbitrary time zones
- Less often, drilling down into data at the day > dim1 > dim 2 level
I created a Live Aggregate Projection, as it seemed like it'd serve use cases 1 and 2 pretty well. Its defined
select utc_time, dim1 sum(metric) as metric group by utc_time, dim1
However, when I query it like below, Vertica says it is infeasible to use the LAP (tested by providing a hint). Use of the data looks like
select date(utc_time at ?timezone) as day, dim1, sum(metric) as total group by 1, 2
Question
- Is Vertica able to use Live Aggregate Projections when you group by an expression like aggregating at a date level?
- If it is, what am I doing wrong?
- If it can not, would you model the data differently? What other tooling does Vertica offer to support a use case like this?
