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

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>
Cc: PostgreSQL <pgsql-general(at)postgresql(dot)org>
Subject: Re: eliminating records not in (select id ... so SLOW?
Date: 2008-08-01 01:37:39
Message-ID: 2344.1217554659@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

Responses

pgsql-general by date

Next:From: Henry - Zen Search SADate: 2008-08-01 02:20:35
Subject: Re: Clone a database to other machine
Previous:From: AndrewDate: 2008-07-31 23:22:42
Subject: Re: hibernate nativequery and uuid

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