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); -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql