Re: query optimization question

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