Re: query optimization question
От | Jack Coates |
---|---|
Тема | Re: query optimization question |
Дата | |
Msg-id | 1075403095.7494.117.camel@cletus.lyris.com обсуждение исходный текст |
Ответ на | Re: query optimization question (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: query optimization question
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-performance |
On Thu, 2004-01-29 at 10:05, Tom Lane wrote: > Jack Coates <jack@lyris.com> writes: > > That completed in 3.5 minutes on MS-SQL. I killed the query this morning > > after 15 hours on PostgreSQL 7.4. I tried a GROUP BY memberid_ HAVING > > variation, which completed in 59 seconds on MS-SQL. I killed it after 35 > > minutes on PostgreSQL. > > Hm. I'd like to think that 7.4 would be competitive on grouping > queries. What sort of plan did you get from it? Comparable to the first plan. jackdb=# explain SELECT DISTINCT members_.memberid_ jackdb-# FROM members_ jackdb-# WHERE ( members_.List_='list1' jackdb(# AND members_.MemberType_='normal' jackdb(# AND members_.SubType_='mail' jackdb(# AND members_.emailaddr_ IS NOT NULL ) jackdb-# GROUP BY memberid_ HAVING ( jackdb(# ( select count(*) from lyrActiveRecips, outmail_ jackdb(# where outmail_.MessageID_ = lyrActiveRecips.MailingID jackdb(# and outmail_.Type_ = 'list' jackdb(# and members_.MemberID_ = lyrActiveRecips.MemberID jackdb(# and lyrActiveRecips.NextAttempt > '2004-01-20 00:00:00' ) jackdb(# + jackdb(# ( select count(*) from lyrCompletedRecips, outmail_ jackdb(# where members_.MemberID_ = lyrCompletedRecips.MemberID jackdb(# and outmail_.MessageID_ = lyrCompletedRecips.MailingID jackdb(# and outmail_.Type_ = 'list' jackdb(# and lyrCompletedRecips.FinalAttempt > '2004-01-20 00:00:00' jackdb(# and lyrCompletedRecips.CompletionStatusID = 300 ) jackdb(# = 3 ); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Unique (cost=453.08..453.09 rows=1 width=4) -> Group (cost=453.08..453.09 rows=1 width=4) -> Sort (cost=453.08..453.08 rows=1 width=4) Sort Key: memberid_ -> Index Scan using ix_members_list_notifyerr on members_ (cost=0.00..453.07 rows=1 width=4) 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=39.64..39.64 rows=1 width=0) -> Hash Join (cost=17.10..39.64 rows=1 width=0) Hash Cond: ("outer".messageid_ = "inner".mailingid) -> Seq Scan on outmail_ (cost=0.00..22.50 rows=6 width=4) Filter: ((type_)::text = 'list'::text) -> Hash (cost=17.09..17.09 rows=1 width=4) -> Index Scan using ix_completedrecipsmemberid on lyrcompletedrecips (cost=0.00..17.09 rows=1 width=4) Index Cond: ($0 = memberid) Filter: ((finalattempt > '2004-01-20 00:00:00'::timestamp without time zone) AND (completionstatusid = 300)) -> Aggregate (cost=47.55..47.55 rows=1 width=0) -> Hash Join (cost=25.00..47.55 rows=1 width=0) Hash Cond: ("outer".messageid_ = "inner".mailingid) -> Seq Scan on outmail_ (cost=0.00..22.50 rows=6 width=4) Filter: ((type_)::text = 'list'::text) -> Hash (cost=25.00..25.00 rows=2 width=4) -> Seq Scan on lyractiverecips (cost=0.00..25.00 rows=2 width=4) Filter: (($0 = memberid) AND (nextattempt > '2004-01-20 00:00:00'::timestamp without time zone)) (25 rows) -- Jack Coates, Lyris Technologies Applications Engineer 510-549-4350 x148, jack@lyris.com "Interoperability is the keyword, uniformity is a dead end." --Olivier Fourdan
В списке pgsql-performance по дате отправления: