RE: [GENERAL] Benchmarks - Mailing list pgsql-general

On Thu, 6 Jan 2000, Culberson, Philip wrote:

    This is a considerable amount faster.  I never thought about the
indices getting hit here.  Thanks a lot.

# 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@candle.pha.pa.us]
# Sent: Thursday, January 06, 2000 10:14 AM
# To: Dustin Sallings
# Cc: The Hermit Hacker; pgsql-general@hub.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@candle.pha.pa.us            |  (610) 853-3000
#   +  If your life is a hard drive,     |  830 Blythe Avenue
#   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
#
# ************
#
#

--
SA, beyond.com           My girlfriend asked me which one I like better.
pub  1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
|    Key fingerprint =  87 02 57 08 02 D0 DA D6  C8 0F 3E 65 51 98 D8 BE
L_______________________ I hope the answer won't upset her. ____________