X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id A5280537AB
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Thu, 21 Apr 2005 16:12:22 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 73981-01
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Thu, 21 Apr 2005 19:12:11 +0000 (GMT)
Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.204])
	by svr1.postgresql.org (Postfix) with ESMTP id 62DA553709
	for <pgsql-performance@postgresql.org>;
	Thu, 21 Apr 2005 16:12:09 -0300 (ADT)
Received: by wproxy.gmail.com with SMTP id 68so618988wri
	for <pgsql-performance@postgresql.org>;
	Thu, 21 Apr 2005 12:12:10 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=rRTRP451t0c77U7QOIU3Y49WHSbNKFlG77zU35tgR2ctEnJRblPkD7SHtML8gLQRNEBEMyUQps78E06WMeduprN9UcXQZLplTIBwejqa50FT1PerTRp5fFajYXcbhg62Ov/0AX4pfvr2lhj7eTz5B5PrzRQp5XSi9y4j8IBhdHI=
Received: by 10.54.5.61 with SMTP id 61mr25242wre;
	Thu, 21 Apr 2005 12:12:09 -0700 (PDT)
Received: by 10.54.86.16 with HTTP; Thu, 21 Apr 2005 12:12:09 -0700 (PDT)
Message-ID: <33c6269f0504211212b0163ac@mail.gmail.com>
Date: Thu, 21 Apr 2005 15:12:09 -0400
From: Alex Turner <armtuk@gmail.com>
Reply-To: Alex Turner <armtuk@gmail.com>
To: Bill Chandler <billybobc1210@yahoo.com>
Subject: Re: Index bloat problem?
Cc: Josh Berkus <josh@agliodbs.com>,
	pgsql-perform <pgsql-performance@postgresql.org>
In-Reply-To: <20050421173855.76286.qmail@web51410.mail.yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <20050421173855.76286.qmail@web51410.mail.yahoo.com>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.278 tagged_above=0 required=5 tests=AWL,
	DNS_FROM_RFC_ABUSE, RCVD_BY_IP
X-Spam-Level: 
X-Archive-Number: 200504/588
X-Sequence-Number: 11985

Same thing happens in Oracle

ALTER INDEX <blah> rebuild

To force a rebuild.  It will mark the free blocks as 'free' below the
PCTFREE value for the tablespace.

Basically If you build an index with 9999 entries.  and each entry is
1/4 of a block, the database will write 2500 blocks to the disk.  If
you delete a random 75% of the index values, you will now have 2500
blocks that have 75% free space.  The database will reuse that free
space in those blocks as you insert new values, but until then, you
still have 2500 blocks worth of data on a disk, that is only 25% full.
 Rebuilding the index forces the system to physically re-allocate all
that data space, and now you have just 2499 entries, that use 625
blocks.

I'm not sure that 'blocks' is the correct term in postgres, it's
segments in Oracle, but the concept remains the same.

Alex Turner
netEconomist

On 4/21/05, Bill Chandler <billybobc1210@yahoo.com> wrote:
>=20
> --- Josh Berkus <josh@agliodbs.com> wrote:
> > Bill,
> >
> > > What about if an out-of-the-ordinary number of
> > rows
> > > were deleted (say 75% of rows in the table, as
> > opposed
> > > to normal 5%) followed by a 'VACUUM ANALYZE'?
> > Could
> > > things get out of whack because of that situation?
> >
> > Yes.  You'd want to run REINDEX after and event like
> > that.  As you should now.
> >
> > --
> > Josh Berkus
> > Aglio Database Solutions
> > San Francisco
> >
>=20
> Thank you.  Though I must say, that is very
> discouraging.  REINDEX is a costly operation, timewise
> and due to the fact that it locks out other processes
> from proceeding.  Updates are constantly coming in and
> queries are occurring continuously.  A REINDEX could
> potentially bring the whole thing to a halt.
>=20
> Honestly, this seems like an inordinate amount of
> babysitting for a production application.  I'm not
> sure if the client will be willing to accept it.
>=20
> Admittedly my knowledge of the inner workings of an
> RDBMS is limited, but could somebody explain to me why
> this would be so?  If you delete a bunch of rows why
> doesn't the index get updated at the same time?  Is
> this a common issue among all RDBMSs or is it
> something that is PostgreSQL specific?  Is there any
> way around it?
>=20
> thanks,
>=20
> Bill
>=20
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>=20
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>=20
>                http://archives.postgresql.org
>