Subject: Performance after restart/reboot pre-Analyze

From: David Fogarty <[email-masked]>
Date: Thu, 28 Mar 2024 10:43:37 -0500

Hello,

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:

1. Is there a way to have Analyze stats/query planner "plans" persist through a restart? Like store them somehow after an Analyze and tell Postgres where to pull them from?

2. If not, is there a technical reason/limitation as to why?

3. Are there general configuration settings/optimizations for improving performance after a restart before an Analyze has finished?

If this would be better directed toward a different mailing list, let me know. I appreciate any guidance on this.
Also, if it affects anything, the environments are on AWS RDS, we're currently on 14 and 15 but will be upgrading to 16 very soon. I've only recently been brought into this, so I'm not fully aware of what kinds of queries we mostly handle or how the various tables/views are indexed.

Thanks,
Dave

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 28 Mar 2024 08:50:53 -0700

On Thu, Mar 28, 2024 at 8:44 AM David Fogarty <[email-masked]> 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,
>

Nope, statistics are WAL logged.

> but that leads me to three questions:
>
> 1. Is there a way to have Analyze stats/query planner "plans" persist
> through a restart?
>

Plans are session-scoped.

What you are observing is the shared buffers being empty.  There is a
contrib library, pg_prewarm, designed to help alleviate this problem.

https://www.postgresql.org/docs/current/pgprewarm.html

David J.

---

From: Tom Lane <[email-masked]>
Date: Thu, 28 Mar 2024 11:52:59 -0400

> 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 t…

A simple restart shouldn't invalidate statistics.  I'd lay some small
change on your actual problem being not that, but the need to
repopulate the server's buffers from disk files.  If so, pg_prewarm
might help you.

			regards, tom lane

---

From: Alvaro Herrera <[email-masked]>
Date: Thu, 28 Mar 2024 18:32:26 +0100

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)