On Nov 20, 2007, at 6:14 PM, Ow Mun Heng wrote:
On Mon, 2007-11-19 at 08:24 -0500, Bill Moran wrote:In response to Ow Mun Heng <Ow.Mun.Heng@xxxxxxx>:Even with the regular vacuuming and even a vacuum full ( on my test DB)I still see that perhaps something is wrong (from the below) (I got this gem from the mailling list archives) hmxmms=> SELECT c.relname, c.reltuples::bigint as rowcnt, pg_stat_get_tuples_inserted(c.oid) AS inserted, pg_stat_get_tuples_updated(c.oid) AS updated, pg_stat_get_tuples_deleted(c.oid) AS deleted FROM pg_class c WHERE c.relkind = 'r'::"char" GROUP BY c.oid, c.relname, c.reltuples HAVING pg_stat_get_tuples_updated(c.oid) + pg_stat_get_tuples_deleted(c.oid) > 1000 ORDER BY pg_stat_get_tuples_updated(c.oid) + pg_stat_get_tuples_deleted(c.oid) DESC; relname | rowcnt | inserted | updated | deleted -----------------------+----------+----------+---------+----------tst_r | 11971691 | 0 | 0 | 22390528 <--pg_statistic | 1465 | 280 | 7716 | 153 dr_ns | 2305571 | 1959 | 0 | 1922 pg_attribute | 3787 | 1403 | 184 | 1292No matter how many times I vacuum/full the deleted number still doesn'tgo down.Are you sure you're interpreting that number correctly? I took it to mean a counter of the number of delete operations since server start.
Actually, it's not on server start; it's on stats reset. Which can happen at server start depending on your config.
You are right. This is definitely a snafu in my interpretation. After I restarted PG on the laptop, the numbers went away. So, then I'm confusedas to why the above "gem" was provided as a means to see which tables needs more vacumming.
By itself it doesn't help; you need to track how many rows have been updated or deleted since the last time you vacuumed. That, along with the rowcount, will give you an idea of how much of the table is dead space.
-- Decibel!, aka Jim C. Nasby, Database Architect decibel@xxxxxxxxxxx Give your computer some brain candy! www.distributed.net Team #1828
<<attachment: smime.p7s>>
