From: Yves Vindevogel <yves.vindevogel@implements.be>
To: pgsql-performance@postgresql.org
Subject: Fwd: (Re)-indexing on updates
Date: Sun, 21 Aug 2005 21:36:03 +0200
Message-ID: <7d4e3af17fe3d48a741969cea5f462e3@implements.be> (raw)
I always forget that this goes to the writer itself and not to the
group.
>
>
> Ok, this is a major setback in some of my procedures.
> From time to time, I must update one field in about 10% of the records.
> So this will take time.
>
> How can I work around that ?
>
> Some personal opinions ...
> 1) Drop indexes, run update, create indexes, vacuum
> 2) Move the field to another table and use joins ? I could delete the
> records when needed and add them again
>
>
> This mechanism, of inserting a new record and marking the old one, is
> that data kept somewhere where I can "see" it ?
> I need for one app a trace of all my changes in the database. I have
> a set of triggers to do that for the moment on each table.
> Could I use that mechanism somehow to avoid my triggers ?
> Any documentation on that mechanism (hacker stuff like what tables are
> used) ?
> Any good books on stuff like this ? I love to read and know how the
> inside mechanics work.
>
> Tnx
>
>
>
> On 21 Aug 2005, at 21:06, Jeffrey W. Baker wrote:
>
>> 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
>>
>>
>>
> Met vriendelijke groeten,
> Bien à vous,
> Kind regards,
>
> Yves Vindevogel
> Implements
>
>
>
> Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91
>
> Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
>
> Web: http://www.implements.be
>
> First they ignore you. Then they laugh at you. Then they fight you.
> Then you win.
> Mahatma Ghandi.
>
Met vriendelijke groeten,
Bien à vous,
Kind regards,
Yves Vindevogel
Implements
Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91
Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
Web: http://www.implements.be
First they ignore you. Then they laugh at you. Then they fight you.
Then you win.
Mahatma Ghandi.
Attachments:Pasted Graphic 2.tiff (image/tiff, 5.6K) downloadview imagePasted Graphic 2.tiff (image/tiff, 5.6K) downloadview image
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: yves.vindevogel@implements.be
Subject: Re: Fwd: (Re)-indexing on updates
In-Reply-To: <7d4e3af17fe3d48a741969cea5f462e3@implements.be>
* 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