"Tom Lane" <tgl@xxxxxxxxxxxxx> writes: > I wrote: >> Gregory Stark <stark@xxxxxxxxxxxxxxxx> 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! ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
