Re: Big problem with sql update operation - Mailing list pgsql-performance

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.

(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