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

Stephen Harris <lists(at)spuddy(dot)org> writes:
> 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

Hmm ... no overlap between the sets of users for different hosts?
This looks like the worst-case bloat should be 2X (at most one dead
and one live row per user), but the numbers you are reporting ---
particularly the unused-item-pointer count --- show clearly that at
some point there was a bloat factor of more than 100, ie, there had
been at least 100 complete replacements of the table without a vacuum.

Perhaps that vacuum step was only recently added to this script?

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

I wouldn't think you need to do it every night, it's just a one-time
fix.

regards, tom lane