public inbox for [email protected]
help / color / mirror / Atom feedFrom: Scot Kreienkamp <[email protected]>
To: Tom Lane <[email protected]>
Cc: pgsql-generallists.postgresql.org <[email protected]>
Subject: RE: vacuum analyze query performance - help me understand
Date: Tue, 19 Aug 2025 20:09:40 +0000
Message-ID: <SJ0PR15MB5245E8979235B81FD116039B9A30A@SJ0PR15MB5245.namprd15.prod.outlook.com> (raw)
In-Reply-To: <[email protected]>
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>
<[email protected]>
Hi Tom,
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.
[Scot Kreienkamp]
I thought dead rows were excluded from backup and the resulting restore? All the janitorial was already done a few hours before by a vacuum analyze, so with no activity there shouldn't have been any to do. That's why I'm still looking for answers.
Turning off autovacuum is an anti-pattern.
[Scot Kreienkamp]
Agreed, that's why it's not disabled. I'm out of date, not clueless. The only time I turn off autovac is during full database restores. Last I knew running a periodic reindex and a daily vac/analyze even with autovac enabled was considered best practice. Is that no longer the case?
(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?)
[Scot Kreienkamp]
It is the final release. I guarantee I'm more irritated that we're still on this version than anyone else on earth. They've been warned, many times and loudly, for much longer than 4 years. All I can do is keep reminding and warning of the consequences, like not being able to get help with problems when they inevitably arise. In the meantime, I still have to support it like a number of people's livelihoods (including mine) depends on it. Because they do.
Scot Kreienkamp | Applications Infrastructure Architect | La-Z-Boy Corporate
One La-Z-Boy Drive | Monroe, Michigan 48162 | • (734) 384-6403 | | • 1-734-915-1444 | Email: [email protected]
This message is intended only for the individual or entity to which it is addressed. It may contain privileged, confidential information which is exempt from disclosure under applicable laws. If you are not the intended recipient, you are strictly prohibited from disseminating or distributing this information (other than to the intended recipient) or copying this information. If you have received this communication in error, please notify us immediately by e-mail or by telephone at the above number. Thank you.
Attachments:
[application/ms-tnef] winmail.dat (22.3K, 2-winmail.dat)
download
view thread (3+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: RE: vacuum analyze query performance - help me understand
In-Reply-To: <SJ0PR15MB5245E8979235B81FD116039B9A30A@SJ0PR15MB5245.namprd15.prod.outlook.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox