Received: from mta2.snfc21.pbi.net (mta2.snfc21.pbi.net [206.13.28.123]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA86999 for ; Mon, 25 Oct 1999 11:39:13 -0400 (EDT) (envelope-from postgres@weblynk.com) Received: from pinto.usedcars.com ([206.170.239.155]) by mta2.snfc21.pbi.net (Sun Internet Mail Server sims.3.5.1999.09.16.21.57.p8) with SMTP id <0FK60000B0SOC9@mta2.snfc21.pbi.net> for pgsql-sql@postgreSQL.org; Mon, 25 Oct 1999 08:38:49 -0700 (PDT) Date: Mon, 25 Oct 1999 08:36:30 -0700 From: Rich Ryan Subject: Re: [SQL] Vacuum takes more than 1 hr To: "pgsql-sql@postgresql.org" Message-id: <002c01bf1efe$b5e874a0$9befaace@usedcars.com> MIME-version: 1.0 X-Mailer: Microsoft Outlook Express 5.00.2314.1300 Content-type: text/plain; charset="iso-8859-1" Content-transfer-encoding: 7bit X-MSMail-Priority: Normal X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300 References: <17667.940862730@sss.pgh.pa.us> X-Priority: 3 Instead of using CVS, will ftp'ing the postgresql.snapshot.tar.gz file do? I looked around for an explanation of what this file is, but didn't find any. ----- Original Message ----- From: Tom Lane To: Postgres Cc: pgsql-sql@postgresql.org Sent: Monday, October 25, 1999 7:45 AM Subject: Re: [SQL] Vacuum takes more than 1 hr > Postgres 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 > > ************