Thread: optimizing a query

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

On Sun, Dec 14, 2008 at 02:51:24PM -0800, Michal Szymanski wrote:
> On 14 Gru, 20:22, vindex+lists-pgsql-...@apartia.org (Louis-David
> Mitterrand) wrote:
> >
> > 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_date FROM 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?
> 
> The question is how do you plan to use your view ? Do you select all
> rows from viev or you select only few tow from view using additional
> filters? Usually you can rewrite subquery to JOINsbut without
> information how do you plan use view it is hard to say is it bettter
> solution.
> It is important how many row do you plan in each table.

I usually select all rows from the view with additional filters.

If you have an example of rewriting the query with a join (instead of
subquery) would you care sending it? So that I could run some tests.

Thanks,