Live Aggregate Projections to calculate EPS in a system
I am trying to create live aggregate projections with a group by clause and then call a select to optimize the query execution. I can see that the projection gets created but when I call the EXPLAIN on my select query, I dont see it is using the created projection. Could you please help.
create projection events_eps (id, deviceReceiptTime, count) as select id, deviceReceiptTime, count(id) from events group by id, deviceReceiptTime;
EXPLAIN select cast(deviceReceiptTime / 3600000 as integer) as time, count(id) from events where deviceReceiptTime>(EXTRACT(EPOCH from '2019-04-06T15:05:18.659Z'::TIMESTAMPTZ) - 1366518)1000 and deviceReceiptTime<(EXTRACT(EPOCH from '2019-04-06T15:05:18.659Z'::TIMESTAMPTZ)) 1000 group by deviceReceiptTime/3600000;
+-GROUPBY HASH (LOCAL RESEGMENT GROUPS) [Cost: 8M, Rows: 3M] (PATH ID: 1)
| Aggregates: count(events.id)
| Group By: (events.deviceReceiptTime / 3600000)
| +---> STORAGE ACCESS for events [Cost: 7M, Rows: 1B] (PATH ID: 2)
| | Projection: events_super
| | Materialize: events.deviceReceiptTime, events.id
| | Filter: ((events.deviceReceiptTime > 1553196600659.000000) AND (events.deviceReceiptTime < 1554563118659.000000))
The problem is your GROUP BY in the SELECT is different than the GROUP BY in the LAP.
The follow SQL statements will use the LAP:
But the next one will not as it has a calculation on a GROUP BY column:
You need to create a LAP with a matching GROUP BY:
Now the query will use the new LAP: