Hello all,
For several months,the UPDATE performance on a particuarly busy table has been steadily decreasing.(Postgres 7.2, SunOS 5.8) It is not an extraordinarily large table, but recieves lots of inserts, updates, and deletes.
After looking through the mailing lists and documentation, I think I have traced the problem back to the relpages entry in pg_class for the table. If I understand it correctly, the use of EXPLAIN will give you an approximate cost for a particular query which is based on 1*relpages + .01*tuples. Using explain on the table in question led me investigate the contents of pg_class.
Before reindexing the table or vacuuming the db, the contents of pg_class revealed:
relpages | reltuples | relname
-----------+-----------+-------------------------------
64601 | 22053 | banner_impression
3745 | 22053 | banner_impression_archived
1 | 1 | banner_impression_id_sequence
4023 | 22053 | banner_impression_pkey
(4 rows)
After deleting unecessary tuples, reindexing, and vacuuming, pg_class gives the following:
relpages | reltuples | relname
-----------+-----------+-------------------------------
64601 | 181 | banner_impression
71 | 181 | banner_impression_archived
1 | 1 | banner_impression_id_sequence
71 | 181 | banner_impression_pkey
(4 rows)
Obviously, the reltuples column was updated after the delete, and the reindex helped recover space on the indexes, but the relpages entry on the main table didn't change. Since relpages has the most weight in the query plan, this would seem to explain why performance continues to degrade
Does anyone know what may be causing this and if there is a way to force the relpages column to be recalculated?
Thanks for any help
Adam