What is the difference between:
vacuum;
and
vacuum analyze;
I read the postgresql manual, but this is still not clear 100% for me.
Do I need to run both, or one of them is sufficient?
asked Sep 27, 2016 at 16:24
Add a comment
|
1 Answer
tl;dr running vacuum analyze is sufficient
Because vacuum analyze is complete superset of vacuum. If you run vacuum analyze you don't need to run vacuum separately. See the discussion on the mailing list archive.
Analyze is an additional maintenance operation next to vacuum. It is supposed to keep the statistics up to date on the table.
Vacuuming isn't the only periodic maintenance your database needs. You also need to analyze the database so that the query planner has table statistics it can use when deciding how to execute a query. Simply put: Make sure you're running ANALYZE frequently enough, preferably via autovacuum. And increase the default_statistics_target (in postgresql.conf) to 100.
More info: https://wiki.postgresql.org/wiki/Introduction_to_VACUUM,_ANALYZE,_EXPLAIN,_and_COUNT
answered Sep 28, 2016 at 13:07
-
2That wiki page seems to be a bit out of date. ("And increase the default_statistics_target (in postgresql.conf) to 100") 100 has been the default since at least version 9.1 Sep 28, 2016 at 14:58
-
And I would said 100 is insane to use today. For my case since PostreSQL 9.6, I was unable to generate good plans using a default_statistics_target < 2000 Aug 21, 2020 at 18:36
-