pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Postgres <postgres@weblynk.com>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgreSQL.org>
Subject: Re: [SQL] Vacuum takes more than 1 hr
Date: Mon, 25 Oct 1999 10:45:30 -0400
Message-ID: <17667.940862730@sss.pgh.pa.us> (raw)
In-Reply-To: <001001bf1ed7$01dc9b20$9befaace@usedcars.com>

Postgres <postgres@weblynk.com> writes:
> I have a table with about 30 columns and 200K rows. When I perform a vacuum
> on it (no analyze) it takes usually a little more than 1 hour.

Hmm, doesn't seem all that large.  One question is whether you are all
the way up-to-date on Postgres --- some performance problems in vacuum
were fixed recently.  (The problems were excessive memory usage,
actually, but that could translate to long runtime if the process
started to swap.  Does the backend that's doing the vacuum seem to grow
to a size much larger than it starts at?)  I don't recall whether this
patch is in 6.5.2 or not, but it will be in 6.5.3, or you could pull
the current REL6_5 branch sources from the CVS server.

A performance problem that still remains is that vacuum seems
unreasonably slow at updating indexes.  Some people have found that
dropping and recreating indexes around a vacuum nets out faster than
letting vacuum do it.  You should also ask yourself whether each
index on the table is earning its keep --- each one costs time on
every insert or update, quite aside from vacuum.  Only the indexes
that actually get used for your common queries are likely to be worth
their overhead.

			regards, tom lane



view thread (4+ messages)  latest in thread

Message-ID: <17667.940862730@sss.pgh.pa.us>
Permalink:  ../17667.940862730@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/17667.940862730@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, postgres@weblynk.com
  Subject: Re: [SQL] Vacuum takes more than 1 hr
  In-Reply-To: <17667.940862730@sss.pgh.pa.us>
  References: <001001bf1ed7$01dc9b20$9befaace@usedcars.com> <17667.940862730@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox