Thread: QUERY: autovacuum: VACUUM ANALYZE table versus QUERY: autovacuum:VACUUM table
QUERY: autovacuum: VACUUM ANALYZE table versus QUERY: autovacuum:VACUUM table
From
Jason Ralph
Date:
Hello list, I am trying to find out if the naming convention from autovacuum does what its command line equivalent does, or at leastwhat I think it does. QUERY: autovacuum: VACUUM ANALYZE table versus autovacuum: VACUUM ANALYZE table. I have been getting my autovacuum tuned and I have the settings where I see 2 types of queries running now and doing theirjobs. I know vacuum alone will mark dead tuples ready for use again, and analyze alone will update statistics for thequery planner. 1. QUERY: autovacuum: VACUUM ANALYZE table 2. QUERY: autovacuum: VACUUM table My question is do we need both? Why wouldn't QUERY: autovacuum: VACUUM ANALYZE table perform both, I always thought whenissuing VACUUM ANALYZE table it would do both. Why wouldn't we just want vacuum analyze to run? Or is QUERY: autovacuum: VACUUM ANALYZE table different from the command line and it only analyzes and QUERY: autovacuum:VACUUM table only vacuums which would make sense. Thanks as always and hope this is clear. Jason Ralph This message contains confidential information and is intended only for the individual named. If you are not the named addresseeyou should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if youhave received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteedto be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete,or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contentsof this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copyversion.
Re: QUERY: autovacuum: VACUUM ANALYZE table versus QUERY: autovacuum:VACUUM table
From
Adrian Klaver
Date:
On 11/1/19 7:49 PM, Jason Ralph wrote: > Hello list, > I am trying to find out if the naming convention from autovacuum does what its command line equivalent does, or at leastwhat I think it does. > > QUERY: autovacuum: VACUUM ANALYZE table versus autovacuum: VACUUM ANALYZE table. The above are the same, I'm guessing you are referring to 1) and 2) below. > > I have been getting my autovacuum tuned and I have the settings where I see 2 types of queries running now and doing theirjobs. I know vacuum alone will mark dead tuples ready for use again, and analyze alone will update statistics for thequery planner. > > 1. QUERY: autovacuum: VACUUM ANALYZE table > 2. QUERY: autovacuum: VACUUM table > > My question is do we need both? Why wouldn't QUERY: autovacuum: VACUUM ANALYZE table perform both, I always thought whenissuing VACUUM ANALYZE table it would do both. Why wouldn't we just want vacuum analyze to run? > Or is QUERY: autovacuum: VACUUM ANALYZE table different from the command line and it only analyzes and QUERY: autovacuum:VACUUM table only vacuums which would make sense. See here: https://www.postgresql.org/docs/11/routine-vacuuming.html#AUTOVACUUM "Each worker process will check each table within its database and execute VACUUM and/or ANALYZE as needed" In same section read sections on vacuum and analyze thresholds, which determine the 'and/or' above. The commands are the same. Autovacuum is just a background process that calls the regular VACUUM/ANALYZE commands as needed. > > > Thanks as always and hope this is clear. > > > Jason Ralph > > This message contains confidential information and is intended only for the individual named. If you are not the namedaddressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mailif you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot beguaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete,or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contentsof this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copyversion. > > > -- Adrian Klaver adrian.klaver@aklaver.com
Re: QUERY: autovacuum: VACUUM ANALYZE table versus QUERY: autovacuum: VACUUM table
From
Tom Lane
Date:
Jason Ralph <jralph@affinitysolutions.com> writes:
> I am trying to find out if the naming convention from autovacuum does what its command line equivalent does, or at
leastwhat I think it does.
> QUERY: autovacuum: VACUUM ANALYZE table versus autovacuum: VACUUM ANALYZE table.
> I have been getting my autovacuum tuned and I have the settings where I see 2 types of queries running now and doing
theirjobs. I know vacuum alone will mark dead tuples ready for use again, and analyze alone will update statistics for
thequery planner.
> 1. QUERY: autovacuum: VACUUM ANALYZE table
> 2. QUERY: autovacuum: VACUUM table
> My question is do we need both?
Some autovacuum runs will do only VACUUM on a given table, some will do
only ANALYZE, and some will do both. The decisions about which of
these operations are needed are related but not identical (one's based
on n_dead_tup and the other on n_mod_since_analyze, plus you can set
different threshold parameters to compare those to). The pg_stat_activity
report of what's happening does match the command-line syntax.
regards, tom lane