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.
