Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 4F50F9FB2E6 for ; Wed, 18 Oct 2006 19:52:04 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 31282-07 for ; Wed, 18 Oct 2006 22:52:00 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id E10949FA1E2 for ; Wed, 18 Oct 2006 19:51:59 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id 4CE0956446; Wed, 18 Oct 2006 17:51:58 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Wed, 18 Oct 2006 17:51:56 -0500 Date: Wed, 18 Oct 2006 17:51:56 -0500 From: "Jim C. Nasby" To: Graham Davis Cc: pgsql-performance@postgresql.org Subject: Re: index growth problem Message-ID: <20061018225156.GG56874@nasby.net> References: <4536A8A3.1050409@refractions.net> <20061018223307.GC56874@nasby.net> <4536AD3C.8090701@refractions.net> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <4536AD3C.8090701@refractions.net> X-Operating-System: FreeBSD 6.1-RELEASE-p10 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.13 (2006-08-11) X-Hashcash: 1:20:061018:gdavis@refractions.net::0a9N/j9mBzH8mT37:000000000000000 0000000000000000000000001xKA X-Hashcash: 1:20:061018:pgsql-performance@postgresql.org::0Tr+q/gElQWyq+Jl:00000 00000000000000000000000003SN X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200610/387 X-Sequence-Number: 21516 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)