Tom,
1. Actually I just tested you suggestion
SELECT COUNT (*) FROM T1 where NOT EXISTS
(SELECT 1 FROM T2 where
T1.PK =
T2.FK)
and in worked in PG 8.3.8. On a DB with 6M T1 records and 5M T2 records it took 1m8s,
My suggestion, i.e.
SELECT COUNT(*) FROM T1 LEFT JOIN T2 ON
T1.PK =
T2.FK WHERE
T2.FK IS NULL
was about twice as fast, 37s. (both returned same number of rows, about 2/3 of T1)
However I can use DELETE with your version (instead of "SELECT COUNT (*)" above) but not with mine (can't have LEFT JOIN in DELETE), so YOU WIN. Thanks!
2. BTW. I presented my question earlier in an overly simplified fashion. Sorry. In actuality the two tables are joined on two columns,
say Ka and Kb (a composite key column), e.g. T1.PKa = T2.FKa and T1.PKb = T2.FKb. So the IN versions suggested will not work
since AFAIK IN only works for a single value.
-- Shaul
On Tue, Oct 20, 2009 at 3:59 PM, Tom Lane
<tgl@sss.pgh.pa.us> wrote:
Well, yeah, but it's unnecessarily inefficient --- why not just
DELETE FROM T1 where
T1.PK NOT IN
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
