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
