MIME-Version: 1.0
References: <6F39634B-20B1-4683-9320-C4D7B68D53CF@icsusa.com>
In-Reply-To: <6F39634B-20B1-4683-9320-C4D7B68D53CF@icsusa.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Thu, 28 Mar 2024 08:50:53 -0700
Message-ID: 
 <CAKFQuwZEcn9512o=Zgh_SsWnO0VcYU7-t4SZypJTY=AqtVwk9w@mail.gmail.com>
Subject: Re: Performance after restart/reboot pre-Analyze
To: David Fogarty <dfogarty@icsusa.com>
Cc: pgsql-novice@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000003007110614ba7f2d"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKFQuwZEcn9512o%3DZgh_SsWnO0VcYU7-t4SZypJTY%3DAqtVwk9w%40mail.gmail.com>
Precedence: bulk

--0000000000003007110614ba7f2d
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Thu, Mar 28, 2024 at 8:44=E2=80=AFAM David Fogarty <dfogarty@icsusa.com>=
 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 quer=
y
> 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.

--0000000000003007110614ba7f2d
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div class=3D"gmail_default" style=3D"fon=
t-family:arial,helvetica,sans-serif"><span style=3D"font-family:Arial,Helve=
tica,sans-serif">On Thu, Mar 28, 2024 at 8:44=E2=80=AFAM David Fogarty &lt;=
<a href=3D"mailto:dfogarty@icsusa.com">dfogarty@icsusa.com</a>&gt; wrote:</=
span><br></div></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_=
quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,=
204);padding-left:1ex"><div><div><br></div><div>It seems like whenever I re=
start 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,</d=
iv></div></blockquote><div><br></div><div><div class=3D"gmail_default" styl=
e=3D"font-family:arial,helvetica,sans-serif">Nope, statistics are WAL logge=
d.</div></div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"m=
argin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left=
:1ex"><div><div> but that leads me to three questions:</div><div><br></div>=
<div>1. Is there a way to have Analyze stats/query planner &quot;plans&quot=
; persist through a restart?</div></div></blockquote><div><br></div><div><d=
iv class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif"=
>Plans are session-scoped.</div></div><div class=3D"gmail_default" style=3D=
"font-family:arial,helvetica,sans-serif"><br></div><div class=3D"gmail_defa=
ult" style=3D"font-family:arial,helvetica,sans-serif">What you are observin=
g is the shared buffers being empty.=C2=A0 There is a contrib library, pg_p=
rewarm, designed to help alleviate=C2=A0this problem.</div><div class=3D"gm=
ail_default" style=3D"font-family:arial,helvetica,sans-serif"><br></div><di=
v class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif">=
<a href=3D"https://www.postgresql.org/docs/current/pgprewarm.html">https://=
www.postgresql.org/docs/current/pgprewarm.html</a><br></div><div class=3D"g=
mail_default" style=3D"font-family:arial,helvetica,sans-serif"><br></div><d=
iv class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif"=
>David J.</div><div class=3D"gmail_default" style=3D"font-family:arial,helv=
etica,sans-serif"><br></div></div></div>

--0000000000003007110614ba7f2d--