Thanks to all replies.
The main thing is that there was no index on the said column when I am updating and there are about 40+ other columns mostly of integer and smallint types. Also in MySQL I am using InnoDB tables. For both there is a single transaction when working, No other user is connected. The major difference is that when there are indices on other columns than is being updated, it takes more time. I think the reason behind this is that MVCC. As all rows are rewritten(newly inserted with changed column value), the indices must be updated accordingly and this may take more time?
CPKulkarni
On Thu, Feb 19, 2009 at 9:36 PM, Tom Lane
<tgl@sss.pgh.pa.us> wrote:
> Tom Lane wrote:
>> This is not correct; PG *never* overwrites an existing record (at least
>> not in any user-accessible code paths).
> That's what I always thought, but I encountered some odd behaviour while
> trying to generate table bloat that made me think otherwise. I generated
> a large table full of dummy data then repeatedly UPDATEd it. To my
> surprise, though, it never grew beyond the size it had at creation time
> ... if the transaction running the UPDATE was the only one active.
> If there were other transactions active too, the table grew as I'd expect.
> Is there another explanation for this that I've missed?
In 8.3 that's not unexpected: once you have two entries in a HOT chain
then a later update can reclaim the dead one and re-use its space.
(HOT can do that without any intervening VACUUM because only within-page
changes are needed.) However, that only works when the older one is in
fact dead to all observers; otherwise it has to be kept around, so the
update chain grows.
regards, tom lane