Re: (Re)-indexing on updates

From: Jeffrey W. Baker
Subject: Re: (Re)-indexing on updates
Date: ,
Msg-id: 1124651165.5990.1.camel@noodles
(view: Whole thread, Raw)
In response to: (Re)-indexing on updates  (Yves Vindevogel)
Responses: Re: (Re)-indexing on updates  (Yves Vindevogel)
List: pgsql-performance

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



pgsql-performance by date:

From: Ron
Date:
Subject: Re: extremly low memory usage
From: Yves Vindevogel
Date:
Subject: Re: (Re)-indexing on updates