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

Re: correlated delete with 'in' and 'left outer join'

From: Michael Chaney <mdchaney(at)michaelchaney(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: correlated delete with 'in' and 'left outer join'
Date: 2004-02-27 18:01:16
Message-ID: 20040227180116.GA8807@michaelchaney.com (view raw or flat)
Thread:
Lists: pgsql-general
On Fri, Feb 27, 2004 at 12:05:48PM -0500, Mike Mascari wrote:
> Michael Chaney wrote:
> 
> >Please, before continuing this thread, read my post below.  What you're
> >all getting around to, albeit painfully, is that this subquery is
> >worthless as-is.  This is the mysql way of finding rows in one table
> >with no match in another without the convenience of the "in" or "exists"
> >constructs.
> >
> >Because we're using Postgres and have those constructs, the original
> >query can be rewritten simply with either:
> >
> >delete from LogEvent where EventType != 'i' and ItemID not in
> >		(select ItemID from Item)
> >
> >That's it.  That's the whole query.  It does what he wants.
> 
> One more minor point. :-)
> 
> If you are using 7.3 or earlier, PostgreSQL will sequentially scan 
> the IN subquery result, which executes quite slowly and therefore 
> the EXISTS method Stephan stated should be used:
> 
> DELETE FROM LogEvent
> WHERE EventType != 'i' AND NOT EXISTS (
>  SELECT 1
>  FROM Item
>  WHERE Item.ItemID = LogEvent.ItemID
> );
> 
> If you are using >= 7.4, then your query above is optimal:

Not necessarily.  I had a query just last week that still wouldn't
optimize with the "in" notation, but did optimize with "exists"
notation.  My other post about this showed both queries for that reason,
but I still feel that, for academic purposes, the "in" clause is far
more readable.

Anyway, good point.

Michael
-- 
Michael Darrin Chaney
mdchaney(at)michaelchaney(dot)com
http://www.michaelchaney.com/

In response to

pgsql-general by date

Next:From: Mike MascariDate: 2004-02-27 18:03:02
Subject: Re: correlated delete with 'in' and 'left outer join'
Previous:From: Stephan SzaboDate: 2004-02-27 17:59:37
Subject: Re: correlated delete with 'in' and 'left outer join'

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