Re: Improving NOT IN

On Tue, 2007-01-30 at 17:34 -0500, Tom Lane wrote:

> I think the NOT IN optimization that *would* be of use is to
> automatically transform the NOT IN representation to an
> outer-join-with-null-test type of operation, so as to give us a wider
> choice of join methods. However, I'm not sure about correct handling
> of NULLs on the RHS in such a scenario. The existing hashed-IN code
> has to jump through some really ugly hoops to give spec-compliant
> answers with NULLs.

ISTM that we can handle this neatly by looking for a WHERE clause that
specifically excludes NULLs in the NOT IN.

i.e. a query of the form

select ...
from LHS
where key NOT IN
(select key
from RHS
where key is not null)

can be optimised to

select ...
from LHS left outer join RHS
on LHS.key = RHS.key
where RHS.key IS NULL;

This rewards people that understand the spec-compliant behaviour and
ensure there coding is watertight in the presence of NULLs.

We can extend that behaviour later when we have plan invalidation to
make it also pick up NOT NULL constraints on the keys of the RHS table.

Doing it this way round is much more useful, since not all tables have
NOT NULL constraints on their join columns so is slightly wider in its
usefulness than just checking constraints. It's also very annoying in
this specific case to not have any way for the SQL developer to pass
information to the planner - and I don't mean hints.

This would be similar to pull_up_IN_clauses()

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com