Re: Performance after restart/reboot pre-Analyze

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)