469,963 Members | 1,885 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,963 developers. It's quick & easy.

subselect, order by and left join

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #1
2 7085
On Mon, 8 Nov 2004, Morten K. Poulsen wrote:
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?


AFAIK, you have no guarantees as to the output order unless you have
another order by. The join may destroy the ordering, so even if you get
the ordering you want right now, you shouldn't rely on it.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #2
On Mon, Nov 08, 2004 at 04:54:40AM -0800, Stephan Szabo wrote:
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?


AFAIK, you have no guarantees as to the output order unless you have another
order by. The join may destroy the ordering, so even if you get the ordering
you want right now, you shouldn't rely on it.


OK. Thanks for the reply.

Morten

--
Morten K. Poulsen <mo***************@afdelingp.dk>
http://www.afdelingp.dk/

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Daniel Rossi | last post: by
2 posts views Thread by kjc | last post: by
4 posts views Thread by dtwalter | last post: by
6 posts views Thread by Sebastien | last post: by
1 post views Thread by nemesisdan | last post: by
1 post views Thread by rainxy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.