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

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: mike(at)linkify(dot)com
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: correlated delete with "in" and "left outer join"
Date: 2004-02-27 05:22:39
Message-ID: 20040226212119.G71934@megazone.bigpanda.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Thu, 26 Feb 2004 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.
>
> The query is:
>
> delete from LogEvent where EventType != 'i' and ItemID in
> (select distinct e.ItemID from LogEvent e left outer join Item i
> on e.ItemID = i.ItemID where e.EventType != 'i' and i.ItemID is null);
>
> I understand that using "in" is not very efficient.
>
> Is there some other way to write this query without the "in"?

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Karam Chand 2004-02-27 05:45:30 Re: Index Information
Previous Message Alexander Cohen 2004-02-27 03:17:14 check for user validity