RE: Keep needing to run manual analyze

-----Original Message-----
From: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
Sent: 07 January 2021 02:06
To: Rob Northcott <Rob(dot)Northcott(at)compilator(dot)com>; pgsql-general(at)postgresql(dot)org
Subject: Re: Keep needing to run manual analyze

On Wed, 2021-01-06 at 17:28 +0000, Rob Northcott wrote:
>> We have an application that uses a Postgres database (currently 9.6). All the autovacuum/analyze settings are just left at the defaults.
>> We’ve had a few instances recently where users have complained of slow
>> performance and running a manual Analyze (not vacuum) on their database has resolved it.
>> Should it be considered normal that this needs to be done manually periodically even when autovacuum is on?
>> It’s not a very heavy application by a lot of people’s standards (it’s
>> just a point-of-sale/stock-control system) but there are a couple of hundred databases on the server, all in fairly constant use through the day.
>> Is it possible that the autovacuum/analyze isn’t running for some
>> reason? If so, is there anything I should look at to make it work
>> better or should I just be looking at scheduling a manual vacuum/analyze periodically on all databases?

>That is not necessary.

>You should figure out what tables need more frequent ANALYZE runs to get good execution plans and tune autoanalyze for these tables, >for example by reducing the scale factor for them.

Thanks - that confirms what the previous poster said, that we should be able to set up autovacuum to do what we need. I'll go away and read up on it. Thanks for the tips.