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
> 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.
In response to
pgsql-sql by date
|Next:||From: Philippe Lang||Date: 2008-12-15 12:12:54|
|Subject: "Subquery must return only one column" & query optimization|
|Previous:||From: Corey Horton||Date: 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|