Skip site navigation (1) Skip section navigation (2)

Re: optimizing a query

From: Louis-David Mitterrand <vindex+lists-pgsql-sql(at)apartia(dot)org>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: optimizing a query
Date: 2008-12-15 06:51:32
Message-ID: 20081215065131.GA7250@apartia.fr (view raw or flat)
Thread:
Lists: pgsql-sql
On Sun, Dec 14, 2008 at 02:51:24PM -0800, Michal Szymanski wrote:
> On 14 Gru, 20:22, vindex+lists-pgsql-(dot)(dot)(dot)(at)apartia(dot)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,

In response to

pgsql-sql by date

Next:From: Philippe LangDate: 2008-12-15 12:12:54
Subject: "Subquery must return only one column" & query optimization
Previous:From: Corey HortonDate: 2008-12-15 00:39:39
Subject: Re: Re: [SQL] array_to_string(anyarray, text) that was working in 8.1 is not working in 8.3

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group