Re: pessimal trivial-update performance

On 2010-07-04 06:11, Tom Lane wrote:
> Robert Haas<robertmhaas@gmail.com>  writes:
>    
>> CREATE OR REPLACE FUNCTION update_tab() RETURNS void AS $$
>> BEGIN
>> 	INSERT INTO tab VALUES (0);
>> 	FOR i IN 1..100000 LOOP
>> 		UPDATE tab SET x = x + 1;
>> 	END LOOP;
>> END
>> $$ LANGUAGE plpgsql;
>>      
> I believe that none of the dead row versions can be vacuumed during this
> test.  So yes, it sucks, but is it representative of real-world cases?
>
>    
The problem can generally be written as "tuples seeing multiple
updates in the same transaction"?

I think that every time PostgreSQL is used with an ORM, there is
a certain amount of multiple updates taking place. I have actually
been reworking clientside to get around multiple updates, since they
popped up in one of my profiling runs. Allthough the time I optimized
away ended being both "roundtrip time" + "update time", but having
the database do half of it transparently, might have been sufficient
to get me to have had a bigger problem elsewhere..

To sum up. Yes I think indeed it is a real-world case.

Jesper

-- 
Jesper