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-11-08 10:18:39
Message-ID: 20041108101839.GB7053@mopo.tv2.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)afdelingp(dot)dk>
http://www.afdelingp.dk/

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Glaesemann 2004-11-08 10:39:46 Proper nesting of hierarchical objects
Previous Message Jim Riley 2004-11-08 09:54:42 Re: RFD: comp.databases.postgresql.general