Skip site navigation (1) Skip section navigation (2)

Performance analysis of plpgsql code

From: "Karl O(dot) Pinc" <kop(at)meme(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Performance analysis of plpgsql code
Date: 2005-06-27 23:30:45
Message-ID: 1119915045l.4428l.5l@mofo (view raw or flat)
Thread:
Lists: pgsql-patchespgsql-performance
Hi,

I'm having a hard time finding the poorly performing
statements in my plpgsql procedures, many of which
are triggers.  Am I missing something?

I can get the query plans by starting up a new
connection and doing:
SET DEBUG_PRINT_PLAN TO TRUE;
SET CLIENT_MIN_MESSAGES TO DEBUG1;
And then running code that exercises my functions.
Then I can find the queries that, in theory,
could have problems.  But problems remain
after this.

What I'd really like is a SET variable (or maybe
a clause in CREATE FUNCTION) that causes any
functions compiled to issue EXPLAIN ANALYZE output
and the query text itself, to be RAISEd.
Then I could watch the performance as it ran.

Short of that I think I'm going to be reduced to
writing a C function that returns the real
system time so I can spatter my code with
RAISE statements that indicate actual execution
time.

Is there a better approach?
Does anybody have such a C function handy?


Karl <kop(at)meme(dot)com>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein


Responses

pgsql-performance by date

Next:From: Michael FuhrDate: 2005-06-27 23:33:03
Subject: Re: Performance analysis of plpgsql code
Previous:From: Karl O. PincDate: 2005-06-27 23:16:01
Subject: Forcing use of a particular index

pgsql-patches by date

Next:From: Michael FuhrDate: 2005-06-27 23:33:03
Subject: Re: Performance analysis of plpgsql code
Previous:From: Michael GlaesemannDate: 2005-06-27 20:37:18
Subject: Interval->day patch

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group