From ivanbeh@chronos.com.my Thu Jun  23 09:57:11 2005
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id D12A652968
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Thu, 23 Jun 2005 06:57:11 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 00513-01
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Thu, 23 Jun 2005 09:57:08 +0000 (GMT)
Received: from smtp-gw.fnbs.net.my (smtp-gw.fnbs.net.my [202.9.108.191])
	by svr1.postgresql.org (Postfix) with ESMTP id C6FDE528C7
	for <pgsql-performance@postgresql.org>;
	Thu, 23 Jun 2005 06:57:03 -0300 (ADT)
Received: from mail-std.fnbs.net.my (smtp-std.fnbs.net.my [202.9.108.197])
	by fnsrvlx9.fnbs.net.my (SMTP Mailer) with ESMTP id B3F5A529FF
	for <pgsql-performance@postgresql.org>;
	Thu, 23 Jun 2005 17:57:03 +0800 (MYT)
Received: from Beh (unverified [203.106.54.162])
	by mail-std.fnbs.net.my (SurgeMail 3.0c2) with ESMTP id 14657212
	for <pgsql-performance@postgresql.org>;
	Thu, 23 Jun 2005 17:57:02 +0800 MYT
Message-ID: <000601c577d9$e2658600$a279640a@Beh>
From: "Chun Yit(Chronos)" <ivanbeh@chronos.com.my>
To: <pgsql-performance@postgresql.org>
Subject: Postgresql7.4.5 running slow on plpgsql function 
Date: Thu, 23 Jun 2005 17:56:52 +0800
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0003_01C5781C.EF90D170"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.096 tagged_above=0 required=5 tests=HTML_50_60,
	HTML_MESSAGE
X-Spam-Level: 
X-Archive-Number: 200506/531
X-Sequence-Number: 13168

This is a multi-part message in MIME format.

------=_NextPart_000_0003_01C5781C.EF90D170
Content-Type: text/plain;
	charset="gb2312"
Content-Transfer-Encoding: quoted-printable

>hi, need some help with some experts here.
>currently we have a function that use together with temp table, it =
calls search result function, everytime
>this function is calling, it will go through some filter before come =
out as a result.
>now we have some major problem , the first time the function execute, =
it take about 13 second
>second time the function is execute, it take about 17 second, every =
time you execute the function
>the time taken will grow about 4 second, ?
>may i know what going on here?
>since we use function with temp table, so every statement that related =
to temp table will using EXECUTE
>command.
>
>regards
>ivan
------=_NextPart_000_0003_01C5781C.EF90D170
Content-Type: text/html;
	charset="gb2312"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dgb2312">
<META content=3D"MSHTML 6.00.2900.2668" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>&gt;hi, need some help with some =
experts=20
here.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&gt;currently we have a function that =
use together=20
with temp table, it calls search result function, everytime</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&gt;this function is calling, it will =
go through=20
some filter before come out as a result.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&gt;now we have some major problem , =
the first time=20
the function execute, it take about 13 second</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&gt;second time the function is =
execute, it take=20
about 17 second, every time you execute the function</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&gt;the time taken will grow about 4 =
second,=20
?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&gt;may&nbsp;i know what going on=20
here?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&gt;since we use function with temp =
table, so every=20
statement that related to temp table will using EXECUTE</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&gt;command.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&gt;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&gt;regards</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&gt;ivan</FONT></DIV></BODY></HTML>

------=_NextPart_000_0003_01C5781C.EF90D170--




From mike@fuhr.org Tue Jun  28 06:26:46 2005
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 2DD5652838
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 28 Jun 2005 03:26:46 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 36376-05
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 28 Jun 2005 06:26:45 +0000 (GMT)
Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158])
	by svr1.postgresql.org (Postfix) with ESMTP id 10A46529BD
	for <pgsql-performance@postgresql.org>;
	Tue, 28 Jun 2005 03:26:42 -0300 (ADT)
Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1])
	by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id j5S6QMeE084703
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK);
	Tue, 28 Jun 2005 00:26:25 -0600 (MDT)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: from winnie.fuhr.org (localhost [127.0.0.1])
	by winnie.fuhr.org (8.13.3/8.13.3) with ESMTP id j5S6QMwh018757;
	Tue, 28 Jun 2005 00:26:22 -0600 (MDT)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: (from mfuhr@localhost)
	by winnie.fuhr.org (8.13.3/8.13.3/Submit) id j5S6QKbU018756;
	Tue, 28 Jun 2005 00:26:20 -0600 (MDT) (envelope-from mfuhr)
