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

The option with


T1: A B C and T2 A D  (to avoid the updates)

works very well with a simple query


Insert into T2 (A, D)

select A, functionToGetD from T1 left join T2 on T1.A = T2.A

where T2.A is null


The above gives me the new records for those where D was not filled
yet.

Since they are all new records, I have no trouble with the MVCC


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



---------------------------(end of
broadcast)---------------------------

TIP 9: In versions below 8.0, the planner will ignore your desire to

       choose an index scan if your joining column's datatypes do not

       match



</excerpt>Met vriendelijke groeten,

Bien à vous,

Kind regards,


<bold>Yves Vindevogel</bold>

<bold>Implements</bold>

<smaller>

</smaller>The option with

T1: A B C and T2 A D  (to avoid the updates)
works very well with a simple query

Insert into T2 (A, D)
select A, functionToGetD from T1 left join T2 on T1.A = T2.A
where T2.A is null

The above gives me the new records for those where D was not filled yet.
Since they are all new records, I have no trouble with the MVCC

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
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>
>
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.