>> > 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.
Yes. But may be the FULL MERGE JOIN could be improved, because it
is ordered, it actually has "outer path's path key": "coalesce(id1,id2)".
> If there are NULLs in the left hand side then it needs to be treated as
> unsorted, which forces a sort.
Yes, it is not ordered by the id1 from the left table because of NULLs.
And it is also not ordered by the id2 from the right table because of NULLs.
But it is ordered by coalesce(id1,id2). Could postgresql have sense about
this fact?
> If you know there are no NULLs then don't do a FULL join.
Full join is right choice for my task. There are images of products stored
on HDD, their IDs are in table pics_arch. And there are image IDs mentioned
in the pricelist, they are in table pr_img. Thus some images could be both
on HDD and pricelist, some only on HDD, other only in the pricelist. I use
full join of these two tables to show HTML-table consists of all images
with remark "both on HDD and in pricelist", "only on HDD" or "only in
pricelist".
