From: Bruce Momjian <maillist@candle.pha.pa.us>
To: Steven M. Wheeler <swheeler@sabre.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@hub.org
Subject: Re: [SQL] Questions about vacuum analyze
Date: Fri, 1 Oct 1999 16:14:17 -0400 (EDT)
Message-ID: <199910012014.QAA04993@candle.pha.pa.us> (raw)
In-Reply-To: <37F50FD3.F269B020@sabre.com>
> 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
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: maillist@candle.pha.pa.us, swheeler@sabre.com, tgl@sss.pgh.pa.us, pgsql-sql@hub.org
Subject: Re: [SQL] Questions about vacuum analyze
In-Reply-To: <199910012014.QAA04993@candle.pha.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