0

I could not find an answer for this in postgres docs, even though some DBAs in their blogs claim that manual analyze builds whole table stats and autovacuum updates only incremental stats, but confusion is some DBAs say the opposite.

I understand that autovacuum is the scheduled version of vacuum and analyze based on other parameters we are aware of. Now my question is does autovacuum's analyze rebuilds whole table statistics or just updates the existing statistics(incremental)? Also another question related to same topic is, does "default statistics" value applies to autovacuum analyze as well?

Thanks,

Sajith P Shetty

Improve this question

2 Answers 2

Reset to default
0

Autovacuum's analyze does the same thing as manual ANALYZE (of all columns) does. I see nothing in the source code to support that "autovacuum works incremental and manual analyze does full rebuild". "update_attstats" doesn't even look to see who is calling it.

And they handle "default statistics" the same way.

Improve this answer
2
  • Thanks @jjanes, I would recommend you to refer to below link: autoanalyze He says "“Autovacuum also keeps a table’s data distribution statistics up-to-date (it doesn’t rebuild them). When manually run, the ANALYZE command actually rebuilds these statistics instead of updating them.” Do you deny that ?
    – Sajith P Shetty
    Commented Feb 1, 2022 at 14:51
  • @SajithPShetty yes. I see nothing in the source code to support that. "update_attstats" doesn't even look to see who is calling it.
    – jjanes
    Commented Feb 1, 2022 at 18:17
0

There might be some confusion between vacuum, vacuum full and analyze.

  • vacuum is the operation that marks dead rows as free space.
  • analyze is the operation that gather statistics on the data.
  • vacuum full is not a variation of vacuum, even though the name suggests it. That's why I love calling it unbloat or rewrite. It's the operation that rewrite the whole table. AS a consequence, this operation will truly reclaim free space and get rid of bloat.

For more details, please read the documentation here: https://www.postgresql.org/docs/current/routine-vacuuming.html

Improve this answer
2
  • Sorry the question was different. "Autovacuum doesnt rebuild them however keeps a table’s data distribution statistics up-to-date . When manually run, the ANALYZE command actually rebuilds these statistics instead of updating them." I was looking for justification to above statement. Do you vote it?
    – Sajith P Shetty
    Commented Feb 1, 2022 at 14:53
  • There is not difference between gathering statistics, updating statistics and rebuild statistics. It's the same process: - Take a sample of data - Compute statistics on the sample - Store the computed statistics
    – Arkhena
    Commented Feb 28, 2022 at 9:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.