X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 75486D1B513 for ; Fri, 12 Dec 2003 01:17:44 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 40621-10 for ; Thu, 11 Dec 2003 21:17:16 -0400 (AST) Received: from p15135922.pureserver.info (willfork.com [217.160.217.214]) by svr1.postgresql.org (Postfix) with ESMTP id 0C4DFD1B507 for ; Thu, 11 Dec 2003 21:17:12 -0400 (AST) Received: from pD9EB7C08.dip.t-dialin.net (pD9EB7C08.dip.t-dialin.net [217.235.124.8]) (authenticated bits=0) by p15135922.pureserver.info (8.12.6/8.12.6) with ESMTP id hBC1H6Am011618 for ; Fri, 12 Dec 2003 02:17:09 +0100 Subject: Measuring execution time for sql called from PL/pgSQL From: Aram Kananov To: "pgsql-performance@postgresql.org" Content-Type: text/plain Organization: Message-Id: <1071191825.5163.164.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.2 (1.2.2-5) Date: 12 Dec 2003 02:17:06 +0100 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/189 X-Sequence-Number: 5049 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