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:
s/tree/three/;
Which version this interesting finding applies to?
Maybe try one where you TRUNCATE the table rather than DELETE from it?
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