From: Scott Marlowe <scott.marlowe@gmail.com>
To: Mikko Partio <mpartio@gmail.com>
Cc: ruben@rentalia.com <ruben@rentalia.com>
Cc: Decibel! <decibel@decibel.org>
Cc: db@zigo.dhs.org
Cc: pgsql-performance@postgresql.org
Subject: Re: [Again] Postgres performance problem
Date: Wed, 12 Sep 2007 14:07:01 -0500
Message-ID: <dcc563d10709121207q66f09c33ldcd181e4a2876904@mail.gmail.com> (raw)
In-Reply-To: <2ca799770709121156l7ddbb20agf8086e8bfcbc881a@mail.gmail.com>
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.
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-performance@postgresql.org
Cc: scott.marlowe@gmail.com, mpartio@gmail.com, ruben@rentalia.com, decibel@decibel.org, db@zigo.dhs.org
Subject: Re: [Again] Postgres performance problem
In-Reply-To: <dcc563d10709121207q66f09c33ldcd181e4a2876904@mail.gmail.com>
* 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