Re: Explaining functions. - Mailing list pgsql-general

From Chris Spotts
Subject Re: Explaining functions.
Date
Msg-id 006901c9f411$8c5c7260$a5155720$@com
Whole thread Raw
In response to Re: Explaining functions.  ("Hartman, Matthew" <Matthew.Hartman@krcc.on.ca>)
Responses Re: Explaining functions.
List pgsql-general
> > 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;


pgsql-general by date:

Previous
From: Greg Stark
Date:
Subject: Re: after vacuum analyze, explain still wrong
Next
From: sergio nogueira
Date:
Subject: PANIC: cannot abort transaction 140578842, it was already committed