I've noticed that my tables are not being auto vacuumed or analyzed
regularly, even though I have very aggressive autovacuum settings.
The stats collector appears to still be running, since I can see a
postgres.exe process with -forkcol. However, I never notice it using
I/O or CPU usage. Also, querying the pg_stat_user_tables view shows no
change in table stats even for tables that change very frequently.
I see error such as these in the log every now and then - not sure if
they are related. These have been discussed at length in other posts
and seems to have something to do with PG holding onto old file handles
(Windows specific):
2007-01-24 06:24:16 ERROR: could not open relation 1663/16404/333779:
Permission denied
2007-01-24 06:24:17 ERROR: could not open relation 1663/16404/333779:
Permission denied
2007-01-24 06:24:18 ERROR: could not open relation 1663/16404/333779:
Permission denied
2007-01-24 06:24:19 ERROR: could not open relation 1663/16404/333779:
Permission denied
I'm running PG 8.2.1 on Windows. Here is some of the output from "show
all":
"autovacuum";"on"
"autovacuum_analyze_scale_factor";"0.02"
"autovacuum_analyze_threshold";"250"
"autovacuum_freeze_max_age";"200000000"
"autovacuum_naptime";"1min"
"autovacuum_vacuum_cost_delay";"-1"
"autovacuum_vacuum_cost_limit";"-1"
"autovacuum_vacuum_scale_factor";"0.08"
"autovacuum_vacuum_threshold";"500"
"stats_block_level";"on"
"stats_command_string";"on"
"stats_reset_on_server_start";"off"
"stats_row_level";"on"
"stats_start_collector";"on"
"vacuum_cost_delay";"20ms"
"vacuum_cost_limit";"200"
"vacuum_cost_page_dirty";"20"
"vacuum_cost_page_hit";"1"
"vacuum_cost_page_miss";"10"
"vacuum_freeze_min_age";"100000000"