Feedback-ID: ia2694551:Fastmail
Date: Thu, 28 Mar 2024 18:32:26 +0100
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: David Fogarty <dfogarty@icsusa.com>
Cc: pgsql-novice@lists.postgresql.org
Subject: Re: Performance after restart/reboot pre-Analyze
Message-ID: <202403281732.p3cxllpbx7qf@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <6F39634B-20B1-4683-9320-C4D7B68D53CF@icsusa.com>
Archived-At: 
 <https://www.postgresql.org/message-id/202403281732.p3cxllpbx7qf%40alvherre.pgsql>
Precedence: bulk

On 2024-Mar-28, David Fogarty wrote:

> It seems like whenever I restart my database server performance is
> quite bad until an Analyze has been performed. I expect this is due to
> the query planner needing new stats, but that leads me to three
> questions:

How exactly are you restarting the database server?  Statistics
collected by ANALYZE are lost on crashes or when recovery is run, so if
you're just hard-crashing your server, then ANALYZE is indeed necessary.
If that's what's happening, try not doing that -- use fast shutdown (not
immediate) and wait until it's finished before turning power off.

If you do need to crash your server (but why risk your data?), then
maybe vacuumdb --analyze-in-stages would be helpful.

Otherwise, a cold cache as others mentioned is the most likely
explanation.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"Doing what he did amounts to sticking his fingers under the hood of the
implementation; if he gets his fingers burnt, it's his problem."  (Tom Lane)