Gregory Stark <stark@xxxxxxxxxxxxxxxx> writes: > "Alexey A. Nalbat" <alexey_nalbat@xxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
