Fwd: (Re)-indexing on updates - Mailing list pgsql-performance

I always forget that this goes to the writer itself and not to the
group.

<excerpt><bold>

</bold>

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:


<excerpt>On Sun, 2005-08-21 at 20:32 +0200, Yves Vindevogel wrote:

<excerpt>


______________________________________________________________________


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 ?

</excerpt>

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.


<excerpt>

Or is there some kind of mechanism that does create a sort of new

record, thus makes the indexes go wild.

</excerpt>

Yes.


-jwb




</excerpt>Met vriendelijke groeten,

Bien à vous,

Kind regards,


<bold>Yves Vindevogel</bold>

<bold>Implements</bold>

<smaller>

</smaller></excerpt>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
>
<excerpt><smaller>


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

<italic><x-tad-smaller>

First they ignore you.  Then they laugh at you.  Then they fight you.
Then you win.

Mahatma Ghandi.</x-tad-smaller></italic></smaller></excerpt><excerpt>


</excerpt>Met vriendelijke groeten,

Bien à vous,

Kind regards,


<bold>Yves Vindevogel</bold>

<bold>Implements</bold>

<smaller>

</smaller>>
>
> 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

<smaller>


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

<italic><x-tad-smaller>

First they ignore you.  Then they laugh at you.  Then they fight you.
Then you win.

Mahatma Ghandi.</x-tad-smaller></italic></smaller>



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.