X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 88515D1C9B4 for ; Thu, 29 Jan 2004 19:05:29 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 67315-05 for ; Thu, 29 Jan 2004 15:04:58 -0400 (AST) Received: from luna.lyris.com (luna.lyris.com [216.91.57.116]) by svr1.postgresql.org (Postfix) with ESMTP id D701AD1C954 for ; Thu, 29 Jan 2004 15:04:55 -0400 (AST) Received: from luna.lyris.com (luna.lyris.com [216.91.57.116]) by luna.lyris.com (8.12.8/8.12.8) with ESMTP id i0TJ4uIt014970 for ; Thu, 29 Jan 2004 11:04:56 -0800 Received: from luna.lyris.com ([216.91.57.116]) by luna.lyris.com (MailMonitor for SMTP v1.2.2 ) ; Thu, 29 Jan 2004 11:04:56 -0800 (PST) Received: from cletus.lyris.com (cletus.lyris.com [216.91.56.155]) by luna.lyris.net with SMTP (MailShield v2.04 - LINUX Jul 17 2001 16:58:31); Thu, 29 Jan 2004 11:04:55 -0800 Subject: Re: query optimization question From: Jack Coates To: pgsql-performance In-Reply-To: <25064.1075399504@sss.pgh.pa.us> References: <1075338353.7494.71.camel@cletus.lyris.com> <10115.1075341889@sss.pgh.pa.us> <1075396980.7494.107.camel@cletus.lyris.com> <25064.1075399504@sss.pgh.pa.us> Content-Type: text/plain Organization: Lyris Technologies, Inc. Message-Id: <1075403095.7494.117.camel@cletus.lyris.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.4-9mdk Date: Thu, 29 Jan 2004 11:04:55 -0800 Content-Transfer-Encoding: 7bit X-SMTP-HELO: cletus.lyris.com X-SMTP-MAIL-FROM: jack@lyris.com X-SMTP-RCPT-TO: pgsql-performance@postgresql.org X-SMTP-PEER-INFO: cletus.lyris.com [216.91.56.155] X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/305 X-Sequence-Number: 5479 On Thu, 2004-01-29 at 10:05, Tom Lane wrote: > Jack Coates 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