pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jack Coates <jack@lyris.com>
To: pgsql-performance <pgsql-performance@postgresql.org>
Subject: query optimization question
Date: Wed, 28 Jan 2004 17:05:54 -0800
Message-ID: <1075338353.7494.71.camel@cletus.lyris.com> (raw)

Hi all,

I've got a query that needs some help, please. Is there a way to avoid
all the looping? I've got freedom to work with the double-indented
sections below ) AND (, but the initial select distinct wrapper is much
more difficult to change. This is auto-generated code.

explain analyze SELECT DISTINCT members_.emailaddr_, members_.memberid_ 
FROM members_  WHERE ( 
	members_.List_='list1'  
	AND members_.MemberType_='normal'  
	AND members_.SubType_='mail'  
	AND members_.emailaddr_ IS NOT NULL 
	) AND (
		( select count(*) from lyrActiveRecips, members_ a, outmail_  
		where lyrActiveRecips.UserName = a.UserNameLC_  
		and lyrActiveRecips.Domain = a.Domain_  
		and a.MemberID_ = members_.MemberID_  
		and outmail_.MessageID_ = lyrActiveRecips.MailingID  
		and outmail_.Type_ = 'list'  
		and lyrActiveRecips.NextAttempt > '2004-01-20 00:00:00' 
		)
		  +  
		( select count(*) from lyrCompletedRecips, members_ a, outmail_  
		where a.MemberID_ = lyrCompletedRecips.MemberID  
		and a.UserNameLC_ = members_.UserNameLC_  
		and a.Domain_ = members_.Domain_  
		and outmail_.MessageID_ = lyrCompletedRecips.MailingID  
		and outmail_.Type_ = 'list'  
		and lyrCompletedRecips.FinalAttempt > '2004-01-20 00:00:00'  
		and lyrCompletedRecips.CompletionStatusID = 300 )  
		  = 3 
	) 
;
                                                                             QUERY PLAN                                                                              
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=537.06..537.07 rows=1 width=72) (actual
time=114460.908..114460.908 rows=0 loops=1)
   ->  Sort  (cost=537.06..537.06 rows=1 width=72) (actual
time=114460.905..114460.905 rows=0 loops=1)
         Sort Key: emailaddr_, memberid_
         ->  Index Scan using ix_members_list_notifyerr on members_ 
(cost=0.00..537.05 rows=1 width=72) (actual time=114460.893..114460.893
rows=0 loops=1)
               Index Cond: ((list_)::text = 'list1'::text)
               Filter: (((membertype_)::text = 'normal'::text) AND
((subtype_)::text = 'mail'::text) AND (emailaddr_ IS NOT NULL) AND
(((subplan) + (subplan)) = 3))
               SubPlan
                 ->  Aggregate  (cost=52.39..52.39 rows=1 width=0)
(actual time=0.089..0.090 rows=1 loops=818122)
                       ->  Hash Join  (cost=47.55..52.39 rows=1 width=0)
(actual time=0.086..0.086 rows=0 loops=818122)
                             Hash Cond: ("outer".memberid_ =
"inner".memberid)
                             ->  Index Scan using ix_members_emaillc on
members_ a  (cost=0.00..4.83 rows=1 width=4) (actual time=0.077..0.081
rows=1 loops=818122)
                                   Index Cond: (((domain_)::text =
($2)::text) AND ((usernamelc_)::text = ($1)::text))
                             ->  Hash  (cost=47.55..47.55 rows=1
width=4) (actual time=0.025..0.025 rows=0 loops=1)
                                   ->  Hash Join  (cost=25.00..47.55
rows=1 width=4) (actual time=0.023..0.023 rows=0 loops=1)
                                         Hash Cond: ("outer".messageid_
= "inner".mailingid)
                                         ->  Seq Scan on outmail_ 
(cost=0.00..22.50 rows=6 width=4) (actual time=0.001..0.001 rows=0
loops=1)
                                               Filter: ((type_)::text =
'list'::text)
                                         ->  Hash  (cost=25.00..25.00
rows=2 width=8) (actual time=0.003..0.003 rows=0 loops=1)
                                               ->  Seq Scan on
lyrcompletedrecips  (cost=0.00..25.00 rows=2 width=8) (actual
time=0.001..0.001 rows=0 loops=1)
                                                     Filter:
((finalattempt > '2004-01-20 00:00:00'::timestamp without time zone) AND
(completionstatusid = 300))
                 ->  Aggregate  (cost=51.59..51.59 rows=1 width=0)
(actual time=0.033..0.034 rows=1 loops=818122)
                       ->  Hash Join  (cost=27.35..51.59 rows=1 width=0)
(actual time=0.028..0.028 rows=0 loops=818122)
                             Hash Cond: ((("outer".username)::text =
("inner".usernamelc_)::text) AND (("outer"."domain")::text =
("inner".domain_)::text))
                             ->  Hash Join  (cost=22.52..46.72 rows=3
width=211) (actual time=0.003..0.003 rows=0 loops=818122)
                                   Hash Cond: ("outer".mailingid =
"inner".messageid_)
                                   ->  Seq Scan on lyractiverecips 
(cost=0.00..22.50 rows=334 width=215) (actual time=0.001..0.001 rows=0
loops=818122)
                                         Filter: (nextattempt >
'2004-01-20 00:00:00'::timestamp without time zone)
                                   ->  Hash  (cost=22.50..22.50 rows=6
width=4) (actual time=0.003..0.003 rows=0 loops=1)
                                         ->  Seq Scan on outmail_ 
(cost=0.00..22.50 rows=6 width=4) (actual time=0.002..0.002 rows=0
loops=1)
                                               Filter: ((type_)::text =
'list'::text)
                             ->  Hash  (cost=4.82..4.82 rows=2
width=211) (actual time=0.017..0.017 rows=0 loops=818122)
                                   ->  Index Scan using pk_members_ on
members_ a  (cost=0.00..4.82 rows=2 width=211) (actual time=0.011..0.013
rows=1 loops=818122)
                                         Index Cond: (memberid_ = $0)
 Total runtime: 114474.407 ms
(34 rows)

that's with no data in lyractiverecips or lyrcompletedrecips. With data
in those tables, the query still hasn't completed after several hours on
two different machines.

thanks,
-- 
Jack Coates, Lyris Technologies Applications Engineer
510-549-4350 x148, jack@lyris.com
"Interoperability is the keyword, uniformity is a dead end."
				--Olivier Fourdan





view thread (14+ messages)  latest in thread

Message-ID: <1075338353.7494.71.camel@cletus.lyris.com>
Permalink:  ../1075338353.7494.71.camel@cletus.lyris.com/
Also on:    postgresql.org/message-id/1075338353.7494.71.camel@cletus.lyris.com

 · 

reply

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: jack@lyris.com
  Subject: Re: query optimization question
  In-Reply-To: <1075338353.7494.71.camel@cletus.lyris.com>

* 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