pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
query optimization question
14+ messages / 4 participants
[nested] [flat]

* query optimization question
@ 2004-01-29 01:05 Jack Coates <jack@lyris.com>
  2004-01-29 02:04 ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 14+ messages in thread

From: Jack Coates @ 2004-01-29 01:05 UTC (permalink / raw)
  To: pgsql-performance

Hi all,

I've got a query that needs some help, please. Is there a way to avoid
all the looping? I've got freedom to work with the double-indented
sections below ) AND (, but the initial select distinct wrapper is much
more difficult to change. This is auto-generated code.

explain analyze SELECT DISTINCT members_.emailaddr_, members_.memberid_ 
FROM members_  WHERE ( 
	members_.List_='list1'  
	AND members_.MemberType_='normal'  
	AND members_.SubType_='mail'  
	AND members_.emailaddr_ IS NOT NULL 
	) AND (
		( select count(*) from lyrActiveRecips, members_ a, outmail_  
		where lyrActiveRecips.UserName = a.UserNameLC_  
		and lyrActiveRecips.Domain = a.Domain_  
		and a.MemberID_ = members_.MemberID_  
		and outmail_.MessageID_ = lyrActiveRecips.MailingID  
		and outmail_.Type_ = 'list'  
		and lyrActiveRecips.NextAttempt > '2004-01-20 00:00:00' 
		)
		  +  
		( select count(*) from lyrCompletedRecips, members_ a, outmail_  
		where a.MemberID_ = lyrCompletedRecips.MemberID  
		and a.UserNameLC_ = members_.UserNameLC_  
		and a.Domain_ = members_.Domain_  
		and outmail_.MessageID_ = lyrCompletedRecips.MailingID  
		and outmail_.Type_ = 'list'  
		and lyrCompletedRecips.FinalAttempt > '2004-01-20 00:00:00'  
		and lyrCompletedRecips.CompletionStatusID = 300 )  
		  = 3 
	) 
;
                                                                             QUERY PLAN                                                                              
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=537.06..537.07 rows=1 width=72) (actual
time=114460.908..114460.908 rows=0 loops=1)
   ->  Sort  (cost=537.06..537.06 rows=1 width=72) (actual
time=114460.905..114460.905 rows=0 loops=1)
         Sort Key: emailaddr_, memberid_
         ->  Index Scan using ix_members_list_notifyerr on members_ 
(cost=0.00..537.05 rows=1 width=72) (actual time=114460.893..114460.893
rows=0 loops=1)
               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=52.39..52.39 rows=1 width=0)
(actual time=0.089..0.090 rows=1 loops=818122)
                       ->  Hash Join  (cost=47.55..52.39 rows=1 width=0)
(actual time=0.086..0.086 rows=0 loops=818122)
                             Hash Cond: ("outer".memberid_ =
"inner".memberid)
                             ->  Index Scan using ix_members_emaillc on
members_ a  (cost=0.00..4.83 rows=1 width=4) (actual time=0.077..0.081
rows=1 loops=818122)
                                   Index Cond: (((domain_)::text =
($2)::text) AND ((usernamelc_)::text = ($1)::text))
                             ->  Hash  (cost=47.55..47.55 rows=1
width=4) (actual time=0.025..0.025 rows=0 loops=1)
                                   ->  Hash Join  (cost=25.00..47.55
rows=1 width=4) (actual time=0.023..0.023 rows=0 loops=1)
                                         Hash Cond: ("outer".messageid_
= "inner".mailingid)
                                         ->  Seq Scan on outmail_ 
(cost=0.00..22.50 rows=6 width=4) (actual time=0.001..0.001 rows=0
loops=1)
                                               Filter: ((type_)::text =
'list'::text)
                                         ->  Hash  (cost=25.00..25.00
rows=2 width=8) (actual time=0.003..0.003 rows=0 loops=1)
                                               ->  Seq Scan on
lyrcompletedrecips  (cost=0.00..25.00 rows=2 width=8) (actual
time=0.001..0.001 rows=0 loops=1)
                                                     Filter:
((finalattempt > '2004-01-20 00:00:00'::timestamp without time zone) AND
(completionstatusid = 300))
                 ->  Aggregate  (cost=51.59..51.59 rows=1 width=0)
(actual time=0.033..0.034 rows=1 loops=818122)
                       ->  Hash Join  (cost=27.35..51.59 rows=1 width=0)
(actual time=0.028..0.028 rows=0 loops=818122)
                             Hash Cond: ((("outer".username)::text =
("inner".usernamelc_)::text) AND (("outer"."domain")::text =
("inner".domain_)::text))
                             ->  Hash Join  (cost=22.52..46.72 rows=3
width=211) (actual time=0.003..0.003 rows=0 loops=818122)
                                   Hash Cond: ("outer".mailingid =
"inner".messageid_)
                                   ->  Seq Scan on lyractiverecips 
(cost=0.00..22.50 rows=334 width=215) (actual time=0.001..0.001 rows=0
loops=818122)
                                         Filter: (nextattempt >
'2004-01-20 00:00:00'::timestamp without time zone)
                                   ->  Hash  (cost=22.50..22.50 rows=6
width=4) (actual time=0.003..0.003 rows=0 loops=1)
                                         ->  Seq Scan on outmail_ 
(cost=0.00..22.50 rows=6 width=4) (actual time=0.002..0.002 rows=0
loops=1)
                                               Filter: ((type_)::text =
'list'::text)
                             ->  Hash  (cost=4.82..4.82 rows=2
width=211) (actual time=0.017..0.017 rows=0 loops=818122)
                                   ->  Index Scan using pk_members_ on
members_ a  (cost=0.00..4.82 rows=2 width=211) (actual time=0.011..0.013
rows=1 loops=818122)
                                         Index Cond: (memberid_ = $0)
 Total runtime: 114474.407 ms
(34 rows)

that's with no data in lyractiverecips or lyrcompletedrecips. With data
in those tables, the query still hasn't completed after several hours on
two different machines.

thanks,
-- 
Jack Coates, Lyris Technologies Applications Engineer
510-549-4350 x148, jack@lyris.com
"Interoperability is the keyword, uniformity is a dead end."
				--Olivier Fourdan





^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: query optimization question
  2004-01-29 01:05 query optimization question Jack Coates <jack@lyris.com>
@ 2004-01-29 02:04 ` Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 17:23   ` Re: query optimization question Jack Coates <jack@lyris.com>
  0 siblings, 1 reply; 14+ messages in thread

