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

subselect, order by and left join

From: "Morten K(dot) Poulsen" <morten-postgresql(at)afdelingp(dot)dk>
To: pgsql-general(at)postgresql(dot)org
Subject: subselect, order by and left join
Date: 2004-10-27 13:03:34
Message-ID: 20041027130334.GA7310@mopo.i.tv2.dk (view raw or flat)
Thread:
Lists: pgsql-general
Dear list,

Please let me know if this is not the list to ask this kind of question.

I am trying to optimize a query that joins two relatively large (750000 rows in
each) tables. If I do it using a subselect, I can "force" the planner to choose
the fastest path. Now, my question is:

If I have a subselect with an ORDER BY, and I LEFT JOIN the result with the
other table, is the order maintained? Or is PostgreSQL free to return the rows
in any order, after the join?

My query is the following:

SELECT a.*
FROM (SELECT * FROM tree WHERE parent_id=1363405 ORDER BY order_index DESC) AS a
LEFT JOIN content AS b ON a.object_id=b.id
WHERE (b.onair = 't') LIMIT 1;

Thanks,
Morten

-- 
Morten K. Poulsen <morten-postgresql(at)afdelingp(dot)dk>
http://www.afdelingp.dk/

pgsql-general by date

Next:From: Ben-Nes MichaelDate: 2004-10-27 13:24:19
Subject: Resource temporarily unavailable
Previous:From: Andrew SullivanDate: 2004-10-27 12:56:24
Subject: Re: [Fwd: Abrupt close of pgsql backend]

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