Re: Explaining functions.

On Tue, Jun 23, 2009 at 3:04 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote:
> Merlin Moncure <mmoncure@gmail.com> writes:
>> On Tue, Jun 23, 2009 at 8:03 AM, Hartman,
>> Matthew<Matthew.Hartman@krcc.on.ca> wrote:
>>> Is there a recommended approach when trying to use EXPLAIN on a
>>> function? Specifically, a function that is more than the typical SELECT
>>> statement or tiny loop. The one in question that I'm hoping to optimize
>>> 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.
>
> There was some discussion once of using the same infrastructure the
> plpgsql debugger uses to build a plpgsql profiler.  That would help
> automate the first part of this, at least.  Anybody know the status
> of that project?

There is a profiler in the debugger source tree. Iirc, it dumps it's
data out to an XML in a not-so-friendly manner. I haven't ever tested
it, so it may have been broken since Korry handed it over.

http://pgfoundry.org/scm/?group_id=1000175

--
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com