Re: Deleting orphaned records (not exists is very slow)

On Sep 27, 2010, at 8:02 , Tim Uckun wrote:

> What is the best strategy for deleting orphaned records from a large table.
>
> The usual NOT IN is very slow so there must be a better way in
> postgres for dealing with these.

If the table is large, I sometimes use the following pattern:

1. Create a trigger on the referenced table (foo) to delete rows from the
referencing table (bar) when they're deleted from the referenced table.
This is a poor man's ON DELETE CASCADE and prevents any more rows from being
orphaned.

2. Create a table with the keys of the referencing table which are no longer
in the referenced table:

CREATE TABLE orphaned_bar
SELECT keycol
  FROM bar
  LEFT JOIN foo USING (keycol)
  WHERE foo.keycol IS NULL;

keycol may be multiple columns if you've got a multi-column key.

3. You're then free to delete the rows from bar however you wish, using orphaned_bar.
You might want to do them in one go, or in batches. You'll likely want to create an
index on orphaned_bar.keycol.

You can then add your foreign key and get rid of the trigger on foo when you're done.

Hope this helps.

Michael Glaesemann
grzm seespotcode net