From: Michal Szymanski <szymanskim@datera.pl>
To: pgsql-performance@postgresql.org
Subject: Big problem with sql update operation
Date: Fri, 25 May 2007 10:57:04 +0200
Message-ID: <4656A4E0.7020400@datera.pl> (raw)
Recently I've wrote few pgSql procedures that generates invoices and
store it in postgres table. Small test has shown that there is
performance problem. I've thought that string operation in pgsql are not
perfect but it has appeared that 90% of time program waste on very
simple update.
Below is my simplified procedures:
CREATE TABLE group_fin_account_tst (
group_fin_account_tst_id BIGSERIAL PRIMARY KEY,
credit NUMERIC(8,2) DEFAULT 0.00 NOT NULL
) ; ALTER TABLE group_fin_account_tst OWNER TO freeconetadm;
INSERT INTO group_fin_account_tst
(credit) VALUES (4);
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
END LOOP;
END; $BODY$ LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION test() OWNER TO freeconetadm;
select test();
The strange thing is how program behave when I increase number of
iteration.
Below my results (where u/s is number of updates per second)
On windows
500 - 0.3s(1666u/s)
1000 - 0.7s (1428u/s)
2000 - 2.3s (869u/s)
4000 - 9s (444u/s)
8000 -29s (275u/s)
16000-114s (14u/s)
On linux:
500 - 0.5s(1000u/s)
1000 - 1.8s (555u/s)
2000 - 7.0s (285u/s)
4000 - 26s (153u/s)
8000 -101s (79u/s)
16000-400s (40u/s)
On both systems relation between number of iteration and time is
strongly nonlinear! Do you know what is a problem? Is it possible to
commit transaction inside pgsql procedure because I think that maybe
transaction is too long?
Regards
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
Subject: Re: Big problem with sql update operation
In-Reply-To: <4656A4E0.7020400@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