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

Stephen Harris <lists@spuddy.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