>>>>> "edipoelder" == edipoelder <edipoelder@ig.com.br> writes:
edipoelder> times. Then I changed the function and run, at id = edipoelder> 14. Change again at id = 15. Where is
underlined edipoelder> (^^^^), i tried to put, 'now', timestamp 'now', etc, edipoelder> and always get the same
time.What i'm doing wrong? edipoelder> obs.: -> TABLE TEST (ID SERIAL, ANUMBER INTEGER) -> TABLE TIMES (ID
SERIAL,START TIME, END TIME); PostgreSQL -> 7.0.2 under Conectiva Linux
now() returns the time at the start of a transaction. Since pl/psql
functions are implicit transactions, now() will not change inside a
function call.
I've used something like the following:
create function bm(integer) returns text as '
declare cnt alias for $1; startt text; endt text;
begin startt := timeofday(); for i in 1..cnt LOOP
-- insert statement you want to time here
end loop; endt := timeofday();
return delta_time_msecs(startt,endt);
end;' language 'plpgsql';
create function delta_time_msecs(text,text) returns float8 as '
declare startt alias for $1; endt alias for $2; span interval;
days float8; hours float8; minutes float8; mseconds float8;
begin span := endt::timestamp - startt::timestamp; mseconds :=
date_part(''milliseconds'',span)::float8; minutes := date_part(''minutes'',span)::float8; hours :=
date_part(''hours'',span)::float8; days := date_part(''days'',span)::float8;
return abs(mseconds + minutes*60.0*1000.0 + hours*3600.0*1000.0 + days*24.0*3600.0*1000.0);
end;' language 'plpgsql';
select bm(1000)::float8/1000.0;
This will give you the average time, averaged over a thousand queries.
-Dan
