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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at


Reply via email to