Re: [PERFORM] Different plan between 9.6 and 9.4 when using "Group by"

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: [PERFORM] Different plan between 9.6 and 9.4 when using "Group by"
Дата
Msg-id CAMkU=1waxrmkJNxwPbC2cEjBFxYSFfdEEL1qSwDe1yC9DnAdJQ@mail.gmail.com
обсуждение исходный текст
Ответ на [PERFORM] Different plan between 9.6 and 9.4 when using "Group by"  (梁海安（Killua Leung）<LIANGHAIAN001@pingan.com.cn>)
Список pgsql-performance
Дерево обсуждения

On Sat, May 27, 2017 at 1:40 AM, 梁海安（Killua Leung） <LIANGHAIAN001@pingan.com.cn> wrote:

Hi team:

       The following SQL is very slow in 9.6.1 for the plan has a “sort” node.



The difference is only a factor of 2.  I wouldn't call it "very" slow.

Your explain plans are unreadable, please try posting them as un-line-wrapped text files, or using something like https://explain.depesz.com/, to share them in a readable way.  (Also, VERBOSE probably isn't doing us much
good here, and makes it much less readable).

Writing your CTEs as inline subqueries might help the planner make some better choices here.  Also, the estimate for CTE n is so bad, I'm guessing that their is a high functional dependency on:

a.mapping_code = b.mapping_code AND a.channel=b.channel

While the planner is assuming they are independent.  You might be able to get better estimates there by doing something like:

a.mapping_code+0 = b.mapping_code+0 AND a.channel=b.channel

(or using ||'' rather than +0 if the types are textual rather than numerical).  But I doubt it would be enough of a difference to change the plan, but it is an easy thing to try.

Cheers,

Jeff


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

Предыдущее
От: phb07
Дата:
Сообщение: Re: [PERFORM] Monitoring tool for Postgres Database
Следующее
От: "Kevin.Hughes@uk.fujitsu.com"
Дата:
Сообщение: Re: [PERFORM] Client Server performance & UDS