pg_autovacuum seems to be a neat freak and cleans way too much

[Brian Hirt <bhirt@mobygames.com>]

I've having a strange issue with pg_autovacuum.   I have a table with 
about 4 million rows in 20,000 pages.   autovacuum likes to vacuum 
and/or analyze  it every 45 minutes or so, but it probably doesn't have 
more that a few hundred rows changed every few hours.   when i run 
autovacuum with -d3 it says

[2004-05-18 07:04:26 PM]   table name:     
basement_nightly."public"."search_words4"
[2004-05-18 07:04:26 PM]      relid: 396238832;   relisshared: 0
[2004-05-18 07:04:26 PM]      reltuples: 4;  relpages: 20013
[2004-05-18 07:04:26 PM]      curr_analyze_count:  0; cur_delete_count: 
  0
[2004-05-18 07:04:26 PM]      ins_at_last_analyze: 0; 
del_at_last_vacuum: 0
[2004-05-18 07:04:26 PM]      insert_threshold:    504; 
delete_threshold    1008

reltuples: 4 seems wrong.  I would expect a table with 4m rows and 20k 
pages to have more than 4 tuples.   I think this is why the insert 
threshhold is all messed up -- which is why it gets analyzed way too 
frequently.

this happens with other big tables too.   the autovacuum is from 7.4.2, 
some information is below.


output from vacuum:

basement=# vacuum ANALYZE verbose search_words4;
INFO:  vacuuming "public.search_words4"
INFO:  index "search_words4_data_id" now contains 4069268 row versions 
in 15978 pages
DETAIL:  479 index row versions were removed.
1 index pages have been deleted, 0 are currently reusable.
CPU 0.42s/0.70u sec elapsed 29.48 sec.
INFO:  index "search_words4_pkey" now contains 4069268 row versions in 
17576 pages
DETAIL:  479 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.77s/0.74u sec elapsed 150.19 sec.
INFO:  "search_words4": removed 479 row versions in 6 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "search_words4": found 479 removable, 4069268 nonremovable row 
versions in 19950 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 1.30s/1.61u sec elapsed 179.96 sec.
INFO:  analyzing "public.search_words4"
INFO:  "search_words4": 19950 pages, 3000 rows sampled, 4069800 
estimated total rows
VACUUM
basement=#



here's the frequency
[2004-05-18 12:12:54 PM] Performing: ANALYZE "public"."search_words4"
[2004-05-18 01:59:13 PM] Performing: ANALYZE "public"."search_words4"
[2004-05-18 02:05:36 PM] Performing: ANALYZE "public"."search_words4"
[2004-05-18 02:29:25 PM] Performing: VACUUM ANALYZE 
"public"."search_words4"
[2004-05-18 02:46:09 PM] Performing: ANALYZE "public"."search_words4"
[2004-05-18 03:39:31 PM] Performing: ANALYZE "public"."search_words4"
[2004-05-18 05:20:45 PM] Performing: ANALYZE "public"."search_words4"
[2004-05-18 06:08:03 PM] Performing: VACUUM ANALYZE 
"public"."search_words4"
[2004-05-18 06:18:34 PM] Performing: ANALYZE "public"."search_words4"
[2004-05-18 07:34:27 PM] Performing: ANALYZE "public"."search_words4"
[2004-05-18 07:43:18 PM] Performing: ANALYZE "public"."search_words4"


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend