Re: unnesesary sorting after Merge Full Join
От | Alexey A. Nalbat |
---|---|
Тема | Re: unnesesary sorting after Merge Full Join |
Дата | |
Msg-id | 008201c87850$1e261c30$478aa959@nalbat обсуждение исходный текст |
Ответ на | unnesesary sorting after Merge Full Join (Alexey Nalbat <nalbat@price.ru>) |
Ответы |
Re: unnesesary sorting after Merge Full Join
|
Список | pgsql-general |
>> > 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".
В списке pgsql-general по дате отправления: