DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=ntTFeQUhCk+mlYNpdhDaKbW75uKNHkfuVgPMKB0NKbSjBxwTajBU4cbVmaYA85zMK0hneysQsTpD7/WudJUuSZ0kWLPzJHNeMf/bfU7NHwlCvCzkJpf7k13Oc3XILGs+V4GofW8t64orXYqTXVBkhckRAcyJbLcEuMIAtZXb3rk=
Message-ID: <dcc563d10709121207q66f09c33ldcd181e4a2876904@mail.gmail.com>
Date: Wed, 12 Sep 2007 14:07:01 -0500
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: "Mikko Partio" <mpartio@gmail.com>
Subject: Re: [Again] Postgres performance problem
Cc: "ruben@rentalia.com" <ruben@rentalia.com>, Decibel! <decibel@decibel.org>,
	db@zigo.dhs.org, pgsql-performance@postgresql.org
In-Reply-To: <2ca799770709121156l7ddbb20agf8086e8bfcbc881a@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <46E636CB.8080008@rentalia.com>
	<43393.192.121.104.48.1189495883.squirrel@zigo.dhs.org>
	<46E64891.9080005@rentalia.com> <20070911225242.GG38801@decibel.org>
	<46E79865.906@rentalia.com>
	<dcc563d10709121128o1e5883a9n96b1f9ed74901be9@mail.gmail.com>
	<2ca799770709121156l7ddbb20agf8086e8bfcbc881a@mail.gmail.com>

On 9/12/07, Mikko Partio <mpartio@gmail.com> wrote:
>
>
> On 9/12/07, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> > On 9/12/07, ruben@rentalia.com <ruben@rentalia.com> wrote:
>
> > > > Try a REINDEX. VACUUM FULL is especially hard on the indexes, and it's
> > > > easy for them to seriously bloat.
> > >
> > > Reindex is  done everyday after VACUUM FULL VERBOSE ANALYZE. I save also
> > > the output averyday and save it into a log, and I can check that it is
> > > done properly.
> >
> > Then you're vacuum full is wasted.  A reindex accomplishes the same
> > thing, plus shrinks indexes (vacuum full can bloat indexes).
>
> Aren't you mixing up REINDEX and CLUSTER?

I don't think so.  reindex (which runs on tables and indexes, so the
name is a bit confusing, I admit) basically was originally a "repair"
operation that rewrote the whole relation and wasn't completely
transaction safe (way back, 7.2 days or so I think).  Due to the
issues with vacuum full bloating indexes, and being slowly replaced by
regular vacuum, reindex received some attention to make it transaction
/ crash safe and has kind of take the place of vacuum full in terms of
"how to fix bloated objects".

cluster, otoh, rewrites the table into index order.

Either one does what a vacuum full did / does, but generally does it better.