MIME-Version: 1.0
References: <a2cd8084-b93e-4da0-8999-569a6e58ef6b.8f62f83c-ab51-428e-98d1-ab1abb243a49.f9527220-afca-46a5-9ff9-6943b4d51eeb@emailsignatures365.codetwo.com>
 <a2cd8084-b93e-4da0-8999-569a6e58ef6b.b1460fbe-55a2-4060-995f-39f4e2f3c7f9.62e1aa37-6482-4ce2-8283-21c365f4f47c@emailsignatures365.codetwo.com>
 <SJ0PR15MB524590D2BECF3283CDCE39289A30A@SJ0PR15MB5245.namprd15.prod.outlook.com>
 <601313.1755627596@sss.pgh.pa.us> <SJ0PR15MB5245E8979235B81FD116039B9A30A@SJ0PR15MB5245.namprd15.prod.outlook.com>
In-Reply-To: <SJ0PR15MB5245E8979235B81FD116039B9A30A@SJ0PR15MB5245.namprd15.prod.outlook.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Wed, 20 Aug 2025 12:26:19 -0400
Message-ID: <CAKAnmmJKOykz=xwGGTRJX9arvhOLa6rXShv-S_sZ52urDfaXnQ@mail.gmail.com>
Subject: Re: vacuum analyze query performance - help me understand
To: Scot Kreienkamp <Scot.Kreienkamp@la-z-boy.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, 
	"pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f0e1c9063cce7054"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmJKOykz%3DxwGGTRJX9arvhOLa6rXShv-S_sZ52urDfaXnQ%40mail.gmail.com>
Precedence: bulk

--000000000000f0e1c9063cce7054
Content-Type: text/plain; charset="UTF-8"

Moving forward advice:

* Run the query more than once before doing a manual vacuum to rule out
caching.
* Change your flags for the vacuumdb from --quiet to --verbose and we can
see exactly what vacuum has done. Ideally have cron append to a file on disk
* Similarly, set log_autovacuum_min_duration to 0 (which logs all
autovacuum activity).
* As mentioned upthread, use explain (analyze, buffers, settings) for
better output
* Using the pg_buffercache extension can show you exactly what is in shared
buffers (for future debugging)

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

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

<div dir=3D"ltr"><div><br></div><div>Moving forward advice:</div><div><br><=
/div><div>* Run the query more than once before doing a manual vacuum to ru=
le out caching.</div><div>* Change your flags for the vacuumdb=C2=A0from --=
quiet to --verbose and we can see exactly what vacuum has done. Ideally hav=
e cron append to a file on disk</div><div>* Similarly, set log_autovacuum_m=
in_duration to 0 (which logs all autovacuum activity).</div><div>* As menti=
oned upthread, use explain (analyze, buffers, settings) for better output</=
div><div>* Using the pg_buffercache extension can show you exactly what is =
in shared buffers (for future debugging)</div><div><br></div><div dir=3D"lt=
r" class=3D"gmail_signature"><div dir=3D"ltr"><div>Cheers,</div><div>Greg</=
div><div><br></div><div>--</div><div>Crunchy Data - <a href=3D"https://www.=
crunchydata.com" target=3D"_blank">https://www.crunchydata.com</a></div><di=
v>Enterprise Postgres Software Products &amp; Tech Support</div><div><br></=
div></div></div></div>

--000000000000f0e1c9063cce7054--