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:Met vriendelijke groeten,
______________________________________________________________________
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
Bien à vous,
Kind regards,
Yves Vindevogel
Implements
Met vriendelijke groeten,
Mail: [EMAIL PROTECTED] - Mobile: +32 (478) 80 82 91
Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
Web: http://www.implements.be
<x-tad-smaller>
First they ignore you. Then they laugh at you. Then they fight you. Then you win.
Mahatma Ghandi.</x-tad-smaller>
Bien à vous,
Kind regards,
Yves Vindevogel
Implements
Mail: [EMAIL PROTECTED] - Mobile: +32 (478) 80 82 91
Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
Web: http://www.implements.be
<x-tad-smaller>
First they ignore you. Then they laugh at you. Then they fight you. Then you win.
Mahatma Ghandi.</x-tad-smaller>
---------------------------(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
