From: Jim C. Nasby <jim@nasby.net>
To: Brendan Curran <brendan.curran@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Pg Performance list <pgsql-performance@postgresql.org>
Subject: Re: Scrub one large table against another
Date: Tue, 10 Oct 2006 20:03:02 -0500
Message-ID: <20061011010301.GV72517@nasby.net> (raw)
In-Reply-To: <452C30CA.2070801@gmail.com>
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>
On Tue, Oct 10, 2006 at 05:46:18PM -0600, Brendan Curran wrote:
>
>
> 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...
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jim@nasby.net, brendan.curran@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: Scrub one large table against another
In-Reply-To: <20061011010301.GV72517@nasby.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox