Search Postgresql Archives

Re: unnesesary sorting after Merge Full Join

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



"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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux