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@wdc.com>:
>>>
>>> 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 | 1292
>>>
>>> No matter how many times I vacuum/full the deleted number still
>>> doesn't
>>> go 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
> confused
> as 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@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
