pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to analyze function performance
3+ messages / 3 participants
[nested] [flat]
* How to analyze function performance
@ 2006-06-15 12:16 Mindaugas <mind@bi.lt>
0 siblings, 2 replies; 3+ messages in thread
From: Mindaugas @ 2006-06-15 12:16 UTC (permalink / raw)
To: pgsql-performance
Hello,
Is it possible to somehow analyze function performance? E.g.
we are using function cleanup() which takes obviously too much time
to execute but I have problems trying to figure what is slowing things
down.
When I explain analyze function lines step by step it show quite
acceptable performance.
PostgreSQL 8.0 is running on two dual core Opterons.
Thanks,
Mindaugas
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How to analyze function performance
@ 2006-06-15 13:03 Tomas Vondra <tv@fuzzy.cz>
parent: Mindaugas <mind@bi.lt>
1 sibling, 0 replies; 3+ messages in thread
From: Tomas Vondra @ 2006-06-15 13:03 UTC (permalink / raw)
To: Mindaugas <mind@bi.lt>; +Cc: pgsql-performance
It depends what is the purpose of the function. If it's mainly a
container for a heap of SQL queries along with some simple IF, ELSE
etc. then I use two simple ways to analyze the performance (or lack
of performance):
1) I use a lot of debug messages
2) I print out all SQL and the execute EXPLAIN / EXPLAIN ANALYZE on them
If the function is mainly a computation of something, it's usually nice
to try to use for example C language, as it's much faster than PL/pgSQL
for this type of functions.
But it depends on what you are trying to do in that function ...
Tomas
> Hello,
>
> Is it possible to somehow analyze function performance? E.g.
> we are using function cleanup() which takes obviously too much time
> to execute but I have problems trying to figure what is slowing things
> down.
>
> When I explain analyze function lines step by step it show quite
> acceptable performance.
>
> PostgreSQL 8.0 is running on two dual core Opterons.
>
> Thanks,
>
> Mindaugas
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How to analyze function performance
@ 2006-06-15 14:24 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mindaugas <mind@bi.lt>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2006-06-15 14:24 UTC (permalink / raw)
To: Mindaugas <mind@bi.lt>; +Cc: pgsql-performance
"Mindaugas" <mind@bi.lt> writes:
> Is it possible to somehow analyze function performance? E.g.
> we are using function cleanup() which takes obviously too much time
> to execute but I have problems trying to figure what is slowing things
> down.
> When I explain analyze function lines step by step it show quite
> acceptable performance.
Are you sure you are "explain analyze"ing the same queries the function
is really doing? You have to account for the fact that what plpgsql is
issuing is parameterized queries, and sometimes that limits the
planner's ability to pick a good plan. For instance, if you have
declare x int;
begin
...
for r in select * from foo where key = x loop ...
then what is really getting planned and executed is "select * from foo
where key = $1" --- every plpgsql variable gets replaced by a parameter
symbol "$n". You can model this for EXPLAIN purposes with a prepared
statement:
prepare p1(int) as select * from foo where key = $1;
explain analyze execute p1(42);
If you find out that a particular query really sucks when parameterized,
you can work around this by using EXECUTE to force the query to be
planned afresh on each use with literal constants instead of parameters:
for r in execute 'select * from foo where key = ' || x loop ...
The replanning takes extra time, though, so don't do this except where
you've specifically proved there's a need.
BTW, be careful to use quote_literal() when needed in queries built as
strings, else you'll have bugs and maybe even security problems.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-06-15 14:24 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-15 12:16 How to analyze function performance Mindaugas <mind@bi.lt>
2006-06-15 13:03 ` Tomas Vondra <tv@fuzzy.cz>
2006-06-15 14:24 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox