Subject: Re: (Re)-indexing on updates
From: "Jeffrey W. Baker" <jwbaker@acm.org>
To: Yves Vindevogel <yves.vindevogel@implements.be>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <ba89fec2b148abbc137d9a9b8e2832c7@implements.be>
References: <ba89fec2b148abbc137d9a9b8e2832c7@implements.be>
Content-Type: text/plain
Date: Sun, 21 Aug 2005 12:06:05 -0700
Message-Id: <1124651165.5990.1.camel@noodles>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Sun, 2005-08-21 at 20:32 +0200, Yves Vindevogel wrote:
> 
> 
> ______________________________________________________________________
> 
> Hi,
> 
> Say I have a table with column A, B, C, D
> A has a unique index on it (primary key)
> B and C have a normal index on it
> D has no index
> 
> If I perform a query like    update tbl set D = 'whatever' ;
> that should make no difference on the indexes on the other columns,
> right ?

What postgresql does on update is to make a new record, so there will be
two records in your table and two records in your index.  You would need
to vacuum the table to mark the space for the old record free, and you
would need to reindex the table to shrink the index.

> 
> Or is there some kind of mechanism that does create a sort of new
> record, thus makes the indexes go wild.

Yes.

-jwb