Keep needing to run manual analyze

From: Rob Northcott <Rob(dot)Northcott(at)compilator(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Keep needing to run manual analyze
Date: 2021-01-06 17:28:57
Message-ID: AM8PR08MB5843DB9B35A9BCE8DC93E3479BD00@AM8PR08MB5843.eurprd08.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

I've had a search around but there seem to be lots of differing opinions on what needs to be done manually and what should be handled by the autovacuum.

Any hints most welcome...

Med vänlig hälsning / Best Regards

Rob Northcott
Software Developer (UK Office, formerly TEAM Systems)

Phone +44 1752 712052

Compilator AB
Södergatan 22
SE-211 34 Malmö
Sweden
www.compilator.com<http://www.compilator.com/>

[Asset 2(at)2x]<http://www.compilator.com/>

THIS COMMUNICATION MAY CONTAIN CONFIDENTIAL AND/OR OTHERWISE PROPRIETARY MATERIAL AND IS THUS FOR USE ONLY BY THE INTENDED RECIPIENT. IF YOU RECEIVED THIS IN ERROR, PLEASE CONTACT THE SENDER AND DELETE THE E-MAIL AND ITS ATTACHMENTS FROM ALL COMPUTERS.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Lewis 2021-01-06 18:11:09 Re: Keep needing to run manual analyze
Previous Message Andrus 2021-01-06 17:15:24 Error messages on duplicate schema names