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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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/

Browse pgsql-general by date

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