Re: OUTER JOIN performance regression remains in 8.3beta4

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> So if I write (along with some other joins):
> t1 join t2 on (t1.x=t2.x) where t1.x=3
> I'll get a different result than if I write
> t1, t2 where t1.x=3 and t2.x=3

In 8.3 you won't, because those are in fact exactly equivalent (and the
new EquivalenceClass machinery can prove it). The cases that are
interesting are more like

t1 LEFT join t2 on (t1.x=t2.x) where t1.x=3

which is not equivalent to the other construction, because t2.x is only
sort-of-equal to 3.

Hmm ... now that I look at this, it might be a good idea if
have_relevant_eclass_joinclause() didn't skip ec_has_const
EquivalenceClasses. That would give us the same behavior for simple
inner-join cases that I'm advocating for outer joins, namely that we
can consider an early join between two rels that are related in the
fashion you show. We don't actually need to invent dummy join
clauses to make that happen, because the join search code believes
have_relevant_eclass_joinclause() even if it doesn't see a
joinclause for itself ...

regards, tom lane