Measuring execution time for sql called from PL/pgSQL

Поиск
Список
Период
Сортировка
От Aram Kananov
Тема Measuring execution time for sql called from PL/pgSQL
Дата
Msg-id 1071191825.5163.164.camel@localhost.localdomain
обсуждение исходный текст
Ответы Re: Measuring execution time for sql called from PL/pgSQL  (Tomasz Myrta <jasiek@klaster.net>)
Re: Measuring execution time for sql called from PL/pgSQL  ("David Shadovitz" <david@www.shadovitz.com>)
Список 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




В списке pgsql-performance по дате отправления:

Предыдущее
От: William Yu
Дата:
Сообщение: Re: Hardware suggestions for Linux/PGSQL server
Следующее
От: Alfranio Tavares Correia Junior
Дата:
Сообщение: Re: Performance problems with a higher number of clients