To: brendan.curran@gmail.com
cc: Pg Performance list <pgsql-performance@postgresql.org>
Subject: Re: Scrub one large table against another 
In-reply-to: <452C1437.9040709@gmail.com> 
References: <452C1437.9040709@gmail.com>
Comments: In-reply-to Brendan Curran <brendan.curran@gmail.com>
	message dated "Tue, 10 Oct 2006 15:44:23 -0600"
Date: Tue, 10 Oct 2006 18:14:05 -0400
Message-ID: <13451.1160518445@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Brendan Curran <brendan.curran@gmail.com> writes:
> CREATE TEMP TABLE temp_list_suppress(email_record_id int8);

> INSERT INTO temp_list_suppress
> 	SELECT email_record_id from ONLY email_record er
> 	WHERE email_list_id = 9 AND email IN
> 	(select email from suppress);

> CREATE INDEX unique_id_index on temp_list_suppress ( email_record_id );

> INSERT INTO er_banned
> SELECT * from ONLY email_record er WHERE EXISTS
> (SELECT 1 from temp_list_suppress ts where er.email_record_id = 
> ts.email_record_id)';

> DELETE FROM ONLY email_record WHERE email_list_id = 9 AND email_record_id IN
> 	(SELECT email_record_id from temp_list_suppress);

> TRUNCATE TABLE temp_list_suppress;
> DROP TABLE temp_list_suppress;

> The performance is dreadful, is there a more efficient way to do this? 

Have you tried doing EXPLAIN ANALYZE of each of the INSERT/DELETE steps?
If you don't even know which part is slow, it's hard to improve.

It would probably help to do an "ANALYZE temp_list_suppress" right after
populating the temp table.  As you have it, the second insert and delete
are being planned with nothing more than a row count (obtained during
CREATE INDEX) and no stats about distribution of the table contents.

Also, I'd be inclined to try replacing the EXISTS with an IN test;
in recent PG versions the planner is generally smarter about IN.
(Is there a reason why you are doing the INSERT one way and the
DELETE the other?)

BTW, that TRUNCATE right before the DROP seems quite useless,
although it's not the main source of your problem.

			regards, tom lane