> 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