Re: JOIN with ORDER on both tables does a sort when it souldn't

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> írta:

> Daniel Denes <panther-d(at)freemail(dot)hu> writes:
> > But even then, it won't realize that the result are in correct order,
> > and does a sort! Why?
>
> In general the output of a nestloop doesn't derive any ordering
> properties from the inner scan. It might happen to work in your
> particular case because on the outer side (site_id, order) is unique
> and so the "order" values must be strictly increasing. But if there
> could be multiple rows with the same "order" value coming from the
> outer side, then it would be incorrect to claim that the join output is
> sorted by (outer.order, inner.order).
>
> It's possible that the planner could be taught to recognize this
> situation, but it looks to me like doing that would result in drastic
> increases in planning time for many queries (due to having to consider
> a lot more Paths) with a resulting win in only a very few.
>
> regards, tom lane
>

Aww, you're right... I absolutely forgot that scenario (rows with
same "order" in the outer scan).
But that led me to another question: if there could be rows with the
same "order" value in the outer scan, how could I get the list where
forums in distinct forum_groups don't mix, they are sorted by "order"
inside the group, and groups are sorted according to the
outer "order"? (Groups with the same outer "order" can be listed in
either way, but don't mix them!)
The query I wrote would return something like this in such a situation:

group_id | group_ord | forum_id | forum_ord
----------+-----------+----------+-----------
... | ... | ... | ...
41 | 6 | 761 | 1
27 | 6 | 763 | 1
41 | 6 | 762 | 2
27 | 6 | 764 | 2
... | ... | ... | ...

But what if I wanted one of these (either one is OK)?

group_id | group_ord | forum_id | forum_ord
----------+-----------+----------+-----------
... | ... | ... | ...
41 | 6 | 761 | 1
41 | 6 | 762 | 2
27 | 6 | 763 | 1
27 | 6 | 764 | 2
... | ... | ... | ...

group_id | group_ord | forum_id | forum_ord
----------+-----------+----------+-----------
... | ... | ... | ...
27 | 6 | 763 | 1
27 | 6 | 764 | 2
41 | 6 | 761 | 1
41 | 6 | 762 | 2
... | ... | ... | ...

How would I do this? The only thing I can think of is inserting another
ORDER BY column in the middle (assuming group_id is a PRIMARY KEY):
group_ord ASC, group_id ASC, forum_ord ASC

Of course this would now be the same situation as before... :)

Végleges lézeres szőrtelenítés:jún. 30-ig most mindkét hónalj kezelése csak 79 000 Ft! Klikk ide a részleteketért!
http://www.webdesign.hu/aesthetica/flash_microsite/?id=8;p_code=2029