From: Bruce Momjian <maillist@candle.pha.pa.us>
Message-Id: <199910012014.QAA04993@candle.pha.pa.us>
Subject: Re: [SQL] Questions about vacuum analyze
In-Reply-To: <37F50FD3.F269B020@sabre.com> from "Steven M. Wheeler" at "Oct 1,
	1999 02:47:32 pm"
To: "Steven M. Wheeler" <swheeler@sabre.com>
Date: Fri, 1 Oct 1999 16:14:17 -0400 (EDT)
CC: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-sql@hub.org
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

> System: Compaq ProLiant 3000 with 2 300MHz PentPro, 512MB RAM, 32GB hardware
> based stripe-set for the DB area.
> OS: Linux kernel 2.2.12
> DB: version 6.5
> 
> Thanks for the info, thought I'd send a followup regarding my ongoing
> problems with vacuum.
> 
> After the last email, I vacuumed the DB a couple of times, with indexes
> dropped.  The first time it completed in 2-3 hours, the next time was
> appreciably longer.  After this I stopped doing anything except for inserts,
> trying to catch up on the incoming data.  This went on for a little over a
> week until I had inserted several million additional rows (its now up to
> 31M+).  I then dropped the indexes and started vacuum again.
> Start: 09/28/1999@10:06:57
> Finish: 09/30/1999@19:13:14
> 
> 33 hours - WOW!

Yikes, that is a long time.

> 
> This morning, I rebuilt the indexes and tried to do a "select distinct
> statdate from currnt;"  This select statement has been running for several

SELECT DISTINCT	is going to take forever because it has to read all
rows, then sort them to remove the duplicates.  That could take some
time.  DISTINCT doesn't use the indexes, because if it did, it would be
paging in all over the place.  Better to read the table sequentially,
then sort.


-- 
  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