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

On Thu, Feb 26, 2004 at 06:26:19PM -0800, mike(at)linkify(dot)com wrote:
> I'm using postgresl 7.3.2 and have a query that executes very slowly.
>
> There are 2 tables: Item and LogEvent. ItemID (an int4) is the
> primary key
> of Item, and is also a field in LogEvent. Some ItemIDs in LogEvent do
> not
> correspond to ItemIDs in Item, and periodically we need to purge the
> non-matching ItemIDs from LogEvent.

delete from LogEvent where EventType!='i' and
ItemID not in (select ItemID from Item);

delete from LogEvent where EventType!='i' and
not exists (select * from Item where Item.ItemID=LogEvent.ItemID);

You might also use a foreign key, cascading delete, etc. As for the
query style, I've had cases with the latest 7.4 where the "in" style
wasn't optimized but the "exists" style was. It's the exact same query,
and technically the optimizer should figure that out. Use "explain" to
see if it's being optimized to use indexes or if it's just doing table
scans.

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