Skip site navigation (1) Skip section navigation (2)

Re: [Again] Postgres performance problem

From: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
To: "Mikko Partio" <mpartio(at)gmail(dot)com>
Cc: "ruben(at)rentalia(dot)com" <ruben(at)rentalia(dot)com>, Decibel! <decibel(at)decibel(dot)org>, db(at)zigo(dot)dhs(dot)org, pgsql-performance(at)postgresql(dot)org
Subject: Re: [Again] Postgres performance problem
Date: 2007-09-12 19:07:01
Message-ID: dcc563d10709121207q66f09c33ldcd181e4a2876904@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On 9/12/07, Mikko Partio <mpartio(at)gmail(dot)com> wrote:
>
>
> On 9/12/07, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> > On 9/12/07, ruben(at)rentalia(dot)com <ruben(at)rentalia(dot)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.

In response to

Responses

pgsql-performance by date

Next:From: Frank SchoepDate: 2007-09-12 19:19:50
Subject: Re: [Again] Postgres performance problem
Previous:From: Mikko PartioDate: 2007-09-12 18:56:13
Subject: Re: [Again] Postgres performance problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group