On Fri, 14 Feb 2003, Clarence Gardner wrote:
> On Fri, 14 Feb 2003, Stephan Szabo wrote:
>
> > On Fri, 14 Feb 2003, Clarence Gardner wrote:
> >
> > >
> > > I have a database that was populated about two months ago, and one
> > > particular table has begun causing problems. It's got about 20,000
> > > records, all fixed length of about 1 kbytes. If we do any operation
> > > that involves a sequential scan of that table (e.g., select count(*)),
> > > it now takes about 20 seconds, and according to linux vmstat, reads
> > > 275000 disk blocks (275 mbytes). The database is vacuumed each night.
> >
> > What does vacuum full verbose <table> show? And how big is the actual
> > data file?
> >
>
> The FULL made the difference -- the table now performs like the copy.
> Despite a nightly vacuum analyze, we've never done a vacuum full. The
> docs (http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-vacuum.html)
> almost, but not quite, come out against it....
>
> We're going to do a full vacuum weekly now.
Actually, if that did work and the locking of vacuum full is an issue, you
may just need to raise your free space map settings and see if that helps
your overall growth/performance. Basically, in short, when vacuum sees
empty space it tries to record where that space is, but it only keeps a
fixed amount of information on the empty space, so if you have alot of
pages that end up with a little bit of empty space you can end up having
alot of that empty space go to waste.