Finding rows in table T1 that DO NOT MATCH any row in table T2

From: Shaul Dar <shauldar(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Finding rows in table T1 that DO NOT MATCH any row in table T2
Date: 2009-10-20 12:37:04
Message-ID: 234efe30910200537rbdaebaft88052b5f51a0a43b@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi,

I have two large tables T1 and T2, such that T2 has a FK to T1 (i.e. T2.FK-->
T1.PK, possibly multiple T2 rows may reference the same T1 row). I have
deleted about 2/3 of table T2. I now want to delete all rows in T1 that are
not referenced by T2, i.e. all rows in T1 that cannot join with (any row in)
T2 on the condition T2.FK = T1.PK (the opposite of a join...)

I assume this will work but will take a long time:

DELETE * FROM T1 where T1.PK NOT IN
(SELECT T1.PK FROM T1, T2 where T1.PK = T2.FK)

What is an *efficient* way to do this?
Thanks,

-- Shaul

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Shaul Dar 2009-10-20 12:46:23 Re: Finding rows in table T1 that DO NOT MATCH any row in table T2
Previous Message Merlin Moncure 2009-10-20 11:55:34 Re: Domain vs table