Re: query optimization question

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