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.
>
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...
