> Excerpts from kuopo's message of jue nov 18 04:10:36 -0300 2010:
>> However, when I analyze the table A, the autovacuum or vacuum on the
>> table B cannot find any removable row version (the number of
>> nonremoveable row versions and pages keeps increasing). After the
>> analysis finishes, the search operations on the table B is still
>> inefficient. If I call full vacuum right now, then I can have quick
>> response time of the search operations on the table B again.
Hi, I don't know how to fix the long VACUUM/ANALYZE, but have you tried to
minimize the growth using HOT?
HOT means that if you update only columns that are not indexed, and if the
update can fit into the same page (into an update chain), this would not
create a dead row.
Are there any indexes on the small table? How large is it? You've
mentioned there are about 2049 rows - that might be just a few pages so
the indexes would not be very efficient anyway.
Try to remove the indexes, and maybe create the table with a smaller
fillfactor (so that there is more space for the updates).
That should be much more efficient and the table should not grow.
You can see if HOT works through pg_stat_all_tables view (columns
n_tup_upd and n_tup_hot_upd).
regards
Tomas