Re: eliminating records not in (select id ... so SLOW?

[Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>]

Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> writes:
> I'm doing something like:
> delete from table1 where id not in (select id from table2).
> table1 contains ~1M record table2 contains ~ 600K record and id is
> unique.

That's going to pretty much suck unless you've got work_mem set high
enough to allow a "hashed subplan" plan --- which is likely to require
tens of MB for this case, I don't recall exactly what the per-row
overhead is.  Experiment until EXPLAIN tells you it'll use a hashed
subplan.

BTW, don't bother with creating the index, it doesn't help for this.

			regards, tom lane