Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group