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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
