From: Jeffrey W. Baker <jwbaker@acm.org>
To: Yves Vindevogel <yves.vindevogel@implements.be>
Cc: pgsql-performance@postgresql.org
Subject: Re: (Re)-indexing on updates
Date: Sun, 21 Aug 2005 12:06:05 -0700
Message-ID: <1124651165.5990.1.camel@noodles> (raw)
In-Reply-To: <ba89fec2b148abbc137d9a9b8e2832c7@implements.be>
References: <ba89fec2b148abbc137d9a9b8e2832c7@implements.be>
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
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: jwbaker@acm.org, yves.vindevogel@implements.be
Subject: Re: (Re)-indexing on updates
In-Reply-To: <1124651165.5990.1.camel@noodles>
* 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