Date: Tue, 28 Jun 2005 00:26:20 -0600
From: Michael Fuhr <mike@fuhr.org>
To: "Chun Yit(Chronos)" <ivanbeh@chronos.com.my>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgresql7.4.5 running slow on plpgsql function
Message-ID: <20050628062620.GB18635@winnie.fuhr.org>
References: <000601c577d9$e2658600$a279640a@Beh>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <000601c577d9$e2658600$a279640a@Beh>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.005 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200506/546
X-Sequence-Number: 13183

On Thu, Jun 23, 2005 at 05:56:52PM +0800, Chun Yit(Chronos) wrote:
>
> currently we have a function that use together with temp table, it calls
> search result function, everytime this function is calling, it will go
> through some filter before come out as a result.  now we have some major
> problem , the first time the function execute, it take about 13 second
> second time the function is execute, it take about 17 second, every time
> you execute the function the time taken will grow about 4 second, ?  may
> i know what going on here?  since we use function with temp table, so
> every statement that related to temp table will using EXECUTE command.

Could you post the function?  Without knowing what the code is doing
it's impossible to say what's happening.  Is the temporary table
growing on each function call?  Does the function delete records
from the table on each call, leaving a lot of dead tuples?

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/



From mike@fuhr.org Wed Jun  29 12:50:42 2005
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 1225F52970
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed, 29 Jun 2005 09:50:42 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 50050-05
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed, 29 Jun 2005 12:50:36 +0000 (GMT)
Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158])
	by svr1.postgresql.org (Postfix) with ESMTP id AE28252956
	for <pgsql-performance@postgresql.org>;
	Wed, 29 Jun 2005 09:50:34 -0300 (ADT)
Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1])
	by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id j5TCoNAl086665
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK);
	Wed, 29 Jun 2005 06:50:26 -0600 (MDT)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: from winnie.fuhr.org (localhost [127.0.0.1])
	by winnie.fuhr.org (8.13.3/8.13.3) with ESMTP id j5TCoNR0031317;
	Wed, 29 Jun 2005 06:50:23 -0600 (MDT)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: (from mfuhr@localhost)
	by winnie.fuhr.org (8.13.3/8.13.3/Submit) id j5TCoMqs031316;
	Wed, 29 Jun 2005 06:50:22 -0600 (MDT) (envelope-from mfuhr)
Date: Wed, 29 Jun 2005 06:50:22 -0600
From: Michael Fuhr <mike@fuhr.org>
To: "Chun Yit(Chronos)" <ivanbeh@chronos.com.my>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgresql7.4.5 running slow on plpgsql function
Message-ID: <20050629125022.GA31236@winnie.fuhr.org>
Reply-To: pgsql-performance@postgresql.org
References: <000601c577d9$e2658600$a279640a@Beh>
	<20050628062620.GB18635@winnie.fuhr.org>
	<003301c57c63$2bbcfee0$a279640a@Beh>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <003301c57c63$2bbcfee0$a279640a@Beh>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.005 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200506/587
X-Sequence-Number: 13224

[Please copy the mailing list on replies so others can contribute
to and learn from the discussion.]

On Wed, Jun 29, 2005 at 12:29:42PM +0800, Chun Yit(Chronos) wrote:
>
> Yes, the function will delete records from the temporary table every time 
> on each call.
> if leaving a lot of dead tuples, then how can we solve it?

If the function deletes all records from the temporary table then
you could use TRUNCATE instead of DELETE.  Otherwise you could
VACUUM the table between calls to the function (you can't run VACUUM
inside a function).

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/