Thread · Horton

vacuum analyze query performance - help me understand

Scot Kreienkamp <scot.kreienkamp@la-z-boy.com> — 2025-08-19T17:37:16Z

Hi list,

I'm struggling to understand why a query is having performance issues related to analyze. Any help in understanding what's going on would be appreciated.

My database servers are 48 core, 512 gigs of memory, backed by 1.2TB SAN, on 9.6 (not my choice, I've been asking for an upgrade since 11 came out) that's having performance issues with one database. I copied it by dump/restore to a development server (same hardware specs) where I could isolate it so nobody else can connect to it except me, so I could guarantee no changes are being made in the data or structure. There are 3 other large (for my environment) databases there though also. This morning at 2am this vacuum command was run via cron on the server:

if pg_isready -q ; then vacuumdb -U postgres --quiet --all --analyze --jobs $(grep -c ^processor /proc/cpuinfo) ; fi

That command should vacuum analyze all tables in all databases, and it was proven that it worked as the last vacuum and analyze dates on the tables in all databases are showing dates from 2am today.

So here's the problem: We ran a (admittedly poorly written) select query 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 same query again, which performed exponentially better. I verified by comparing before and after explains that the query plan did not change between runs of the query, the only changes were stats like cost, rows, width, time, etc. From what I know, stats only get outdated by database activity. With no activity in the database and a recent vacuum analyze, why did running another vacuum analyze make such a difference in performance? Is there any way to prove the vacuum was or wasn't the source of the performance increase? I would be more apt to believe the difference in performance came from something else, like caching from the first query run, or caching from the vacuum?

This was a contractor that gave me these results, and I'm having a hard time believing their conclusion. Any insight would be appreciated.

[cid:lazboy_2024_inc_navy_pms2189_rgb_rev2025_e40a94f2-e344-4a4a-a02c-fc31996e127c.png]

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.

Re: vacuum analyze query performance - help me understand

Tom Lane <tgl@sss.pgh.pa.us> — 2025-08-19T18:19:56Z

Scot Kreienkamp <Scot.Kreienkamp@la-z-boy.com> writes:
> That command should vacuum analyze all tables in all databases, and it was proven that it worked as the last vacuum and analyze dates on the tables 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 query 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 same query again, which performed exponentially better.    I verified by comparing before and after explains that the query plan did not change between runs of the query, the only changes were stats like cost, rows, width, time, 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

RE: vacuum analyze query performance - help me understand

Scot Kreienkamp <scot.kreienkamp@la-z-boy.com> — 2025-08-19T20:09:40Z

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.

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.

Re: vacuum analyze query performance - help me understand

David G. Johnston <david.g.johnston@gmail.com> — 2025-08-19T20:28:35Z

On Tue, Aug 19, 2025 at 10:37 AM Scot Kreienkamp <
Scot.Kreienkamp@la-z-boy.com> wrote:

>
> We ran a (admittedly poorly written) select query 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 same query again, which
> performed exponentially better.


Did/can you observe query buffer usage/performance for the two explain
analyze executions?

David J.

Re: vacuum analyze query performance - help me understand

Greg Sabino Mullane <htamfids@gmail.com> — 2025-08-20T16:26:19Z

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