Re: query optimization question

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: query optimization question
Дата
Msg-id 10115.1075341889@sss.pgh.pa.us
обсуждение исходный текст
Ответ на query optimization question  (Jack Coates <jack@lyris.com>)
Ответы Re: query optimization question  (Jack Coates <jack@lyris.com>)
Список 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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Jack Coates
Дата:
Сообщение: query optimization question
Следующее
От: Bruno Wolff III
Дата:
Сообщение: Re: [SQL] limit 1 and functional indexes