Message-ID: <465762B6.3080608@datera.pl>
Date: Sat, 26 May 2007 00:27:02 +0200
From: Michal Szymanski <szymanskim@datera.pl>
User-Agent: Thunderbird 2.0.0.0 (Windows/20070326)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: pgsql-performance@postgresql.org
Subject: Re: Big problem with sql update operation
References: <4656A4E0.7020400@datera.pl> <8751.1180103318@sss.pgh.pa.us>
In-Reply-To: <8751.1180103318@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Tom Lane wrote:
> Michal Szymanski <szymanskim@datera.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