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

On Wed, Mar 28, 2007 at 12:10:27PM -0400, Tom Lane wrote:
> 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?

No; each row also includes the host ID so each row is unique.

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

No; the subversion logs show it was added to UAT in July 2006 and we
pushed it to production soon after. When we did the push we switched
between the servers and so rebuilt the database (initdb and imported
a backup). We do it this way on major releases so we can always switch
back to the previous server if there are problems with the new code :-)

Sometimes the vacuum code doesn't run if earlier code hangs; we've
sometimes gone 3 or 4 days without a vacuum, but vacuum is generally
consistent.

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

Interesting; maybe we'll schedule one for the next minor code push.

Thanks!

--

rgds
Stephen