Re: unnesesary sorting after Merge Full Join

On Sat, 2008-02-23 at 14:49 -0600, Decibel! wrote:
> On Feb 21, 2008, at 4:08 AM, Alexey Nalbat wrote:

> > I found comment in src/backend/optimizer/path/pathkeys.c:
> > * EXCEPTION: in a FULL or RIGHT join, we cannot treat the result as
> > * having the outer path's path keys, because null lefthand rows may be
> > * inserted at random points. It must be treated as unsorted.
> >
> > How can I get rid of this sorting? Or could this behavior of Merge
> > Full Join be improved?
>
> Theoretically, this can be improved

I don't see how. The ORDER BY ... LIMIT ... code is already optimised.

If there are NULLs in the left hand side then it needs to be treated as
unsorted, which forces a sort.

If you know there are no NULLs then don't do a FULL join.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com