RE: [GENERAL] Benchmarks

Maybe vacuum should be changed to automatically drop all indexes, vacuum,
and re-create all indexes and stop trying to rebuild each index.

> -----Original Message-----
> From: Culberson, Philip [SMTP:philip(dot)culberson(at)dat(dot)com]
> Sent: Thursday, January 06, 2000 1:58 PM
> To: 'Bruce Momjian'; Dustin Sallings
> Cc: The Hermit Hacker; pgsql-general(at)hub(dot)org
> Subject: RE: [GENERAL] Benchmarks
>
> In his very insightful post last week, Mike Mascari pointed out that, on
> tables with heavy insert/updates, it was much faster to drop the index,
> vacuum analyze, and then rebuild the index. Maybe in vacuum there is a
> specific inefficiency in what Mike coined "defragment"ing indexes.
>
> [Snip]
>
> 8. Not running VACUUM - PostgreSQL won't use indexes, or won't optimize
> correctly unless the record count and dispersion estimates are up-to-date.
> People have reported problems with running vacuum while under heavy load.
> We
> haven't seen it, but we run vacuum each night at 4:05 a.m. However, if you
> perform a LARGE number of INSERTS/UPDATES, it is better for you to do the
> following:
>
> DROP INDEX index_on_heavilty_used_table;
> VACUUM ANALYZE;
> CREATE INDEX index_on_heavily_used_table;
>
> Because VACUUM will sit there, and, row by row, essentially "defragment"
> your indexes, which can take damn near forever for any number of updates
> or
> deletes greater than, say, 30,000 rows.
>
> [Snip]
>
> -----Original Message-----
> From: Bruce Momjian [mailto:pgman(at)candle(dot)pha(dot)pa(dot)us]
> Sent: Thursday, January 06, 2000 10:14 AM
> To: Dustin Sallings
> Cc: The Hermit Hacker; pgsql-general(at)hub(dot)org
> Subject: Re: [GENERAL] Benchmarks
>
>
> > Untrue, vacuum is *extremely* important for updating statistics.
> > If you have a lot of data in a table, and you have never vacuumed, you
> > might as well not have any indices. It'd be nice if you could seperate
> > the stat update from the storage reclaim. Actually, it'd be nice if you
> > could reuse storage, so that an actual vacuum wouldn't be necessary
> unless
> > you just wanted to free up disk space you might end up using again
> anyway.
> >
> > The vacuum also doesn't seem to be very efficient. In one of my
> > databases, a vacuum could take in excess of 24 hours, while I've written
> a
> > small SQL script that does a select rename and a insert into select from
> > that will do the same job in about ten minutes. This is a database that
> > cannot lock for more than a few minutes.
>
> This is serious. Why would an INSERT / RENAME be so much faster. Are
> we that bad with VACUUM?
>
> --
> Bruce Momjian | http://www.op.net/~candle
> maillist(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
>
> ************
>
> ************