subselect, order by and left join - Mailing list pgsql-general

(re-post)

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@afdelingp.dk>
http://www.afdelingp.dk/