Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 2B0049FB3AA for ; Tue, 10 Oct 2006 22:03:12 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 01512-08 for ; Tue, 10 Oct 2006 22:03:08 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 67FC29FB3A7 for ; Tue, 10 Oct 2006 22:03:05 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id 5189256427; Tue, 10 Oct 2006 20:03:04 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Tue, 10 Oct 2006 20:03:02 -0500 Date: Tue, 10 Oct 2006 20:03:02 -0500 From: "Jim C. Nasby" To: Brendan Curran Cc: Tom Lane , Pg Performance list Subject: Re: Scrub one large table against another Message-ID: <20061011010301.GV72517@nasby.net> References: <452C1437.9040709@gmail.com> <13451.1160518445@sss.pgh.pa.us> <452C208C.5010603@gmail.com> <13912.1160520427@sss.pgh.pa.us> <452C30CA.2070801@gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <452C30CA.2070801@gmail.com> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:061011:brendan.curran@gmail.com::YGiEAZKQa/cn0ahr:0000000000000 0000000000000000000000009QHz X-Hashcash: 1:20:061011:tgl@sss.pgh.pa.us::TYpLpYgf2nemcbj7:00000000000000000000 000000000000000000000000FjAq X-Hashcash: 1:20:061011:pgsql-performance@postgresql.org::P+LAToJv2WVyFoaO:00000 000000000000000000000000109c X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.052 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200610/215 X-Sequence-Number: 21344 On Tue, Oct 10, 2006 at 05:46:18PM -0600, Brendan Curran wrote: > > > Tom Lane wrote: > >Brendan Curran 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... I think that's coming about because of the IN. Try a simple join instead... SELECT email_record_id FROM ONLY email_record er JOIN suppress s USING (email) WHERE er.email_list_id = 13; -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)