Skip site navigation (1) Skip section navigation (2)

Re: Improving NOT IN

From: "Simon Riggs" <simon(at)2ndquadrant(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Improving NOT IN
Date: 2007-02-01 11:49:55
Message-ID: 1170330596.3681.514.camel@silverbirch.site (view raw or flat)
Thread:
Lists: pgsql-hackers
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



In response to

pgsql-hackers by date

Next:From: Hannu KrosingDate: 2007-02-01 12:38:14
Subject: A more general approach (Re: Data archiving/warehousingidea)
Previous:From: Pavan DeolaseeDate: 2007-02-01 11:44:56
Subject: Re: stack usage in toast_insert_or_update()

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group