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>]

=?ISO-8859-2?Q?D=E1niel_D=E9nes?= <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