Hallo,
I have a large amount of time based events to aggregate, with a finite list of possible events.
the events are logged one at the time:
timestamp_1 : event_1
timestamp_2 : event_2
timestamp_3 : event_1
...
My idea is to prepare the data before to import them in order to get one separate column per event
along with the desired time aggregation
timestamp : houroffset : is_event_1 :is_event_2 : is_event_3
t1 132 True null null
t2 132 null True null
t3 133 True null null
t4 134 null null True
....
With this model, I can easily count my events per time periode:
select
count(is_event_1) as C1,
count(is_event_2) as C2,
count(is_event_3) as C3
from foo
group by houroffset.
(the real model is more compex as they are different categories and volatile properties associated with the events)
and now my questions:
---------------------
- Will the above query acces the "is_event columns", or get the informmation only from the nulls bitmap within the row headers ?
- How does the splitting of the "event" information affect the row headers ? Should I rather define a single event column and aggregate my data using "case when else end" clauses ?
- is a where clause "where is_event_x is not null" more performant than "where is_event_x is true" as the null bitmap can thoretically be used.
- in my model, I couldn't find a way to build an index that could be used to query a single event:
select ...
from foo
where is_event_1=true (|| is_event_1 is not null)
I first imagined an index like create index foo_i on foo ((is_event_1 IS NOT NULL), (is_event_2 IS NOT NULL),...
But it is apparently not used when I have a single event in my where clause.
A solution would be to add a further column in my table containing the event_id and to index it:
timestamp : houroffset : is_event_1 :is_event_2 : is_event_3 : event_id
t1 132 True null null 1
t2 132 null True null 2
t3 133 True null null 1
t4 134 null null True 3
but this extra column would be redundant with the nulls bitmap. Is there a way to avoid this duplication of information ?
- is there a performance gain at all when columns that are seldom used are placed at the end of the rows ?
- I 'll also be thankful for any comments and critics on my model.
cheers,
Marc
