Re: UPDATE

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> writes:
> > 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
>