Skip site navigation (1) Skip section navigation (2)

Re: query optimization question

From: Jack Coates <jack(at)lyris(dot)com>
To: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: query optimization question
Date: 2004-01-29 19:04:55
Message-ID: 1075403095.7494.117.camel@cletus.lyris.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, 2004-01-29 at 10:05, Tom Lane wrote:
> Jack Coates <jack(at)lyris(dot)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(at)lyris(dot)com
"Interoperability is the keyword, uniformity is a dead end."
				--Olivier Fourdan



In response to

Responses

pgsql-performance by date

Next:From: Bill MoranDate: 2004-01-29 19:12:14
Subject: Re: On the performance of views
Previous:From: Josh BerkusDate: 2004-01-29 18:41:19
Subject: Re: On the performance of views

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group