DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding;
	b=pR343njUqMRg14ATVGHYrCNHJzx8tfo0inuIAFTwHqXbOAsMkAiB7cwZjtY4+1pETPI5mE90skY3Xp5CTdt8sMa2Xps+JLruGCZ3r2CVf6CO30zlY6calMp9ukJzgEv0qXsekm+PJuqEJ25cZmX0YZte1MmHr/YIXb3QORvwIuo=
Message-ID: <452C30CA.2070801@gmail.com>
Date: Tue, 10 Oct 2006 17:46:18 -0600
From: Brendan Curran <brendan.curran@gmail.com>
Reply-To: brendan.curran@gmail.com
User-Agent: Thunderbird 1.5.0.7 (Windows/20060909)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: Pg Performance list <pgsql-performance@postgresql.org>
Subject: Re: Scrub one large table against another
References: <452C1437.9040709@gmail.com> <13451.1160518445@sss.pgh.pa.us>
	<452C208C.5010603@gmail.com> <13912.1160520427@sss.pgh.pa.us>
In-Reply-To: <13912.1160520427@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


Tom Lane wrote:
> Brendan Curran <brendan.curran@gmail.com> writes:
>> Tom Lane wrote:
>>> Have you tried doing EXPLAIN ANALYZE of each of the INSERT/DELETE steps?
> 
>> FIRST INSERT (Just the select is explained):
> 
> EXPLAIN ANALYZE, please, not just EXPLAIN.
> 
> 			regards, tom lane
> 

Sorry, here is the EXPLAIN ANALYZE output of that first SELECT

EXPLAIN ANALYZE SELECT email_record_id from ONLY email_record er
	WHERE email_list_id = 13 AND email IN
	(select email from suppress);

Hash Join  (cost=8359220.68..9129843.00 rows=800912 width=8) (actual 
time=2121601.603..2121601.603 rows=0 loops=1)
   Hash Cond: (("outer".email)::text = ("inner".email)::text)
   ->  Unique  (cost=4414093.19..4522324.49 rows=21646260 width=25) 
(actual time=1165955.907..1434439.731 rows=21646261 loops=1)
         ->  Sort  (cost=4414093.19..4468208.84 rows=21646260 width=25) 
(actual time=1165955.903..1384667.715 rows=21646261 loops=1)
               Sort Key: suppress.email
               ->  Seq Scan on suppress  (cost=0.00..393024.60 
rows=21646260 width=25) (actual time=37.784..609848.551 rows=21646261 
loops=1)
   ->  Hash  (cost=3899868.47..3899868.47 rows=4606808 width=32) (actual 
time=554522.983..554522.983 rows=3245336 loops=1)
         ->  Bitmap Heap Scan on email_record er 
(cost=38464.83..3899868.47 rows=4606808 width=32) (actual 
time=275640.435..541342.727 rows=3245336 loops=1)
               Recheck Cond: (email_list_id = 13)
               ->  Bitmap Index Scan on list  (cost=0.00..38464.83 
rows=4606808 width=0) (actual time=275102.037..275102.037 rows=5172979 
loops=1)
                     Index Cond: (email_list_id = 13)
Total runtime: 2122693.864 ms


So much time is being spent in the Unique and Sort leaves... I would 
think that it wouldn't need to do the unique portion, since there is no 
DISTINCT clause...