From: | Brendan Curran <brendan(dot)curran(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Pg Performance list <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Scrub one large table against another |
Date: | 2006-10-10 23:46:18 |
Message-ID: | 452C30CA.2070801@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Tom Lane wrote:
> Brendan Curran <brendan(dot)curran(at)gmail(dot)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...
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Kirkwood | 2006-10-11 00:34:53 | Re: Simple join optimized badly? |
Previous Message | Tom Lane | 2006-10-10 22:47:07 | Re: Scrub one large table against another |