pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Jim C. Nasby <jim@nasby.net>
To: Graham Davis <gdavis@refractions.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: index growth problem
Date: Wed, 18 Oct 2006 17:51:56 -0500
Message-ID: <20061018225156.GG56874@nasby.net> (raw)
In-Reply-To: <4536AD3C.8090701@refractions.net>
References: <eft782$5a1$1@news.hub.org>
	<b42b73150610030804q2719222du57c63a085e26a5a4@mail.gmail.com>
	<efu8tf$1blk$1@news.hub.org>
	<b42b73150610031221y4955e663ped999a83a810d318@mail.gmail.com>
	<eg0u6e$hbr$1@news.hub.org>
	<b42b73150610041407y3554f311u1329c4f3bdc53999@mail.gmail.com>
	<eg1dai$1tfa$1@news.hub.org>
	<4536A8A3.1050409@refractions.net>
	<20061018223307.GC56874@nasby.net>
	<4536AD3C.8090701@refractions.net>

On Wed, Oct 18, 2006 at 03:39:56PM -0700, Graham Davis wrote:
> So I guess any changes that were made to make VACUUM and FSM include 
> indexes
> does not remove the necessity to reindex (as long as we don't want index 
> sizes to bloat and grow larger than they need be).
> Is that correct?

Not in recent releases, no. Remember that any index on a field that gets
update activity will naturally have some amount of empty space due to
page splits, but this is normal (and actually desireable). So you can't
just compare index size before and after a REINDEX and assume
something's wrong if REINDEX shrinks the index; that gain is artificial.

So long as you are vacuuming frequently enough and keep the free space
map large enough, there shouldn't be any need to REINDEX.
-- 
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)



view thread (5+ messages)  latest in thread

Message-ID: <20061018225156.GG56874@nasby.net>
Permalink:  ../20061018225156.GG56874@nasby.net/
Also on:    postgresql.org/message-id/20061018225156.GG56874@nasby.net

 · 

reply
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: jim@nasby.net, gdavis@refractions.net
  Subject: Re: index growth problem
  In-Reply-To: <20061018225156.GG56874@nasby.net>

* 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