Measuring execution time for sql called from PL/pgSQL

From: Aram Kananov <aram(at)kananov(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Measuring execution time for sql called from PL/pgSQL
Date: 2003-12-12 01:17:06
Message-ID: 1071191825.5163.164.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi,

I've got very slow insert performance on some
table which has trigger based on complex PL/pgSQL function.
Apparently insert is slow due some slow sql inside that function,
since CPU load is very high and disk usage is low during insert.
I run Red Hat 9
Anthlon 2.6
1GB ram
Fast IDE Disk

Setting following in postgres.conf apparently doesn't help:
log_statement = true
log_duration = true
since it logs only sql issued by client. It logs only once
per session the sql text but during call to the PL/pgSQL function,
but of course no duration.

Due the complexity of PL/pgSQL function trying to step by step
see the execution plans is very time consuming.

Q1) Is there any way to see which statements are called for PL/pgSQL
and their duration?

I've tried to measure the duration of sql with printing out
"localtimestamp" but for some reason during the same pg/plsql call it
returns the same
value:

Example:
Following gets and prints out the localtimestamp value in the loop
create or replace function foobar()
returns integer as '
declare
v timestamp;
begin
loop
select localtimestamp into v;
raise notice ''Timestamp: %'', v;
end loop;
return null;
end; ' language 'plpgsql'
;

and as result of "select foobar();"

i constantly get the same value:
NOTICE: Timestamp: 2003-12-12 01:51:35.768053
NOTICE: Timestamp: 2003-12-12 01:51:35.768053
NOTICE: Timestamp: 2003-12-12 01:51:35.768053
NOTICE: Timestamp: 2003-12-12 01:51:35.768053
NOTICE: Timestamp: 2003-12-12 01:51:35.768053

Q2) what i do wrong here and what is the "Proper Way" to measure
execution time of sql called inside PG/plSQL.

Thanks in advance

WBR
--
Aram

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message David Shadovitz 2003-12-12 01:50:31 Re: Measuring execution time for sql called from PL/pgSQL
Previous Message William Yu 2003-12-11 23:32:47 Re: Hardware suggestions for Linux/PGSQL server