Received: from localhost (wm.hub.org [200.46.204.128])
	by postgresql.org (Postfix) with ESMTP id 8F21F9FB252
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Fri,  3 Nov 2006 15:40:35 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024)
	with ESMTP id 09370-06 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Fri,  3 Nov 2006 19:40:28 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from mail01.enterprisedb.com (mail01.enterprisedb.com
 [63.246.7.168])
	by postgresql.org (Postfix) with ESMTP id 5C3209FB274
	for <pgsql-performance@postgresql.org>;
	Fri,  3 Nov 2006 15:40:28 -0400 (AST)
thread-index: Acb/f+p+0/j6f/FKRF21HrV7V9bPBA==
Received: from [192.168.0.197] ([148.63.169.95]) by mail01.enterprisedb.com
	with Microsoft SMTPSVC(6.0.3790.1830); Fri, 3 Nov 2006 14:40:23 -0500
Content-Transfer-Encoding: 7bit
Subject: Re: profiling PL/pgSQL?
From: <korryd@enterprisedb.com>
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2757
Content-class: urn:content-classes:message
Importance: normal
To: "Richard Huxton" <dev@archonet.com>
Cc: "A. Kretschmer" <andreas.kretschmer@schollglas.com>,
	<pgsql-performance@postgresql.org>
In-Reply-To: <454B311E.9010001@archonet.com>
References: <C19FC1BA-466D-493F-9FDF-4687C4B7962C@gmail.com>
	<20061103112137.GD15480@a-kretschmer.de>
	<454B311E.9010001@archonet.com>
Content-Type: multipart/alternative;
	boundary="=-Vn0aW5j70mN8a4NXgRUu"
Date: Fri, 03 Nov 2006 14:40:25 -0500
Message-ID: <1162582825.7401.44.camel@sakai.localdomain>
MIME-Version: 1.0
X-Mailer: Evolution 2.4.0 
X-OriginalArrivalTime: 03 Nov 2006 19:40:28.0562 (UTC)
	FILETIME=[EA6D9F20:01C6FF7F]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200611/70
X-Sequence-Number: 21840

This is a multi-part message in MIME format.

--=-Vn0aW5j70mN8a4NXgRUu
Content-Type: text/plain;
	charset="UTF-8"
Content-Transfer-Encoding: 7bit

> > am  Fri, dem 03.11.2006, um  3:12:14 -0800 mailte Drew Wilson folgendes:
> >> I have 700 lines of non-performant pgSQL code that I'd like to  
> >> profile to see what's going on.
> >>
> >> What's the best way to profile stored procedures?
> > 
> > RAISE NOTICE, you can raise the aktual time within a transaction with
> > timeofday()
> 
> Of course you only have very small values of "best" available with 
> plpgsql debugging.
> 
> There's a GUI debugger from EnterpriseDB I believe, but I've no idea how 
> good it is. Any users/company bods care to let us know?



It's an excellent debugger (of course, I'm a bit biased).  

We are working on open-sourcing it now - we needed some of the plugin
features in 8.2.

As Jonah pointed out, we also have a PL/pgSQL profiler (already
open-sourced but a bit tricky to build).  The profiler tells you how
much CPU time you spent at each line of PL/pgSQL code, how many times
you executed each line of code, and how much I/O was caused by each line
(number of scans, blocks fetched, blocks hit, tuples returned, tuples
fetched, tuples inserted, tuples updated, tuples deleted).

It's been a while since I looked at it, but I seem to remember that it
spits out an XML report that you can coax into a nice HTML page via the
XSLT.

The plugin_profiler needs to be converted over to the plugin
architecture in 8.2, but that's not a lot of work.

                -- Korry


--
  Korry Douglas    korryd@enterprisedb.com
  EnterpriseDB      http://www.enterprisedb.com

--=-Vn0aW5j70mN8a4NXgRUu
Content-Type: text/html;
	charset="utf-8"
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.8.0">
</HEAD>
<BODY>
<BLOCKQUOTE TYPE=CITE>
<PRE>
<FONT COLOR="#000000">&gt; am  Fri, dem 03.11.2006, um  3:12:14 -0800 mailte Drew Wilson folgendes:</FONT>
<FONT COLOR="#000000">&gt;&gt; I have 700 lines of non-performant pgSQL code that I'd like to  </FONT>
<FONT COLOR="#000000">&gt;&gt; profile to see what's going on.</FONT>
<FONT COLOR="#000000">&gt;&gt;</FONT>
<FONT COLOR="#000000">&gt;&gt; What's the best way to profile stored procedures?</FONT>
<FONT COLOR="#000000">&gt; </FONT>
<FONT COLOR="#000000">&gt; RAISE NOTICE, you can raise the aktual time within a transaction with</FONT>
<FONT COLOR="#000000">&gt; timeofday()</FONT>

<FONT COLOR="#000000">Of course you only have very small values of &quot;best&quot; available with </FONT>
<FONT COLOR="#000000">plpgsql debugging.</FONT>

<FONT COLOR="#000000">There's a GUI debugger from EnterpriseDB I believe, but I've no idea how </FONT>
<FONT COLOR="#000000">good it is. Any users/company bods care to let us know?</FONT>
</PRE>
</BLOCKQUOTE>
<PRE>

</PRE>
It's an excellent debugger (of course, I'm a bit biased).&nbsp; <BR>
<BR>
We are working on open-sourcing it now - we needed some of the plugin features in 8.2.<BR>
<BR>
As Jonah pointed out, we also have a PL/pgSQL profiler (already open-sourced but a bit tricky to build).&nbsp; The profiler tells you how much CPU time you spent at each line of PL/pgSQL code, how many times you executed each line of code, and how much I/O was caused by each line (number of scans, blocks fetched, blocks hit, tuples returned, tuples fetched, tuples inserted, tuples updated, tuples deleted).<BR>
<BR>
It's been a while since I looked at it, but I seem to remember that it spits out an XML report that you can coax into a nice HTML page via the XSLT.<BR>
<BR>
The plugin_profiler needs to be converted over to the plugin architecture in 8.2, but that's not a lot of work.<BR>
<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Korry<BR>
<TABLE CELLSPACING="0" CELLPADDING="0" WIDTH="100%">
<TR>
<TD>
<BR>
<BR>
--<BR>
&nbsp; Korry Douglas&nbsp;&nbsp;&nbsp; <A HREF="mailto:korryd@enterprisedb.com">korryd@enterprisedb.com</A><BR>
&nbsp; EnterpriseDB&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <A HREF="http://www.enterprisedb.com">http://www.enterprisedb.com</A>
</TD>
</TR>
</TABLE>
</BODY>
</HTML>

--=-Vn0aW5j70mN8a4NXgRUu--