Adam Brusselback <adambrusselback@gmail.com> writes:
>> If the problem is "new server won't use hashagg", I'd wonder whether
>> the work_mem setting is the same, or whether maybe you need to bump
>> it up some (the planner's estimate of how big the hashtable would be
>> might have changed a bit).
> I actually was speaking with Stephen Frost in the slack channel, and tested
> both of those theories.
> The work_mem was the same between the two servers (12MB), but he suggested
> I play around with it. I tried 4MB, 20MB, and 128MB. There was no
> difference from 12MB with any of them.
> I have my default_statistics_target set to 300, and ran a VACUUM ANALYZE
> right after the upgrade to 9.6.1. He suggested I lower it, so I put it
> back down to 100, ran a VACUUM ANALYZE, and observed no change in query. I
> also tried going the other way and set it to 1000, VACUUM ANALYZE, and
> again, no difference to query.
Did you pay attention to the estimated number of groups (ie, estimated
output rowcount for the aggregation plan node) while fooling around with
the statistics? How does it compare to reality, and to 9.5's estimate?
There were several different changes in the planner's number-of-distinct-
values estimation code in 9.6, so maybe the the cause of the difference is
somewhere around there.
regards, tom lane