Re: dynamic events categorization

From: "Marc Mamin" <M(dot)Mamin(at)intershop(dot)de>
To: "Louis-David Mitterrand" <vindex+lists-pgsql-sql(at)apartia(dot)org>, <pgsql-sql(at)postgresql(dot)org>
Subject: Re: dynamic events categorization
Date: 2008-06-26 15:06:14
Message-ID: CA896D7906BF224F8A6D74A1B7E54AB3039E8A50@JENMAIL01.ad.intershop.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql


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 timing
FROM
-- added sub query:
(select 'now'::text::date - 21 as a, 'now'::text::date - 28 as
b) c,
event e
NATURAL JOIN event_type2 t
LEFT JOIN event_subtype2 s USING (id_event_subtype)
LEFT JOIN show_date d USING (id_event);

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Steve Midgley 2008-06-26 19:19:11 Re: ANSI Standard
Previous Message Mark Stosberg 2008-06-25 17:44:24 Re: GROUP BY on a column which might exist in one of two tables