Re: query optimization question

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