Обсуждение: Relpages not being recovered?
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)
-----------+-----------+-------------------------------
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)
-----------+-----------+-------------------------------
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
"Adam Singer" <asinger@easyplanet.com> writes:
> After deleting unecessary tuples, reindexing, and vacuuming, pg_class gives=
> the following:
Did you VACUUM FULL, or just VACUUM?
> Does anyone know what may be causing this and if there is a way to force th=
> e relpages column to be recalculated?
I can assure you that relpages is correct after a vacuum --- look at the
physical file for the table if you want to confirm it.
The answer you are probably really looking for is some combination of
more-frequent vacuums and increasing your FSM configuration parameters
so that free space in the table is recycled more effectively. Try
searching the list archives for "free space map".
regards, tom lane
Actually, I thought a VACUUM or VACUUM ANALYZE would provide the results I wanted.
After I realized my error, a VACUUM FULL gave me what I needed and problem solved.
Thanks for the help though!
Adam
-----Message d'origine-----
De : Tom Lane [mailto:tgl@sss.pgh.pa.us]
Envoyé : jeudi 5 septembre 2002 18:47
À : Adam Singer
Cc : pgsql-admin@postgresql.org
Objet : Re: [ADMIN] Relpages not being recovered?
"Adam Singer" <asinger@easyplanet.com> writes:
> After deleting unecessary tuples, reindexing, and vacuuming, pg_class gives=
> the following:
Did you VACUUM FULL, or just VACUUM?
> Does anyone know what may be causing this and if there is a way to force th=
> e relpages column to be recalculated?
I can assure you that relpages is correct after a vacuum --- look at the
physical file for the table if you want to confirm it.
The answer you are probably really looking for is some combination of
more-frequent vacuums and increasing your FSM configuration parameters
so that free space in the table is recycled more effectively. Try
searching the list archives for "free space map".
regards, tom lane