Re: 7.3.4 on Linux: UPDATE .. foo=foo+1 degrades massivly over time

I hope I understand your question...

All the old tuples that were current before your updates are still in the
heap. The executer has to do the equivelent of 'where
tuple_visible_to_current_transaction' on every tuple in the heap. The more
updates you do, the more tuples have to be visited on subsequent update
runs.

This is why vacuum exists, and it's the price we pay for the otherwise
excellent transactional model in PG.

HTH :-)
Glen Parker

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Philipp Buehler
> Sent: Wednesday, April 21, 2004 10:52 AM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] 7.3.4 on Linux: UPDATE .. foo=foo+1 degrades massivly
> over time
>
> While running
> UPDATE banner SET counterhalf=counterhalf+1 WHERE BannerID=50
> several thousand times, the return times degrade (somewhat linear).
> The relation banner has currently *seven* rows and thus it doesnt matter
> (and i checked :>) if counterhalf is indexed, or not.
>
> A following VACCUM brings back return times to 'start' - but I cannot
> run VACUUM any other minute (?). And it exactly vaccums as many tuples
> as I updated.. sure thing:
> INFO: Removed 5000 tuples in 95 pages.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: Pages 95: Changed 1, Empty 0; Tup 7: Vac 5000, Keep 0, UnUsed 3.
> Total CPU 0.01s/0.03u sec elapsed 0.04 sec.
>
> < big snip >