table size growing out of control - Mailing list pgsql-general

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