Date: Wed, 12 Sep 2007 15:40:36 -0400
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Mikko Partio <mpartio@gmail.com>,
	"ruben@rentalia.com" <ruben@rentalia.com>,
	Decibel! <decibel@decibel.org>, db@zigo.dhs.org,
	pgsql-performance@postgresql.org
Subject: Re: [Again] Postgres performance problem
Message-ID: <20070912194036.GE2244@alvh.no-ip.org>
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>
	<dcc563d10709121207q66f09c33ldcd181e4a2876904@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <dcc563d10709121207q66f09c33ldcd181e4a2876904@mail.gmail.com>
User-Agent: Mutt/1.5.16 (2007-06-11)

Scott Marlowe escribió:

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

Hmm, REINDEX does not rewrite tables.  If there are dead tuples, they
will still be there after REINDEX.


> cluster, otoh, rewrites the table into index order.

... excluding dead tuples, and then rewrites all the indexes.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.