"Robins Tharakan" <[EMAIL PROTECTED]> 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql