Re: Big problem with sql update operation

Tom Lane wrote:
> Michal Szymanski <szymanskim(at)datera(dot)pl> writes:
>
>> CREATE OR REPLACE FUNCTION test()
>> RETURNS void AS
>> $BODY$
>> DECLARE
>> BEGIN
>> FOR v_i IN 1..4000 LOOP
>> UPDATE group_fin_account_tst SET
>> credit = v_i
>> WHERE group_fin_account_tst_id = 1; -- for real procedure I
>> update different rows
>>
>
> Does updating the *same* record 4000 times per transaction reflect the
> real behavior of your application? If not, this is not a good
> benchmark. If so, consider redesigning your app to avoid so many
> redundant updates.
>
>
Real application modifiy every time modify different row.

> (For the record, the reason you see nonlinear degradation is the
> accumulation of tentatively-dead versions of the row, each of which has
> to be rechecked by each later update.)
>
>
There is another strange thing. We have two versions of our test
environment one with production DB copy and second genereated with
minimal data set and it is odd that update presented above on copy of
production is executing 170ms but on small DB it executing 6s !!!!

Michal Szymanski
http://blog.szymanskich.net