Finding orphan records

From: Wes <wespvp(at)syntegra(dot)com>
To: Postgresql-General <pgsql-general(at)postgresql(dot)org>
Subject: Finding orphan records
Date: 2006-01-12 05:51:06
Message-ID: BFEB4A6A.80A6%wespvp@syntegra.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm trying to find/delete all records in table A that are no longer
referenced by tables B or C. There are about 4 million records in table A,
and several hundred million in tables B and C.

Is there something more efficient than:

select address_key, address from addresses where ( not exists(select 1 from
B where BField=addresses.address_key limit 1) ) and ( not exists(select 1
from C where CField=addresses.address_key limit 1) )

Of course, all fields above are indexed.

There are foreign key references in B and C to A. Is there some way to
safely leverage that?

Wes

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Assad Jarrahian 2006-01-12 05:51:27 Re: sql (Stored procedure) design question
Previous Message Michael Fuhr 2006-01-12 05:47:55 Re: getting access to geodistance (what do I need to install/enable) in an existing db install