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

On Sat, May 27, 2017 at 1:40 AM, 梁海安（Killua Leung） <
LIANGHAIAN001(at)pingan(dot)com(dot)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