On Sat, May 27, 2017 at 1:40 AM, 梁海安（Killua Leung） <LIANGHAIAN001@xxxxxxxxxxxxx> wrote:
a.mapping_code = b.mapping_code AND a.channel=b.channel
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:
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
