Skip site navigation (1) Skip section navigation (2)

Re: query optimization question

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jack Coates <jack(at)lyris(dot)com>
Cc: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: query optimization question
Date: 2004-01-29 02:04:49
Message-ID: 10115.1075341889@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Jack Coates <jack(at)lyris(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Bruno Wolff IIIDate: 2004-01-29 12:52:40
Subject: Re: limit 1 and functional indexes
Previous:From: Jack CoatesDate: 2004-01-29 01:05:54
Subject: query optimization question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group