Re: query optimization question

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