Tuesday, November 18, 2008

plpgsql and temp. tables

I tested speed of three possible styles of work with temp tables inside stored procedures. Usually I preferred checking of existence table to exception's trapping. I didn't expect it, but exception's trapping is the best of all.

create or replace function test1()
returns void as $$
begin
drop table if exists omega;
create temp table omega(a integer);
insert into omega values(10);
if exists(select * from omega) then end if;
end;
$$ language plpgsql;

create or replace function test2()
returns void as $$
begin
if exists(select * from pg_class where relname='omega' and pg_table_is_visible(oid)) then
delete from omega;
else
create temp table omega(a integer);
end if;
insert into omega values(10);
if exists(select * from omega) then end if;
end;
$$ language plpgsql;

create or replace function test3()
returns void as $$
begin
begin
delete from omega;
exception
when others then
create temp table omega(a integer);
end;
insert into omega values(10);
if exists(select * from omega) then end if;
end;
$$ language plpgsql;

test via pgbench

Test1:
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 339.780441 (including connections establishing)
tps = 340.172513 (excluding connections establishing)

Test2:
scaling factor: 1
query mode: simple
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 1891.021562 (including connections establishing)
tps = 1907.533096 (excluding connections establishing)

Test3:
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 2664.756569 (including connections establishing)
tps = 2698.289177 (excluding connections establishing

4 Comments:

At November 18, 2008 at 12:18 PM , Anonymous Anonymous said...

s/tree/three/;

 
At November 19, 2008 at 6:18 AM , Anonymous Anonymous said...

Which version this interesting finding applies to?

 
At November 19, 2008 at 10:07 AM , Anonymous Anonymous said...

Maybe try one where you TRUNCATE the table rather than DELETE from it?

 
At November 19, 2008 at 12:30 PM , Blogger Pavel Stěhule said...

I did these tests on 8.4.

Three years ago I found, so delete on really small tables is faster than truncate.

 

Post a Comment

Subscribe to Post Comments [Atom]

<< Home