pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jack Coates <jack@lyris.com>
To: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: query optimization question
Date: Thu, 29 Jan 2004 11:04:55 -0800
Message-ID: <1075403095.7494.117.camel@cletus.lyris.com> (raw)
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>

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





view thread (14+ messages)  latest in thread

Message-ID: <1075403095.7494.117.camel@cletus.lyris.com>
Permalink:  ../1075403095.7494.117.camel@cletus.lyris.com/
Also on:    postgresql.org/message-id/1075403095.7494.117.camel@cletus.lyris.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: jack@lyris.com
  Subject: Re: query optimization question
  In-Reply-To: <1075403095.7494.117.camel@cletus.lyris.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox