Hi,
I am working with a 3 Postgresql databases, 1 is configured with autovaccum enabled with the following settings:
vacuum_cost_delay = 200 # 0-1000 milliseconds
vacuum_cost_page_hit = 6 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
vacuum_cost_limit = 100 # 0-10000 credits
===========================================================================
autovacuum = on # enable autovacuum subprocess?
autovacuum_naptime = 30 # time between autovacuum runs, in secs
autovacuum_vacuum_threshold = 400 # min # of tuple updates before vacuum
autovacuum_analyze_threshold = 100 # min # of tuple updates before analyze
autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before vacuum
autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before analyze
#autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for
# autovac, -1 means use
# vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovac, -1 means use
# vacuum_cost_limit
and the other 2 without it enabled (all the setting commented out in postgresql.conf).
All 3 databases hold the same data (roughly 250,000 rows spread over 82 tables), this data was deleted via a java process (within an application server) using the EntityManager (not the quickest or best way, and it will never have to delete this much data usually). After the deletion has taken place all the estimated rows (via pgadmin) are still as if no delete has taken place !! but performing a count on the table reveals the true figure 0. I decided that a vaccum (analyze) might solve the problem of the estimated rows being way off, and on the 2 machines with autovaccum not enabled this analyze takes only a few minutes at most, but on the machine with it enabled it takes roughly 40 - 50 mins (2882336 ms.), i do not know why ?? does the autovaccum slow down a manually invoked vaccum ?
And the analyze does not solve pgadmins problem with the estimated rows (but disconnecting and the reconnecting does :-))
Can anyone offer me some advice on why the vaccum takes so long on the autovaccum machine.
Thanks in advance,
Andy
