Re: Explaining functions.

Thanks! That'll reduce the amount of copy/pasting I have to do to figure
out the differences in times.

Matthew Hartman
Programmer/Analyst
Information Management, ICP
Kingston General Hospital
(613) 549-6666 x4294

-----Original Message-----
From: Chris Spotts [mailto:rfusca(at)gmail(dot)com]
Sent: Tuesday, June 23, 2009 10:48 AM
To: Hartman, Matthew; 'Merlin Moncure'
Cc: pgsql-general(at)postgresql(dot)org
Subject: RE: [GENERAL] Explaining functions.

> > is around 250 lines.
>
> What I normally do for benchmarking of complex functions is to
> sprinkle the source with "raise notice '%', timeofday();" to figure
> out where the bottlenecks are. Following that, I micro-optimize
> problem queries or expressions outside of the function body in psql.
>
[Spotts, Christopher]
I use this set of functions towards this end, sprinkled about...
I'm sure there are better ways to write it,but it works.

CREATE OR REPLACE FUNCTION set_var(name text, val text) RETURNS text AS
$$
if ($_SHARED{$_[0]} = $_[1]) {
return 'ok';
} else {
return "cannot set shared variable $_[0] to $_[1]";
}
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION get_var(name text) RETURNS text AS $$
return $_SHARED{$_[0]};
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION time_between_calls() RETURNS interval AS $$
DECLARE
ot text;
BEGIN
ot := get_var('calltime');
PERFORM set_var('calltime',timeofday());
RETURN timeofday():: timestamp - ot :: timestamp;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION start_time_between_calls() RETURNS void AS $$

BEGIN
PERFORM set_var('calltime',timeofday());
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION test() RETURNS void AS $$
BEGIN
PERFORM start_time_between_calls();
raise notice '%',time_between_calls();
PERFORM pg_sleep(3);
raise notice '%',time_between_calls();
END
$$ LANGUAGE plpgsql;