From: Tom Lane <tgl@sss.pgh.pa.us>
To: Adam Brusselback <adambrusselback@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query much slower after upgrade to 9.6.1
Date: Mon, 07 Nov 2016 14:32:14 -0500
Message-ID: <4469.1478547134@sss.pgh.pa.us> (raw)
In-Reply-To: <CAMjNa7ewQTf=MOo0emRPiaYm0J-8beqT6OrNjeaQyE7WP+YWNQ@mail.gmail.com>
References: <CAMjNa7cirhpZxx-9HaDcXNFt95w7eoXLwWscTBa2kEFzLQFaZg@mail.gmail.com>
<CAMjNa7cWH=rHs7x96wGfNd5DxzTtLyO-ZxZmia8MXaO+eQ1EzA@mail.gmail.com>
<3809.1478546209@sss.pgh.pa.us>
<CAMjNa7ewQTf=MOo0emRPiaYm0J-8beqT6OrNjeaQyE7WP+YWNQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, adambrusselback@gmail.com
Subject: Re: Query much slower after upgrade to 9.6.1
In-Reply-To: <4469.1478547134@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox