From: Tom Lane <tgl@sss.pgh.pa.us>
To: Scot Kreienkamp <Scot.Kreienkamp@la-z-boy.com>
cc: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: Re: vacuum analyze query performance - help me understand
In-reply-to: <SJ0PR15MB524590D2BECF3283CDCE39289A30A@SJ0PR15MB5245.namprd15.prod.outlook.com>
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>
Comments: In-reply-to Scot Kreienkamp <Scot.Kreienkamp@la-z-boy.com>
	message dated "Tue, 19 Aug 2025 17:37:16 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <601312.1755627596.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 19 Aug 2025 14:19:56 -0400
Message-ID: <601313.1755627596@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/601313.1755627596%40sss.pgh.pa.us>
Precedence: bulk

Scot Kreienkamp <Scot.Kreienkamp@la-z-boy.com> writes:
> That command should vacuum analyze all tables in all databases, and it w=
as proven that it worked as the last vacuum and analyze dates on the table=
s in all databases are showing dates from 2am today.

I take it from this that you have autovacuum turned off and you think
a once-a-day manual vacuum run is an adequate replacement?

> So here's the problem:  We ran a (admittedly poorly written) select quer=
y against a subset of tables which performed poorly.  Then we ran a vacuum=
 analyze against just those tables involved in that query, then ran the sa=
me query again, which performed exponentially better.    I verified by com=
paring before and after explains that the query plan did not change betwee=
n runs of the query, the only changes were stats like cost, rows, width, t=
ime, etc.

If the plan didn't change then the stats updates weren't very relevant.
I am guessing that the actual problem was that those tables were
full of dirty rows, and the VACUUM (not the ANALYZE part) got rid
of dead rows, set hint bits on recently-updated rows, and generally
did a lot of janitorial work that makes subsequent table scans faster.

Turning off autovacuum is an anti-pattern.

(Running a PG version that's four years past EOL is also an
anti-pattern, but you knew that.  Should I ask whether it's
at least the final 9.6 minor release?)

			regards, tom lane