pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedMeasuring the execution time of functions within functions...
3+ messages / 3 participants
[nested] [flat]
* Measuring the execution time of functions within functions...
@ 2006-04-03 09:42 Mario Splivalo <mario.splivalo@mobart.hr>
2006-04-03 12:50 ` Re: Measuring the execution time of functions within functions... Alvaro Herrera <alvherre@commandprompt.com>
2006-04-03 16:32 ` Re: Measuring the execution time of functions within functions... Jerry Sievers <jerry@jerrysievers.com>
0 siblings, 2 replies; 3+ messages in thread
From: Mario Splivalo @ 2006-04-03 09:42 UTC (permalink / raw)
To: pgsql-performance
I have asked this before, but haven't noticed any response, so if there
were any, I appologize for asking this again...
I have a function that is called by middle-tier (java trough JDBC), and
in postgres log I can see only the execution time of that function. I
have no idea how long are functions insde taking time to execute.
Since the function is written in plpgsql I tried to calculate the
durations by using now() function, but realized that within the
transaction now() always retunrs the same value.
The good thing is that those RAISE NOTICE calls from within my function
are logged as they're encountered, so, with carefully placed RAISE
NOTICE calls I could see how much time are the -inside- functions
taking.
For instance:
CREATE FUNCTION test_outer() RETURNS void
AS
$$BODY$$BEGIN
RAISE NOTICE 'We start here'
PERFORM SELECT someInternalFunction1();
RAISE NOTICE 'InternalFunction1 is done now.';
PERFORM SELECT someInternalFunction2();
RAISE NOTICE 'InternalFunction2 is done now.';
-- ... more code here
END$$BODY$$
LANGUAGE 'plpgsql'
Is there any other, maybe more convinient way to measure the 'inside'
function performance? I also have a problem if the outer function is
written in SQL, like this, for instance:
CREATE FUNCTION getSomeData(param1, param2, param3)
RETURN SETOF someType
AS
$$BODY$$SELECT
*
FROM
someTable
JOIN someOtherFunction($1, $2, $3) ON someTable.col =
someOtherFunction.col
WHERE
someCondition
$$BODY$$
LANGUAGE 'sql'.
Thank you in advance,
Mario
--
Mario Splivalo
Mob-Art
mario.splivalo@mobart.hr
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Measuring the execution time of functions within functions...
2006-04-03 09:42 Measuring the execution time of functions within functions... Mario Splivalo <mario.splivalo@mobart.hr>
@ 2006-04-03 12:50 ` Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 0 replies; 3+ messages in thread
From: Alvaro Herrera @ 2006-04-03 12:50 UTC (permalink / raw)
To: Mario Splivalo <mario.splivalo@mobart.hr>; +Cc: pgsql-performance
Mario Splivalo wrote:
> Since the function is written in plpgsql I tried to calculate the
> durations by using now() function, but realized that within the
> transaction now() always retunrs the same value.
Maybe you can use timeofday().
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Measuring the execution time of functions within functions...
2006-04-03 09:42 Measuring the execution time of functions within functions... Mario Splivalo <mario.splivalo@mobart.hr>
@ 2006-04-03 16:32 ` Jerry Sievers <jerry@jerrysievers.com>
1 sibling, 0 replies; 3+ messages in thread
From: Jerry Sievers @ 2006-04-03 16:32 UTC (permalink / raw)
To: mario.splivalo@mobart.hr; +Cc: pgsql-performance
Mario Splivalo <mario.splivalo@mobart.hr> writes:
> I have asked this before, but haven't noticed any response, so if there
> were any, I appologize for asking this again...
>
> I have a function that is called by middle-tier (java trough JDBC), and
> in postgres log I can see only the execution time of that function. I
> have no idea how long are functions insde taking time to execute.
>
> Since the function is written in plpgsql I tried to calculate the
> durations by using now() function, but realized that within the
> transaction now() always retunrs the same value.
>
> The good thing is that those RAISE NOTICE calls from within my function
> are logged as they're encountered, so, with carefully placed RAISE
> NOTICE calls I could see how much time are the -inside- functions
> taking.
>
> For instance:
>
> CREATE FUNCTION test_outer() RETURNS void
> AS
> $$BODY$$BEGIN
> RAISE NOTICE 'We start here'
> PERFORM SELECT someInternalFunction1();
> RAISE NOTICE 'InternalFunction1 is done now.';
> PERFORM SELECT someInternalFunction2();
> RAISE NOTICE 'InternalFunction2 is done now.';
> -- ... more code here
> END$$BODY$$
> LANGUAGE 'plpgsql'
>
> Is there any other, maybe more convinient way to measure the 'inside'
> function performance? I also have a problem if the outer function is
> written in SQL, like this, for instance:
See the timeofday() func which returns the actual time and is not
frozen in the current transaction. You'll need to cast it to
timestamp or other if wishing to do time arithmetic deltas on it.
HTH
--
-------------------------------------------------------------------------------
Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant
305 321-1144 (mobile http://www.JerrySievers.com/
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-04-03 16:32 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-03 09:42 Measuring the execution time of functions within functions... Mario Splivalo <mario.splivalo@mobart.hr>
2006-04-03 12:50 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-04-03 16:32 ` Jerry Sievers <jerry@jerrysievers.com>
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