Re: unnesesary sorting after Merge Full Join

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> "Alexey A. Nalbat" <alexey_nalbat(at)hotbox(dot)ru> writes:
>> 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)".

No, it does not have the outer path's path key. The outer path's key
is just id1.

> The immediate blocker is that currently in build_join_pathkeys() for FULL
> OUTER JOIN we don't note any path keys at all. We could note COALESCE(id1,id2)
> as a path key, though we would have to create an equivalence class

Right ...

> and add COALESCE(id2,id1) to it as well I think.

No, because those two expressions are not equivalent. (Hmm ... squint
... but full merge join is pretty much symmetric, so it's not clear
why it should matter which side is left or right. Maybe COALESCE isn't
exactly the right concept with which to describe the merged variable?)

> Even if it wasn't hard to add that at least for this case in
> reconsider_outer_join_clauses() we explicitly don't consider join clauses
> unless they're against a constant. I haven't quite absorbed the logic here but

That stuff is irrelevant for sort-order considerations.

It strikes me that there's another bit of smarts that could be added
here: in a Merge Right Join the correct output pathkey is the righthand
input's path key, rather than nil. Again this is because mergejoin is
symmetric in the two inputs.

regards, tom lane