Re: Slow sequential scans on one DB but not another; fragmentation?

On Wed, Mar 28, 2007 at 11:36:27AM -0400, Tom Lane wrote:
> Stephen Harris <lists(at)spuddy(dot)org> writes:
> > INFO: "sweep_users": found 835831 removable, 972662 nonremovable row versions in 2890304 pages
> > DETAIL: 0 dead row versions cannot be removed yet.
> > There were 112212932 unused item pointers.
>
> Oy, that's one bloated table ... only one live row in every three or so pages.
>
> Probably a CLUSTER is the most effective way of cleaning it up. Once

> you get it down to size, revisit your vacuuming policy, because it
> definitely isn't getting vacuumed often enough.

It's vacuumed every night after the updates. There are minimal (zero,
most days) updates during the day. As I mentioned earlier, nightly we do:

for host in list_of_hosts
delete from sweep_users where hostid=host
for user in users_for_host
insert into sweep_users ....

vacuum analyze sweep_users

(in fact we just do "vacuum verbose analyze" for the whole database).

You recommend a "cluster sweep_users" before the vacuum, then?

Thanks!

--

rgds
Stephen