Thread: dynamic events categorization

Hello,

I'm looking for a more efficient way of dynamically categorizing some
events. The following view definition looks into each event's latest
event_date object (a theater play can have several, a book only one) to
tell whether the event is current, past or future:
SELECT s.id_event_subtype, s.subtype, t.id_event_type, t.type,e.id_event, e.created_by, e.created_on, e.modified_by,
e.modified_on,e.id_image,e.show_name, e.length, d.id_date,d.start_date, d.end_date, d.low_price, d.high_price,
d.id_location,d.showtime,       CASE            WHEN d.start_date <= 'now'::text::date AND             CASE
  WHEN t.type = 'movie'::text THEN d.start_date >= ('now'::text::date - 21)                WHEN t.type = 'book'::text
THENe.created_on >= ('now'::text::date - 28)                ELSE d.end_date >= 'now'::text::date OR d.end_date IS NULL
         END THEN '0_current'::text            WHEN d.start_date > 'now'::text::date THEN '1_future'::text
WHENd.start_date IS NOT NULL THEN '2_past'::text            ELSE ''::text        END AS timingFROM event eNATURAL JOIN
event_type2tLEFT JOIN event_subtype2 s USING (id_event_subtype)LEFT JOIN show_date d USING (id_event);
 

This view is widely used in my application, including as a basis for
further views, as I almost always need to know the 'timing' category of
an event (past, current, future). But I have nagging doubts about its
efficiency. It also seems pretty slow in its current form.

Any suggestion on how to improve it (including schema modifications) are
more than welcome.

Thanks,

Hello,

I guess that the time offsets (now-21 and now-28) are evaluated each
time the corresponding condition is met.
It may be faster to put them into a separate sub query. I'm not sure
about putting "now" itself within the sub query...

It may also be better to put your query in a procedure where you can put
these constants into variables instead of using a sub query.

Depending of the distribution of a) 2_past,1_future,0_current and '' and
b) t.type,  it may be worth to have different queries, bound with UNION
ALL. This would simplify the "CASE" construct and at least part of the
tests should happen on indexes only.


If the query is run very often, you may want to add a boolean column
is_past on show_date, and have a separate job that put the concerned
records to true every x minutes ...


HTH,

Marc Mamin




SELECT s.id_event_subtype, s.subtype, t.id_event_type, t.type,e.id_event, e.created_by, e.created_on, e.modified_by,
e.modified_on,e.id_image, e.show_name, e.length, d.id_date,d.start_date, d.end_date, d.low_price, d.high_price,
d.id_location,d.showtime,        CASE            WHEN d.start_date <= 'now'::text::date
AND             CASE                WHEN t.type = 'movie'::text THEN
d.start_date >= c.a                WHEN t.type = 'book'::text THEN
e.created_on >= c.b                ELSE d.end_date >=
'now'::text::date OR d.end_date IS NULL            END THEN '0_current'::text            WHEN d.start_date >
'now'::text::date
THEN '1_future'::text            WHEN d.start_date IS NOT NULL THEN
'2_past'::text            ELSE ''::text        END AS timingFROM
-- added sub query:(select 'now'::text::date - 21 as a,  'now'::text::date - 28 as
b) c,event eNATURAL JOIN event_type2 tLEFT JOIN event_subtype2 s USING (id_event_subtype)LEFT JOIN show_date d USING
(id_event);

On Thu, Jun 26, 2008 at 05:06:14PM +0200, Marc Mamin wrote:
> 
> Hello,

Hi Marc,

> I guess that the time offsets (now-21 and now-28) are evaluated each
> time the corresponding condition is met.

Excellent suggestion, this makes the query ~ 15% faster. Every bit
counts.

> It may be faster to put them into a separate sub query. I'm not sure
> about putting "now" itself within the sub query...

Where would you put it?

> It may also be better to put your query in a procedure where you can put
> these constants into variables instead of using a sub query.
> 
> Depending of the distribution of a) 2_past,1_future,0_current and '' and
> b) t.type,  it may be worth to have different queries, bound with UNION
> ALL. This would simplify the "CASE" construct and at least part of the
> tests should happen on indexes only.

Could you give a very short example?

> If the query is run very often, you may want to add a boolean column
> is_past on show_date, and have a separate job that put the concerned
> records to true every x minutes ...

That would require a cron job (?). I'm trying to keep the app self-contained
for now.

> HTH,

It sure does, thanks!

Cheers,