optimizing a query - Mailing list pgsql-sql

From Louis-David Mitterrand
Subject optimizing a query
Date
Msg-id 20081214192213.GA11792@apartia.fr
Whole thread Raw
List pgsql-sql
Hi, 

I have an 'event' table and an 'event_date' table pointing to it
containing (potentially) several event dates (start and (optionnaly)
end) for the event in the past, present and future.

I'm trying to build a query to select the most "relevant" date:
'current' or 'next' or 'last' (by order of priority).

Actually I already have a view of event+"most relevant"event_date:
CREATE VIEW event_story_review AS    SELECT d.* FROM event_list_story_review d     WHERE (d.id_date = (SELECT
d2.id_dateFROM event_date d2 WHERE    (d2.id_event = d.id_event)     ORDER BY d2.end_date is not null desc,
(d2.end_date>= d.today) DESC,     d2.start_date LIMIT 1));
 

This works but I am bothered by the subquery which has a slight
performance impact on all queries using this view (there are many in my
app).

Is there a better way of doing it? maybe without a subquery?

Thanks,

-- 
http://www.critikart.net


pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: array_to_string(anyarray, text) that was working in 8.1 is not working in 8.3
Next
From: Corey Horton
Date:
Subject: Re: array_to_string(anyarray, text) that was working in 8.1 is not working in 8.3