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 | 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. > 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. ANyway... ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
