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