Re: Big problem with sql update operation

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.

(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.)

regards, tom lane