amrit@health2.moph.go.th wrote:
> Now I turn hyperthreading off and readjust the conf . I found the bulb query
> that was :
> update one flag of the table [8 million records which I think not too much]
> .When I turned this query off everything went fine.
> I don't know whether update the data is much slower than insert [Postgresql
> 7.3.2] and how could we improve the update method?
UPDATE is expensive. Under a MVCC setup, it's roughtly the equivalent of
DELETE + INSERT new record (ie, old record deprecated, new version of
record. Updating 8 million records would be very I/O intensive and
probably flushes your OS cache so all other queries hit disk versus
superfast memory. And if this operation is run multiple times during the
day, you may end up with a lot of dead tuples in the table which makes
querying it deadly slow.
If it's a dead tuples issue, you probably have to increase your
freespace map and vacuum analyze that specific table more often. If it's
an I/O hit issue, a lazy updating procedure would help if the operation
is not time critical (eg. load the record keys that need updating and
loop through the records with a time delay.)