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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Shaul Dar <shauldar(at)gmail(dot)com>
Cc: 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 13:59:07
Message-ID: 26129.1256047147@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Shaul Dar <shauldar(at)gmail(dot)com> writes:
> 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)

Well, yeah, but it's unnecessarily inefficient --- why not just

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

However, that still won't be tremendously fast unless the subselect fits
in work_mem. As of 8.4 this variant should be reasonable:

DELETE FROM T1 where NOT EXISTS
(SELECT 1 FROM T2 where T1.PK = T2.FK)

Pre-8.4 you should resort to the "left join where is null" trick,
but there's no need to be so obscure as of 8.4.

regards, tom lane

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Melton Low 2009-10-20 16:14:13 Re: Finding rows in table T1 that DO NOT MATCH any row in table T2
Previous Message A. Kretschmer 2009-10-20 12:54:05 Re: Finding rows in table T1 that DO NOT MATCH any row in table T2