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