Re: Unnecessary repeat condition for a self inner join

"Robins Tharakan" <tharakan(at)gmail(dot)com> writes:
> In case of an INNER JOIN, shouldn't the second condition (in Query2) be
> unnecessary ?
> Or am I being unreasonable in this expectation ?

> SELECT n1.scheme_code
> FROM nav n1
> INNER JOIN nav n2 ON n1.scheme_code = n2.scheme_code
> WHERE n1.scheme_code BETWEEN 100 AND 200
> AND n2.scheme_code BETWEEN 100 AND 200

While the optimizer theoretically could deduce the extra restriction
condition, it doesn't attempt to. It's extremely unclear that the extra
cycles to look for such cases would be repaid on average, because cases
like this aren't that common. The current state of affairs is that
the system will deduce implied equality conditions, but not implied
inequality conditions.

[ thinks for a bit... ] The current policy has been driven in part
by the assumption that looking for cases where such a deduction
could apply would be pretty expensive. I wonder though whether the
recent EquivalenceClass work has changed the landscape. We now store
an explicit representation of the btree opclasses associated with
each equivalence condition, which is one of the pieces that would be
needed to match up the equivalences with inequality conditions.
I'm still dubious, but that's at least one less catalog search ...

regards, tom lane