From: Michal Szymanski <szymanskim@datera.pl>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Big problem with sql update operation
Date: Sat, 26 May 2007 00:27:02 +0200
Message-ID: <465762B6.3080608@datera.pl> (raw)
In-Reply-To: <8751.1180103318@sss.pgh.pa.us>
References: <4656A4E0.7020400@datera.pl>
<8751.1180103318@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: szymanskim@datera.pl, tgl@sss.pgh.pa.us
Subject: Re: Big problem with sql update operation
In-Reply-To: <465762B6.3080608@datera.pl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox