Re: unnesesary sorting after Merge Full Join

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> I wrote:
>> Gregory Stark <stark(at)enterprisedb(dot)com> writes:
>>> as a path key, though we would have to create an equivalence class
>>> 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?)
>
> Wait ... after consuming more caffeine, I think you were right. The
> point of an EquivalenceClass is that it asserts the contained
> expressions must have the same values, and in the case of a full join
> it actually is the case that COALESCE(id1,id2) = COALESCE(id2,id1)
> at every output row. So it's legitimate to put both expressions in
> the same eclass, even though their values might be different in other
> circumstances. And that solves our symmetry problem because the eclass
> is the same whichever way you build it.
>
> It might still be interesting sometime to have a more bespoke
> representation for a merged variable, but I guess we don't need
> it just for this.

It might have an advantage if you're doing a three-way outer join and neither
C(id1, C(id2,id3)) nor C(C(id2,id3), id1) match the requested order of
C(C(id1,id2), id3).

It doesn't see too important to get that right since the outer joins can't be
reordered and the user could reasonably be expected to match the grouping of
his joins with his coalesce expression.

I'm normally a proponent of guaranteeing that equivalent queries produce
identical plans but avoiding factorial-order growth of equivalence classes is
also nice. Even putting both orders in there will have exponential growth
which is a bit frightful.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!