Horton

Re: vacuum analyze query performance - help me understand

Greg Sabino Mullane <htamfids@gmail.com>

View thread View on postgr.es
From: Greg Sabino Mullane <htamfids@gmail.com>
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>
Date: 2025-08-20T16:26:19Z
Lists: pgsql-general 
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