From: Tom Lane @ 2004-01-29 02:04 UTC (permalink / raw)
  To: Jack Coates <jack@lyris.com>; +Cc: pgsql-performance

Jack Coates <jack@lyris.com> writes:
> I've got a query that needs some help, please. Is there a way to avoid
> all the looping? I've got freedom to work with the double-indented
> sections below ) AND (, but the initial select distinct wrapper is much
> more difficult to change. This is auto-generated code.

Well, you're not going to get any serious improvement without a
wholesale rewrite of the query --- I'd think that something driven by
a GROUP BY memberid_ HAVING count(*) = whatever at the outer level would
be a better way to approach it.  As you have it, the system has no
choice but to fully evaluate two very expensive subselects, from scratch,
for each outer row.

However...

> 		( select count(*) from lyrActiveRecips, members_ a, outmail_  
> 		where lyrActiveRecips.UserName = a.UserNameLC_  
> 		and lyrActiveRecips.Domain = a.Domain_  
> 		and a.MemberID_ = members_.MemberID_  
> 		and outmail_.MessageID_ = lyrActiveRecips.MailingID  

Is memberid_ a unique identifier for members_, as one would think from
the name?  If so, can't you drop the join of members_ a in this
subselect, and just use the corresponding fields from the outer table?

> 		( select count(*) from lyrCompletedRecips, members_ a, outmail_
> 		where a.MemberID_ = lyrCompletedRecips.MemberID  
> 		and a.UserNameLC_ = members_.UserNameLC_  
> 		and a.Domain_ = members_.Domain_  
> 		and outmail_.MessageID_ = lyrCompletedRecips.MailingID  

Why are the join conditions different here from the other subselect?
Can't you rephrase them the same as above, and then again remove the
inner appearance of members_ ?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: query optimization question
  2004-01-29 01:05 query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 02:04 ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-01-29 17:23   ` Jack Coates <jack@lyris.com>
  2004-01-29 18:05     ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 14+ messages in thread

From: Jack Coates @ 2004-01-29 17:23 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

On Wed, 2004-01-28 at 18:04, Tom Lane wrote:
> Jack Coates <jack@lyris.com> writes:
> > I've got a query that needs some help, please. Is there a way to avoid
> > all the looping? I've got freedom to work with the double-indented
> > sections below ) AND (, but the initial select distinct wrapper is much
> > more difficult to change. This is auto-generated code.
> 
> Well, you're not going to get any serious improvement without a
> wholesale rewrite of the query --- I'd think that something driven by
> a GROUP BY memberid_ HAVING count(*) = whatever at the outer level would
> be a better way to approach it.  As you have it, the system has no
> choice but to fully evaluate two very expensive subselects, from scratch,
> for each outer row.
> 

I hear you. There's definitely an understanding that this tool can
generate some gnarly queries, and we want to redesign in a way that will
allow some more intelligence to be applied to the problem. In the
meantime, I'll be happy if PG grinds at the same level as other
databases. MS-SQL completed that query in 25 minutes on a database with
31 times the data in it. Since I'm one of the bigger *nix fans around
here, that doesn't make me happy.

> However...
> 
> > 		( select count(*) from lyrActiveRecips, members_ a, outmail_  
> > 		where lyrActiveRecips.UserName = a.UserNameLC_  
> > 		and lyrActiveRecips.Domain = a.Domain_  
> > 		and a.MemberID_ = members_.MemberID_  
> > 		and outmail_.MessageID_ = lyrActiveRecips.MailingID  
> 
> Is memberid_ a unique identifier for members_, as one would think from
> the name?  If so, can't you drop the join of members_ a in this
> subselect, and just use the corresponding fields from the outer table?
> 
> > 		( select count(*) from lyrCompletedRecips, members_ a, outmail_
> > 		where a.MemberID_ = lyrCompletedRecips.MemberID  
> > 		and a.UserNameLC_ = members_.UserNameLC_  
> > 		and a.Domain_ = members_.Domain_  
> > 		and outmail_.MessageID_ = lyrCompletedRecips.MailingID  
> 
> Why are the join conditions different here from the other subselect?
> Can't you rephrase them the same as above, and then again remove the
> inner appearance of members_ ?
> 
> 			regards, tom lane

unfortunately, the column names are different between lyrcompletedrecips
and lyractiverecips. However, one thing we were able to do is to reduce
the number of queries by not trying to match across multiple lists.

SELECT DISTINCT members_.emailaddr_, members_.memberid_ FROM members_ 
WHERE ( members_.List_='list1' 
	AND members_.MemberType_='normal' 
	AND members_.SubType_='mail' 
	AND members_.emailaddr_ IS NOT NULL ) 
AND ( 
	( select count(*) from lyrActiveRecips, outmail_ 
	where outmail_.MessageID_ = lyrActiveRecips.MailingID 
	and outmail_.Type_ = 'list' 
	and members_.MemberID_ = lyrActiveRecips.MemberID 
	and lyrActiveRecips.NextAttempt > '2004-01-20 00:00:00' )
	 + 
	( select count(*) from lyrCompletedRecips, outmail_ 
	where members_.MemberID_ = lyrCompletedRecips.MemberID 
	and outmail_.MessageID_ = lyrCompletedRecips.MailingID 
	and outmail_.Type_ = 'list' 
	and lyrCompletedRecips.FinalAttempt > '2004-01-20 00:00:00' 
	and lyrCompletedRecips.CompletionStatusID = 300 )
	 = 3
);

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.

On a more positive note, if you remember the benchmarking I was doing
last month, PostgreSQL got some pretty good relative numbers. It
requires a lot of hand-holding and tuning relative to MS-SQL, but it
certainly beat the pants off of Oracle 8 and 9 for speed and ease of
management. Oracle 8 was in fact unable to complete the uglier stress
tests. I'll be working on a tuning recommendations white paper today.

thanks for all the help,
-- 
Jack Coates, Lyris Technologies Applications Engineer
510-549-4350 x148, jack@lyris.com
"Interoperability is the keyword, uniformity is a dead end."
				--Olivier Fourdan





^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: query optimization question
  2004-01-29 01:05 query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 02:04 ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 17:23   ` Re: query optimization question Jack Coates <jack@lyris.com>
@ 2004-01-29 18:05     ` Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 19:04       ` Re: query optimization question Jack Coates <jack@lyris.com>
  0 siblings, 1 reply; 14+ messages in thread

From: Tom Lane @ 2004-01-29 18:05 UTC (permalink / raw)
  To: Jack Coates <jack@lyris.com>; +Cc: pgsql-performance

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?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: query optimization question
  2004-01-29 01:05 query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 02:04 ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 17:23   ` Re: query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 18:05     ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-01-29 19:04       ` Jack Coates <jack@lyris.com>
  2004-01-29 19:31         ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 14+ messages in thread

From: Jack Coates @ 2004-01-29 19:04 UTC (permalink / raw)
  To: pgsql-performance

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





^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: query optimization question
  2004-01-29 01:05 query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 02:04 ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 17:23   ` Re: query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 18:05     ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 19:04       ` Re: query optimization question Jack Coates <jack@lyris.com>
@ 2004-01-29 19:31         ` Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 21:23           ` Re: query optimization question Jack Coates <jack@lyris.com>
  2004-01-30 07:19           ` Re: query optimization question Dennis Bjorklund <db@zigo.dhs.org>
  0 siblings, 2 replies; 14+ messages in thread

From: Tom Lane @ 2004-01-29 19:31 UTC (permalink / raw)
  To: Jack Coates <jack@lyris.com>; +Cc: pgsql-performance

Jack Coates <jack@lyris.com> writes:
> 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 ( 

Um, that's not what I had in mind at all.  Does GROUP BY actually do
anything at all here?  (You didn't answer me as to whether memberid_
is a unique identifier or not, but if it is, this GROUP BY is just an
expensive no-op.)

What I was envisioning was pulling the sub-selects up to the top level
and using grouping to calculate the count(*) values for all memberids
in parallel.  Roughly speaking it would look like (again assuming
memberid_ is unique)

SELECT memberid_ FROM
(
  SELECT memberid_ FROM lyrActiveRecips, members_, outmail
  WHERE (all the conditions for this case)
  UNION ALL
  SELECT memberid_ FROM lyrCompletedRecips, members_, outmail
  WHERE (all the conditions for this case)
)
GROUP BY memberid_ HAVING count(*) = 3;

However, if you can't change the boilerplate part of your query then
this is all blue-sky speculation anyway.  What I'm actually more
interested in is your statement that MSSQL can do the original query
quickly.  I find that a bit hard to believe because I don't see any
relevant optimization techniques.  Do they have any equivalent to
EXPLAIN that would give some hint how they're doing it?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: query optimization question
  2004-01-29 01:05 query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 02:04 ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 17:23   ` Re: query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 18:05     ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 19:04       ` Re: query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 19:31         ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-01-29 21:23           ` Jack Coates <jack@lyris.com>
  2004-01-29 22:01             ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 14+ messages in thread

From: Jack Coates @ 2004-01-29 21:23 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

On Thu, 2004-01-29 at 11:31, Tom Lane wrote:
> Jack Coates <jack@lyris.com> writes:
> > 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 ( 
> 
> Um, that's not what I had in mind at all.  Does GROUP BY actually do
> anything at all here?  (You didn't answer me as to whether memberid_
> is a unique identifier or not, but if it is, this GROUP BY is just an
> expensive no-op.)
> 

Sorry for the misunderstanding. It should be unique, yes.

> What I was envisioning was pulling the sub-selects up to the top level
> and using grouping to calculate the count(*) values for all memberids
> in parallel.  Roughly speaking it would look like (again assuming
> memberid_ is unique)
> 
> SELECT memberid_ FROM
> (
>   SELECT memberid_ FROM lyrActiveRecips, members_, outmail
>   WHERE (all the conditions for this case)
>   UNION ALL
>   SELECT memberid_ FROM lyrCompletedRecips, members_, outmail
>   WHERE (all the conditions for this case)
> )
> GROUP BY memberid_ HAVING count(*) = 3;
> 
> However, if you can't change the boilerplate part of your query then
> this is all blue-sky speculation anyway.  

Got it now -- I'm running into some subquery errors trying to implement
this, anyway.

> What I'm actually more
> interested in is your statement that MSSQL can do the original query
> quickly.  I find that a bit hard to believe because I don't see any
> relevant optimization techniques.  Do they have any equivalent to
> EXPLAIN that would give some hint how they're doing it?

yup -- here it is. It will probably be a nasty mess after linewrap gets
done with it, so let me know if you'd like me to post a copy on ftp.

SELECT DISTINCT members_.memberid_   FROM members_   WHERE (
members_.List_='list1'    AND members_.MemberType_='normal'    AND
members_.SubType_='mail' )   GROUP BY memberid_ HAVING (       ( select
count(*) from lyrActiveRecips, outmail_    where
outmail	11	1	0	NULL	NULL	1	NULL	102274.5	NULL	NULL	NULL	104.10356	NULL	NULL	SELECT	0	NULL
  |--Parallelism(Gather Streams)	11	2	1	Parallelism	Gather
Streams	NULL	NULL	102274.5	0.0	0.22011127	23	104.10356	[members_].[MemberID_]	NULL	PLAN_ROW	-1	1.0
       |--Filter(WHERE:(If ([Expr1006] IS NULL) then 0 else
[Expr1006]+If ([Expr1012] IS NULL) then 0 else
[Expr1012]=3))	11	3	2	Filter	Filter	WHERE:(If ([Expr1006] IS NULL) then
0 else [Expr1006]+If ([Expr1012] IS NULL) then 0 else
[Expr1012]=3)	NULL	102274.5	0.0	3.5393338	23	103.88345	[members_].[MemberID_]	NULL	PLAN_ROW	-1	1.0
            |--Hash Match(Right Outer Join,
HASH:([lyrCompletedRecips].[MemberID])=([members_].[MemberID_]),
RESIDUAL:([members_].[MemberID_]=[lyrCompletedRecips].[MemberID]))	11	4	3	Hash Match	Right Outer Join	HASH:([lyrCompletedRecips].[MemberID])=([members_].[MemberID_]), RESIDUAL:([members_].[MemberID_]=[lyrCompletedRecips].[MemberID])	NULL	4782883.5	0.0	21.874712	23	100.34412	[members_].[MemberID_], [Expr1006], [Expr1012]	NULL	PLAN_ROW	-1	1.0
                 |--Compute
Scalar(DEFINE:([Expr1012]=Convert([Expr1020])))	11	5	4	Compute
Scalar	Compute
Scalar	DEFINE:([Expr1012]=Convert([Expr1020]))	[Expr1012]=Convert([Expr1020])	119575.35	0.0	1.3723248	15	4.3749919	[lyrCompletedRecips].[MemberID], [Expr1012]	NULL	PLAN_ROW	-1	1.0
                 |    |--Hash Match(Aggregate,
HASH:([lyrCompletedRecips].[MemberID]),
RESIDUAL:([lyrCompletedRecips].[MemberID]=[lyrCompletedRecips].[MemberID]) DEFINE:([Expr1020]=COUNT(*)))	11	6	5	Hash Match	Aggregate	HASH:([lyrCompletedRecips].[MemberID]), RESIDUAL:([lyrCompletedRecips].[MemberID]=[lyrCompletedRecips].[MemberID])	[Expr1020]=COUNT(*)	119575.35	0.0	1.3723248	15	4.3749919	[lyrCompletedRecips].[MemberID], [Expr1020]	NULL	PLAN_ROW	-1	1.0
                 |         |--Parallelism(Repartition Streams, PARTITION
COLUMNS:([lyrCompletedRecips].[MemberID]))	11	7	6	Parallelism	Repartition Streams	PARTITION COLUMNS:([lyrCompletedRecips].[MemberID])	NULL	119640.6	0.0	0.32407209	173	3.002667	[lyrCompletedRecips].[MemberID]	NULL	PLAN_ROW	-1	1.0
                 |              |--Nested Loops(Inner Join, OUTER
REFERENCES:([outmail_].[MessageID_]))	11	8	7	Nested Loops	Inner
Join	OUTER
REFERENCES:([outmail_].[MessageID_])	NULL	119640.6	0.0	0.75014657	173	2.6785948	[lyrCompletedRecips].[MemberID]	NULL	PLAN_ROW	-1	1.0
                 |                   |--Parallelism(Distribute
Streams)	11	9	8	Parallelism	Distribute
Streams	NULL	NULL	1.0	0.0	2.8501874E-2	128	9.4664574E-2	[outmail_].[MessageID_]	NULL	PLAN_ROW	-1	1.0
                 |                   |    |--Clustered Index
Scan(OBJECT:([lmdb].[dbo].[outmail_].[IX_outmail_list]),
WHERE:([outmail_].[Type_]='list'))	11	10	9	Clustered Index
Scan	Clustered Index
Scan	OBJECT:([lmdb].[dbo].[outmail_].[IX_outmail_list]),
WHERE:([outmail_].[Type_]='list')	[outmail_].[Type_],
[outmail_].[MessageID_]	1.0	0.01878925	3.9800001E-5	128	3.7658099E-2	[outmail_].[Type_], [outmail_].[MessageID_]	NULL	PLAN_ROW	0	1.0
                 |                   |--Clustered Index
Seek(OBJECT:([lmdb].[dbo].[lyrCompletedRecips].[IX_CompletedRecipsMailingID]), SEEK:([lyrCompletedRecips].[MailingID]=[outmail_].[MessageID_]),  WHERE:([lyrCompletedRecips].[CompletionStatusID]=300 AN	11	11	8	Clustered Index Seek	Clustered Index Seek	OBJECT:([lmdb].[dbo].[lyrCompletedRecips].[IX_CompletedRecipsMailingID]), SEEK:([lyrCompletedRecips].[MailingID]=[outmail_].[MessageID_]),  WHERE:([lyrCompletedRecips].[CompletionStatusID]=300 AND [lyrCompletedRecips].[FinalAttempt]>'Jan 20 2004 12:00AM') 	[lyrCompletedRecips].[CompletionStatusID], [lyrCompletedRecips].[FinalAttempt], [lyrCompletedRecips].[MemberID]	119640.6	0.5750553	0.13207871	53	1.5463468	[lyrCompletedRecips].[CompletionStatusID], [lyrCompletedRecips].[FinalAttempt], [lyrCompletedRecips].[MemberID]	NULL	PLAN_ROW	-1	3.0
                 |--Parallelism(Repartition Streams, PARTITION
COLUMNS:([members_].[MemberID_]))	11	19	4	Parallelism	Repartition
Streams	PARTITION
COLUMNS:([members_].[MemberID_])	NULL	4782883.5	0.0	15.474822	19	74.094414	[members_].[MemberID_], [Expr1006]	NULL	PLAN_ROW	-1	1.0
                      |--Nested Loops(Left Outer Join,
WHERE:([members_].[MemberID_]=[lyrActiveRecips].[MemberID]))	11	20	19	Nested Loops	Left Outer Join	WHERE:([members_].[MemberID_]=[lyrActiveRecips].[MemberID])	NULL	4782883.5	0.0	9.9962263	19	58.619591	[members_].[MemberID_], [Expr1006]	NULL	PLAN_ROW	-1	1.0
                           |--Clustered Index
Seek(OBJECT:([lmdb].[dbo].[members_].[IX_members_List_EmailLC]),
SEEK:([members_].[List_]='list1'), 
WHERE:([members_].[MemberType_]='normal' AND
[members_].[SubType_]='mail') ORDERED FORWARD)	11	22	20	Clustered Index
Seek	Clustered Index
Seek	OBJECT:([lmdb].[dbo].[members_].[IX_members_List_EmailLC]),
SEEK:([members_].[List_]='list1'), 
WHERE:([members_].[MemberType_]='normal' AND
[members_].[SubType_]='mail') ORDERED FORWARD	[members_].[SubType_],
[members_].[MemberType_],
[members_].[MemberID_]	4782883.5	40.160122	3.2745986	410	43.434719	[members_].[SubType_], [members_].[MemberType_], [members_].[MemberID_]	NULL	PLAN_ROW	-1	1.0
                           |--Table Spool	11	24	20	Table Spool	Lazy
Spool	NULL	NULL	1.0	1.6756756E-2	3.7999999E-7	15	0.90211391	[lyrActiveRecips].[MemberID], [Expr1006]	NULL	PLAN_ROW	-1	4782883.5
                                |--Compute
Scalar(DEFINE:([Expr1006]=Convert([Expr1021])))	11	25	24	Compute
Scalar	Compute
Scalar	DEFINE:([Expr1006]=Convert([Expr1021]))	[Expr1006]=Convert([Expr1021])	1.0	0.0	7.6000001E-6	15	2.4437904E-2	[lyrActiveRecips].[MemberID], [Expr1006]	NULL	PLAN_ROW	-1	1.0
                                     |--Stream Aggregate(GROUP
BY:([lyrActiveRecips].[MemberID])
DEFINE:([Expr1021]=Count(*)))	11	26	25	Stream Aggregate	Aggregate	GROUP
BY:([lyrActiveRecips].[MemberID])	[Expr1021]=Count(*)	1.0	0.0	7.6000001E-6	15	2.4437904E-2	[lyrActiveRecips].[MemberID], [Expr1021]	NULL	PLAN_ROW	-1	1.0
                                          |--Sort(ORDER
BY:([lyrActiveRecips].[MemberID] ASC))	11	27	26	Sort	Sort	ORDER
BY:([lyrActiveRecips].[MemberID]
ASC)	NULL	1.0	1.1261261E-2	1.00011E-4	11	2.4430305E-2	[lyrActiveRecips].[MemberID]	NULL	PLAN_ROW	-1	1.0
                                              
|--Filter(WHERE:([outmail_].[Type_]='list'))	11	28	27	Filter	Filter	WHERE:([outmail_].[Type_]='list')	NULL	1.0	0.0	4.7999998E-7	156	1.3069032E-2	[lyrActiveRecips].[MemberID]	NULL	PLAN_ROW	-1	1.0
                                                    |--Bookmark
Lookup(BOOKMARK:([Bmk1004]),
OBJECT:([lmdb].[dbo].[outmail_]))	11	29	28	Bookmark Lookup	Bookmark
Lookup	BOOKMARK:([Bmk1004]),
OBJECT:([lmdb].[dbo].[outmail_])	[outmail_].[Type_]	1.0	3.1249749E-3	0.0000011	156	1.3068552E-2	[lyrActiveRecips].[MemberID], [outmail_].[Type_]	NULL	PLAN_ROW	-1	1.0
                                                         |--Nested
Loops(Inner Join, OUTER
REFERENCES:([lyrActiveRecips].[MailingID]))	11	30	29	Nested Loops	Inner
Join	OUTER
REFERENCES:([lyrActiveRecips].[MailingID])	NULL	1.0	0.0	0.00001254	138	9.9424766E-3	[lyrActiveRecips].[MemberID], [Bmk1004]	NULL	PLAN_ROW	-1	1.0
                                                             
|--Bookmark Lookup(BOOKMARK:([Bmk1002]),
OBJECT:([lmdb].[dbo].[lyrActiveRecips]))	11	31	30	Bookmark
Lookup	Bookmark Lookup	BOOKMARK:([Bmk1002]),
OBJECT:([lmdb].[dbo].[lyrActiveRecips])	[lyrActiveRecips].[MemberID],
[lyrActiveRecips].[MailingID]	1.0	3.1249749E-3	0.0000011	53	6.4091529E-3	[lyrActiveRecips].[MemberID], [lyrActiveRecips].[MailingID]	NULL	PLAN_ROW	-1	1.0
                                                              |   
|--Index
Seek(OBJECT:([lmdb].[dbo].[lyrActiveRecips].[jacktest_lar_date_ix]),
SEEK:([lyrActiveRecips].[NextAttempt] > 'Jan 20 2004 12:00AM') ORDERED
FORWARD)	11	32	31	Index Seek	Index
Seek	OBJECT:([lmdb].[dbo].[lyrActiveRecips].[jacktest_lar_date_ix]),
SEEK:([lyrActiveRecips].[NextAttempt] > 'Jan 20 2004 12:00AM') ORDERED
FORWARD	[Bmk1002]	1.0	3.2034749E-3	7.9603E-5	40	3.2830781E-3	[Bmk1002]	NULL	PLAN_ROW	-1	1.0
                                                              |--Index
Seek(OBJECT:([lmdb].[dbo].[outmail_].[PK_outmail_]),
SEEK:([outmail_].[MessageID_]=[lyrActiveRecips].[MailingID]) ORDERED
FORWARD)	11	33	30	Index Seek	Index
Seek	OBJECT:([lmdb].[dbo].[outmail_].[PK_outmail_]),
SEEK:([outmail_].[MessageID_]=[lyrActiveRecips].[MailingID]) ORDERED
FORWARD	[Bmk1004]	1.0	3.2034749E-3	7.9603E-5	93	3.520784E-3	[Bmk1004]	NULL	PLAN_ROW	-1	3.0
-- 
Jack Coates, Lyris Technologies Applications Engineer
510-549-4350 x148, jack@lyris.com
"Interoperability is the keyword, uniformity is a dead end."
				--Olivier Fourdan





^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: query optimization question
  2004-01-29 01:05 query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 02:04 ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 17:23   ` Re: query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 18:05     ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 19:04       ` Re: query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 19:31         ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 21:23           ` Re: query optimization question Jack Coates <jack@lyris.com>
@ 2004-01-29 22:01             ` Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 22:26               ` Re: query optimization question Jack Coates <jack@lyris.com>
  2004-01-30 10:00               ` Re: query optimization question Greg Stark <gsstark@mit.edu>
  0 siblings, 2 replies; 14+ messages in thread

From: Tom Lane @ 2004-01-29 22:01 UTC (permalink / raw)
  To: Jack Coates <jack@lyris.com>; +Cc: pgsql-performance

Jack Coates <jack@lyris.com> writes:
> yup -- here it is. It will probably be a nasty mess after linewrap gets
> done with it,

yup, sure is :-(  If I was familiar with the layout I could probably
decipher where the line breaks are supposed to be, but right now I'm
just confused.

> so let me know if you'd like me to post a copy on ftp.

Probably better to repost it as a gzip'd attachment.  That should
protect the formatting and get it into the list archives.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: query optimization question
  2004-01-29 01:05 query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 02:04 ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 17:23   ` Re: query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 18:05     ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 19:04       ` Re: query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 19:31         ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 21:23           ` Re: query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 22:01             ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-01-29 22:26               ` Jack Coates <jack@lyris.com>
  2004-01-30 07:23                 ` Re: query optimization question Dennis Bjorklund <db@zigo.dhs.org>
  1 sibling, 1 reply; 14+ messages in thread

From: Jack Coates @ 2004-01-29 22:26 UTC (permalink / raw)
  To: ; +Cc: pgsql-performance

On Thu, 2004-01-29 at 14:01, Tom Lane wrote:

> Probably better to repost it as a gzip'd attachment.  That should
> protect the formatting and get it into the list archives.
> 
> 			regards, tom lane

complete with a picture of the GUI version. 26k zipped, let's see if
this makes it through.
-- 
Jack Coates, Lyris Technologies Applications Engineer
510-549-4350 x148, jack@lyris.com
"Interoperability is the keyword, uniformity is a dead end."
				--Olivier Fourdan





^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: query optimization question
  2004-01-29 01:05 query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 02:04 ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 17:23   ` Re: query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 18:05     ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 19:04       ` Re: query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 19:31         ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 21:23           ` Re: query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 22:01             ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 22:26               ` Re: query optimization question Jack Coates <jack@lyris.com>
@ 2004-01-30 07:23                 ` Dennis Bjorklund <db@zigo.dhs.org>
  2004-01-30 17:13                   ` Re: query optimization question Jack Coates <jack@lyris.com>
  0 siblings, 1 reply; 14+ messages in thread

From: Dennis Bjorklund @ 2004-01-30 07:23 UTC (permalink / raw)
  To: Jack Coates <jack@lyris.com>; +Cc: pgsql-performance

On Thu, 29 Jan 2004, Jack Coates wrote:

> > Probably better to repost it as a gzip'd attachment.  That should
> 
> complete with a picture of the GUI version. 26k zipped, let's see if
> this makes it through.

Are you sure you attached it?

At least when it got here there was no attachment.

-- 
/Dennis Björklund




^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: query optimization question
  2004-01-29 01:05 query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 02:04 ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 17:23   ` Re: query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 18:05     ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 19:04       ` Re: query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 19:31         ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 21:23           ` Re: query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 22:01             ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 22:26               ` Re: query optimization question Jack Coates <jack@lyris.com>
  2004-01-30 07:23                 ` Re: query optimization question Dennis Bjorklund <db@zigo.dhs.org>
@ 2004-01-30 17:13                   ` Jack Coates <jack@lyris.com>
  0 siblings, 0 replies; 14+ messages in thread

From: Jack Coates @ 2004-01-30 17:13 UTC (permalink / raw)
  To: pgsql-performance

On Thu, 2004-01-29 at 23:23, Dennis Bjorklund wrote:
> On Thu, 29 Jan 2004, Jack Coates wrote:
> 
> > > Probably better to repost it as a gzip'd attachment.  That should
> > 
> > complete with a picture of the GUI version. 26k zipped, let's see if
> > this makes it through.
> 
> Are you sure you attached it?
> 
> At least when it got here there was no attachment.

argh; attached the 40K version which was in color, removed it to make
the new one with greyscale and forgot to attach that. Here it is again:
-- 
Jack Coates, Lyris Technologies Applications Engineer
510-549-4350 x148, jack@lyris.com
"Interoperability is the keyword, uniformity is a dead end."
				--Olivier Fourdan

Attachments:

  pg-perf-sql-plan.tgz (application/x-compressed-tar, 25.9K) download

^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: query optimization question
  2004-01-29 01:05 query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 02:04 ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 17:23   ` Re: query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 18:05     ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 19:04       ` Re: query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 19:31         ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 21:23           ` Re: query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 22:01             ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-01-30 10:00               ` Greg Stark <gsstark@mit.edu>
  1 sibling, 0 replies; 14+ messages in thread

From: Greg Stark @ 2004-01-30 10:00 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jack Coates <jack@lyris.com>; pgsql-performance


Tom Lane <tgl@sss.pgh.pa.us> writes:

> Jack Coates <jack@lyris.com> writes:
> > yup -- here it is. It will probably be a nasty mess after linewrap gets
> > done with it,
> 
> yup, sure is :-(  If I was familiar with the layout I could probably
> decipher where the line breaks are supposed to be, but right now I'm
> just confused.

I just replaced all newlines that are followed by lines starting in column 1
with spaces and got something reasonable:

SELECT DISTINCT members_.memberid_   FROM members_   WHERE ( members_.List_='list1'    AND members_.MemberType_='normal'    AND members_.SubType_='mail' )   GROUP BY memberid_ HAVING (       ( select count(*) from lyrActiveRecips, outmail_    where outmail	11	1	0	NULL	NULL	1	NULL	102274.5	NULL	NULL	NULL	104.10356	NULL	NULL	SELECT	0	NULL
  |--Parallelism(Gather Streams)	11	2	1	Parallelism	Gather Streams	NULL	NULL	102274.5	0.0	0.22011127	23	104.10356	[members_].[MemberID_]	NULL	PLAN_ROW	-1	1.0
       |--Filter(WHERE:(If ([Expr1006] IS NULL) then 0 else [Expr1006]+If ([Expr1012] IS NULL) then 0 else [Expr1012]=3))	11	3	2	Filter	Filter	WHERE:(If ([Expr1006] IS NULL) then 0 else [Expr1006]+If ([Expr1012] IS NULL) then 0 else [Expr1012]=3)	NULL	102274.5	0.0	3.5393338	23	103.88345	[members_].[MemberID_]	NULL	PLAN_ROW	-1	1.0
            |--Hash Match(Right Outer Join, HASH:([lyrCompletedRecips].[MemberID])=([members_].[MemberID_]), RESIDUAL:([members_].[MemberID_]=[lyrCompletedRecips].[MemberID]))	11	4	3	Hash Match	Right Outer Join	HASH:([lyrCompletedRecips].[MemberID])=([members_].[MemberID_]), RESIDUAL:([members_].[MemberID_]=[lyrCompletedRecips].[MemberID])	NULL	4782883.5	0.0	21.874712	23	100.34412	[members_].[MemberID_], [Expr1006], [Expr1012]	NULL	PLAN_ROW	-1	1.0
                 |--Compute Scalar(DEFINE:([Expr1012]=Convert([Expr1020])))	11	5	4	Compute Scalar	Compute Scalar	DEFINE:([Expr1012]=Convert([Expr1020]))	[Expr1012]=Convert([Expr1020])	119575.35	0.0	1.3723248	15	4.3749919	[lyrCompletedRecips].[MemberID], [Expr1012]	NULL	PLAN_ROW	-1	1.0
                 |    |--Hash Match(Aggregate, HASH:([lyrCompletedRecips].[MemberID]), RESIDUAL:([lyrCompletedRecips].[MemberID]=[lyrCompletedRecips].[MemberID]) DEFINE:([Expr1020]=COUNT(*)))	11	6	5	Hash Match	Aggregate	HASH:([lyrCompletedRecips].[MemberID]), RESIDUAL:([lyrCompletedRecips].[MemberID]=[lyrCompletedRecips].[MemberID])	[Expr1020]=COUNT(*)	119575.35	0.0	1.3723248	15	4.3749919	[lyrCompletedRecips].[MemberID], [Expr1020]	NULL	PLAN_ROW	-1	1.0
                 |         |--Parallelism(Repartition Streams, PARTITION COLUMNS:([lyrCompletedRecips].[MemberID]))	11	7	6	Parallelism	Repartition Streams	PARTITION COLUMNS:([lyrCompletedRecips].[MemberID])	NULL	119640.6	0.0	0.32407209	173	3.002667	[lyrCompletedRecips].[MemberID]	NULL	PLAN_ROW	-1	1.0
                 |              |--Nested Loops(Inner Join, OUTER REFERENCES:([outmail_].[MessageID_]))	11	8	7	Nested Loops	Inner Join	OUTER REFERENCES:([outmail_].[MessageID_])	NULL	119640.6	0.0	0.75014657	173	2.6785948	[lyrCompletedRecips].[MemberID]	NULL	PLAN_ROW	-1	1.0
                 |                   |--Parallelism(Distribute Streams)	11	9	8	Parallelism	Distribute Streams	NULL	NULL	1.0	0.0	2.8501874E-2	128	9.4664574E-2	[outmail_].[MessageID_]	NULL	PLAN_ROW	-1	1.0
                 |                   |    |--Clustered Index Scan(OBJECT:([lmdb].[dbo].[outmail_].[IX_outmail_list]), WHERE:([outmail_].[Type_]='list'))	11	10	9	Clustered Index Scan	Clustered Index Scan	OBJECT:([lmdb].[dbo].[outmail_].[IX_outmail_list]), WHERE:([outmail_].[Type_]='list')	[outmail_].[Type_], [outmail_].[MessageID_]	1.0	0.01878925	3.9800001E-5	128	3.7658099E-2	[outmail_].[Type_], [outmail_].[MessageID_]	NULL	PLAN_ROW	0	1.0
                 |                   |--Clustered Index Seek(OBJECT:([lmdb].[dbo].[lyrCompletedRecips].[IX_CompletedRecipsMailingID]), SEEK:([lyrCompletedRecips].[MailingID]=[outmail_].[MessageID_]),  WHERE:([lyrCompletedRecips].[CompletionStatusID]=300 AN	11	11	8	Clustered Index Seek	Clustered Index Seek	OBJECT:([lmdb].[dbo].[lyrCompletedRecips].[IX_CompletedRecipsMailingID]), SEEK:([lyrCompletedRecips].[MailingID]=[outmail_].[MessageID_]),  WHERE:([lyrCompletedRecips].[CompletionStatusID]=300 AND [lyrCompletedRecips].[FinalAttempt]>'Jan 20 2004 12:00AM') 	[lyrCompletedRecips].[CompletionStatusID], [lyrCompletedRecips].[FinalAttempt], [lyrCompletedRecips].[MemberID]	119640.6	0.5750553	0.13207871	53	1.5463468	[lyrCompletedRecips].[CompletionStatusID], [lyrCompletedRecips].[FinalAttempt], [lyrCompletedRecips].[MemberID]	NULL	PLAN_ROW	-1	3.0
                 |--Parallelism(Repartition Streams, PARTITION COLUMNS:([members_].[MemberID_]))	11	19	4	Parallelism	Repartition Streams	PARTITION COLUMNS:([members_].[MemberID_])	NULL	4782883.5	0.0	15.474822	19	74.094414	[members_].[MemberID_], [Expr1006]	NULL	PLAN_ROW	-1	1.0
                      |--Nested Loops(Left Outer Join, WHERE:([members_].[MemberID_]=[lyrActiveRecips].[MemberID]))	11	20	19	Nested Loops	Left Outer Join	WHERE:([members_].[MemberID_]=[lyrActiveRecips].[MemberID])	NULL	4782883.5	0.0	9.9962263	19	58.619591	[members_].[MemberID_], [Expr1006]	NULL	PLAN_ROW	-1	1.0
                           |--Clustered Index Seek(OBJECT:([lmdb].[dbo].[members_].[IX_members_List_EmailLC]), SEEK:([members_].[List_]='list1'),  WHERE:([members_].[MemberType_]='normal' AND [members_].[SubType_]='mail') ORDERED FORWARD)	11	22	20	Clustered Index Seek	Clustered Index Seek	OBJECT:([lmdb].[dbo].[members_].[IX_members_List_EmailLC]), SEEK:([members_].[List_]='list1'),  WHERE:([members_].[MemberType_]='normal' AND [members_].[SubType_]='mail') ORDERED FORWARD	[members_].[SubType_], [members_].[MemberType_], [members_].[MemberID_]	4782883.5	40.160122	3.2745986	410	43.434719	[members_].[SubType_], [members_].[MemberType_], [members_].[MemberID_]	NULL	PLAN_ROW	-1	1.0
                           |--Table Spool	11	24	20	Table Spool	Lazy Spool	NULL	NULL	1.0	1.6756756E-2	3.7999999E-7	15	0.90211391	[lyrActiveRecips].[MemberID], [Expr1006]	NULL	PLAN_ROW	-1	4782883.5
                                |--Compute Scalar(DEFINE:([Expr1006]=Convert([Expr1021])))	11	25	24	Compute Scalar	Compute Scalar	DEFINE:([Expr1006]=Convert([Expr1021]))	[Expr1006]=Convert([Expr1021])	1.0	0.0	7.6000001E-6	15	2.4437904E-2	[lyrActiveRecips].[MemberID], [Expr1006]	NULL	PLAN_ROW	-1	1.0
                                     |--Stream Aggregate(GROUP BY:([lyrActiveRecips].[MemberID]) DEFINE:([Expr1021]=Count(*)))	11	26	25	Stream Aggregate	Aggregate	GROUP BY:([lyrActiveRecips].[MemberID])	[Expr1021]=Count(*)	1.0	0.0	7.6000001E-6	15	2.4437904E-2	[lyrActiveRecips].[MemberID], [Expr1021]	NULL	PLAN_ROW	-1	1.0
                                          |--Sort(ORDER BY:([lyrActiveRecips].[MemberID] ASC))	11	27	26	Sort	Sort	ORDER BY:([lyrActiveRecips].[MemberID] ASC)	NULL	1.0	1.1261261E-2	1.00011E-4	11	2.4430305E-2	[lyrActiveRecips].[MemberID]	NULL	PLAN_ROW	-1	1.0
                                               |--Filter(WHERE:([outmail_].[Type_]='list'))	11	28	27	Filter	Filter	WHERE:([outmail_].[Type_]='list')	NULL	1.0	0.0	4.7999998E-7	156	1.3069032E-2	[lyrActiveRecips].[MemberID]	NULL	PLAN_ROW	-1	1.0
                                                    |--Bookmark Lookup(BOOKMARK:([Bmk1004]), OBJECT:([lmdb].[dbo].[outmail_]))	11	29	28	Bookmark Lookup	Bookmark Lookup	BOOKMARK:([Bmk1004]), OBJECT:([lmdb].[dbo].[outmail_])	[outmail_].[Type_]	1.0	3.1249749E-3	0.0000011	156	1.3068552E-2	[lyrActiveRecips].[MemberID], [outmail_].[Type_]	NULL	PLAN_ROW	-1	1.0
                                                         |--Nested Loops(Inner Join, OUTER REFERENCES:([lyrActiveRecips].[MailingID]))	11	30	29	Nested Loops	Inner Join	OUTER REFERENCES:([lyrActiveRecips].[MailingID])	NULL	1.0	0.0	0.00001254	138	9.9424766E-3	[lyrActiveRecips].[MemberID], [Bmk1004]	NULL	PLAN_ROW	-1	1.0
                                                              |--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([lmdb].[dbo].[lyrActiveRecips]))	11	31	30	Bookmark Lookup	Bookmark Lookup	BOOKMARK:([Bmk1002]), OBJECT:([lmdb].[dbo].[lyrActiveRecips])	[lyrActiveRecips].[MemberID], [lyrActiveRecips].[MailingID]	1.0	3.1249749E-3	0.0000011	53	6.4091529E-3	[lyrActiveRecips].[MemberID], [lyrActiveRecips].[MailingID]	NULL	PLAN_ROW	-1	1.0
                                                              |    |--Index Seek(OBJECT:([lmdb].[dbo].[lyrActiveRecips].[jacktest_lar_date_ix]), SEEK:([lyrActiveRecips].[NextAttempt] > 'Jan 20 2004 12:00AM') ORDERED FORWARD)	11	32	31	Index Seek	Index Seek	OBJECT:([lmdb].[dbo].[lyrActiveRecips].[jacktest_lar_date_ix]), SEEK:([lyrActiveRecips].[NextAttempt] > 'Jan 20 2004 12:00AM') ORDERED FORWARD	[Bmk1002]	1.0	3.2034749E-3	7.9603E-5	40	3.2830781E-3	[Bmk1002]	NULL	PLAN_ROW	-1	1.0
                                                              |--Index Seek(OBJECT:([lmdb].[dbo].[outmail_].[PK_outmail_]), SEEK:([outmail_].[MessageID_]=[lyrActiveRecips].[MailingID]) ORDERED FORWARD)	11	33	30	Index Seek	Index Seek	OBJECT:([lmdb].[dbo].[outmail_].[PK_outmail_]), SEEK:([outmail_].[MessageID_]=[lyrActiveRecips].[MailingID]) ORDERED FORWARD	[Bmk1004]	1.0	3.2034749E-3	7.9603E-5	93	3.520784E-3	[Bmk1004]	NULL	PLAN_ROW	-1	3.0

I still can't make heads or tails of it though.

-- 
greg




^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: query optimization question
  2004-01-29 01:05 query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 02:04 ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 17:23   ` Re: query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 18:05     ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 19:04       ` Re: query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 19:31         ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-01-30 07:19           ` Dennis Bjorklund <db@zigo.dhs.org>
  2004-01-30 14:51             ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 14+ messages in thread

From: Dennis Bjorklund @ 2004-01-30 07:19 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jack Coates <jack@lyris.com>; pgsql-performance

On Thu, 29 Jan 2004, Tom Lane wrote:

> > jackdb-# GROUP BY memberid_ HAVING ( 
> 
> Um, that's not what I had in mind at all.  Does GROUP BY actually do
> anything at all here?  (You didn't answer me as to whether memberid_
> is a unique identifier or not, but if it is, this GROUP BY is just an
> expensive no-op.)



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: query optimization question
  2004-01-29 01:05 query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 02:04 ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 17:23   ` Re: query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 18:05     ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-29 19:04       ` Re: query optimization question Jack Coates <jack@lyris.com>
  2004-01-29 19:31         ` Re: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  2004-01-30 07:19           ` Re: query optimization question Dennis Bjorklund <db@zigo.dhs.org>
@ 2004-01-30 14:51             ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 14+ messages in thread

From: Tom Lane @ 2004-01-30 14:51 UTC (permalink / raw)
  To: Dennis Bjorklund <db@zigo.dhs.org>; +Cc: Jack Coates <jack@lyris.com>; pgsql-performance

Dennis Bjorklund <db@zigo.dhs.org> writes:
> Getting rid of the group by would not give that kind of speedup?

No.  Getting rid of the per-row subqueries (or at least finding a way to
make 'em a lot cheaper) is the only way to make any meaningful change.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 14+ messages in thread


end of thread, other threads:[~2004-01-30 17:13 UTC | newest]

Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-29 01:05 query optimization question Jack Coates <jack@lyris.com>
2004-01-29 02:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-01-29 17:23   ` Jack Coates <jack@lyris.com>
2004-01-29 18:05     ` Tom Lane <tgl@sss.pgh.pa.us>
2004-01-29 19:04       ` Jack Coates <jack@lyris.com>
2004-01-29 19:31         ` Tom Lane <tgl@sss.pgh.pa.us>
2004-01-29 21:23           ` Jack Coates <jack@lyris.com>
2004-01-29 22:01             ` Tom Lane <tgl@sss.pgh.pa.us>
2004-01-29 22:26               ` Jack Coates <jack@lyris.com>
2004-01-30 07:23                 ` Dennis Bjorklund <db@zigo.dhs.org>
2004-01-30 17:13                   ` Jack Coates <jack@lyris.com>
2004-01-30 10:00               ` Greg Stark <gsstark@mit.edu>
2004-01-30 07:19           ` Dennis Bjorklund <db@zigo.dhs.org>
2004-01-30 14:51             ` Tom Lane <tgl@sss.pgh.pa.us>

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