Skip site navigation (1) Skip section navigation (2)

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

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Finding rows in table T1 that DO NOT MATCH any row in table T2
Date: 2009-10-20 12:54:05
Message-ID: 20091020125405.GD5113@a-kretschmer.de (view raw or flat)
Thread:
Lists: pgsql-performance
In response to Shaul Dar :
> 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,

Maybe this one:

(my id is your pk):

delete from t1 where t1.id in (select t1.id from t1 left join t2 using
(id) where t2.id is null);

Try it, and/or use explain for both versions and see which which is
faster.

Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2009-10-20 13:59:07
Subject: Re: Finding rows in table T1 that DO NOT MATCH any row in table T2
Previous:From: Shaul DarDate: 2009-10-20 12:46:23
Subject: Re: Finding rows in table T1 that DO NOT MATCH any row in table T2

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group