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

From: <mike(at)linkify(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: correlated delete with "in" and "left outer join"
Date: 2004-02-27 02:26:19
Message-ID: 2977.10.0.10.116.1077848779.squirrel@mail.linkify.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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"?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Glaesemann 2004-02-27 02:41:36 Re: correlated delete with "in" and "left outer join"
Previous Message Mike Wertheim 2004-02-27 01:57:40 help with correlated delete and outer join