Friday, August 29, 2008

updatable cursor's test


I had to update every row of table with an result of external function. It was possibility for testing of some patterns:
postgres=# create table testcursor(i integer primary key, v integer);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "testcursor_pkey" for table "testcursor"
postgres=# insert into testcursor select i, 0 from generate_series(1,100000) g(i);
INSERT 0 100000
postgres=# analyze testcursor;

/* external function sample */
create or replace function ext_fce(a integer, b integer)
returns int as $
declare r int;
/* protect section */
r := b;
exception when others then
r := null;
return r;
$$ language plpgsql;
1. test - standard update statement
postgres=# update testcursor set v = ext_fce(i, 30);
UPDATE 100000
Time: 4369,246 ms
2. test - update with updateable cursors
create or replace function testc2(_v integer)
returns void as $$
c cursor for select i from testcursor;
_i integer;
open c;
fetch c into _i;
while found loop
update testcursor set v = ext_fce(_i, _v)
where current of c;
fetch c into _i;
end loop;
close c;
$$ language plpgsql;
postgres=# select testc2(20);

(1 row)

Time: 8434,985 ms
3. test - update with PK
create or replace function testc3(_v integer)
returns void as $$
declare _i integer;
for _i in select i from testcursor loop
update testcursor set v = ext_fce(_i,_v) where i = _i;
end loop;
$$ language plpgsql;
postgres=# select testc3(30);

(1 row)

Time: 9959,209 ms
Result: Using updateable cursor is about 90% slower than one statement. Usig pk is about 17% slower than updateable cursor. - So updateable cursors has sense and it is well to use it. But every iteration is much slower than one statement's update.


At August 29, 2008 at 5:04 AM , Anonymous Anonymous said...

The function testc3 is missing.

At August 29, 2008 at 5:17 AM , Blogger Pavel Stěhule said...

fixed, thanks

At August 29, 2008 at 10:16 AM , Blogger Luca Veronese said...

The result is consistent with the idea that relational databases are better used in a set oriented fashion when possible. Functions extend the domain of applicability of this set-oriented approach (but beware of SQL code inside them).
Your tests are interesting for two reasons:
first: we know how much cursors can suck;
second: cursors are anyway a very good solution if compared with client side code performing the same task, which should be on average at least an order of magnitude slower (I normally use java with an ORM layer and know what it means).
Thank you for sharing your results.


Post a Comment

Subscribe to Post Comments [Atom]

<< Home