table size growing out of control

I have a table of about 5000 records that I noticed was taking a very
long time to do simple selects from. I looked at explain analyze and got
the following:

rms=# explain analyze select count(*) from health_exception_test;
NOTICE: QUERY PLAN:

Aggregate (cost=158497.22..158497.22 rows=1 width=0) (actual
time=78087.35..78087.35 rows=1 loops=1)
-> Seq Scan on health_exception_test (cost=0.00..158483.58 rows=5458
width=0) (actual time=78059.74..78082.31 rows=5458 loops=1)
Total runtime: 78087.44 msec

EXPLAIN
rms=# explain analyze select count(*) from health_exception_test;
NOTICE: QUERY PLAN:

Aggregate (cost=158497.22..158497.22 rows=1 width=0) (actual
time=80363.50..80363.50 rows=1 loops=1)
-> Seq Scan on health_exception_test (cost=0.00..158483.58 rows=5458
width=0) (actual time=80335.86..80358.48 rows=5458 loops=1)
Total runtime: 80363.59 msec

EXPLAIN

Since we do nightly vacuuming, I thought that there might be some index
issues so I did the following query to get the size of the table:

rms=# SELECT relname, relkind, relpages, relpages / 128 AS MB FROM
pg_class WHERE relname LIKE 'health_%';
relname | relkind | relpages | mb
-----------------------+---------+----------+------
health_exception_test | r | 158429 | 1237
health_ex_group | i | 20 | 0
(2 rows)

health_ex_group is an index on 3 fields in the table. I have done a
reindex on the table but that doesn't have much effect (which makes
sense given the small index size). I also did a vacuum verbose analyze
and got the following:

rms=# VACUUM VERBOSE ANALYZE health_exception_test;
NOTICE: --Relation health_exception_test--
NOTICE: Index health_ex_group: Pages 20; Tuples 5458: Deleted 0.
CPU 0.00s/0.01u sec elapsed 0.00 sec.
NOTICE: Removed 1397914 tuples in 14402 pages.
CPU 1.67s/2.04u sec elapsed 22.90 sec.
NOTICE: Index health_ex_group: Pages 20; Tuples 5458: Deleted 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Removed 271549 tuples in 2810 pages.
CPU 0.32s/0.37u sec elapsed 2.89 sec.
NOTICE: Pages 158429: Changed 0, Empty 0; Tup 5458: Vac 1669463, Keep
0, UnUsed 13717916.
Total CPU 11.68s/3.44u sec elapsed 116.67 sec.
NOTICE: --Relation pg_toast_9370044--
NOTICE: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Analyzing health_exception_test
VACUUM

and after checking the size of the table was no different. At this point
we did a full drop/reload of the database and the table has now shrunk
to the following:

rms=# SELECT relname, relkind, relpages, relpages / 128 AS MB FROM
pg_class WHERE relname LIKE 'health_%';
relname | relkind | relpages | mb
-----------------------+---------+----------+----
health_ex_group | i | 20 | 0
health_exception_test | r | 57 | 0
(2 rows)

and we now get a much more pleasing:

rms=# explain analyze select count(*) from health_exception_test;
NOTICE: QUERY PLAN:

Aggregate (cost=125.22..125.22 rows=1 width=0) (actual
time=13.15..13.15 rows=1 loops=1)
-> Seq Scan on health_exception_test (cost=0.00..111.58 rows=5458
width=0) (actual time=0.01..8.18 rows=5458 loops=1)
Total runtime: 13.21 msec

EXPLAIN

For the record, we went through this procedure about 2 weeks ago (slow
queries, reindex, vacuum, drop/reload) So I am wondering what might be
causing the table to grow so large. We run a function against the table
about every 5 minutes which updates on average maybe 100 rows and adds
rows at the rate of maybe 1 an hour, but otherwise everything else is
selects. I wouldn't think that continual updates would have such a
adverse effect on table size, and even if so shouldn't vacuum take care
of this?

Robert Treat