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
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 |