Re: (Re)-indexing on updates

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