From ivanbeh@chronos.com.my Thu Aug  11 03:38:06 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 D603452A06
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Thu, 11 Aug 2005 00:38:06 -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 30677-01
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Thu, 11 Aug 2005 03:37:58 +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 E376352A92
	for <pgsql-performance@postgresql.org>;
	Thu, 11 Aug 2005 00:37:54 -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 AA95752AD2
	for <pgsql-performance@postgresql.org>;
	Thu, 11 Aug 2005 11:37:51 +0800 (MYT)
Received: from Beh (unverified [203.106.54.162])
	by mail-std.fnbs.net.my (SurgeMail 3.0c2) with ESMTP id 17318585
	for <pgsql-performance@postgresql.org>;
	Thu, 11 Aug 2005 11:37:50 +0800 MYT
Message-ID: <002001c59e26$11bc5bf0$a279640a@Beh>
From: "Chun Yit(Chronos)" <ivanbeh@chronos.com.my>
To: <pgsql-performance@postgresql.org>
Subject: it is always delete temp table will slow down the postmaster?
Date: Thu, 11 Aug 2005 11:37:57 +0800
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_001D_01C59E69.1E7A1880"
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.079 tagged_above=0 required=5 tests=[AWL=-0.007,
	HTML_40_50=0.086, HTML_MESSAGE=0.001]
X-Spam-Level: 
X-Archive-Number: 200508/106
X-Sequence-Number: 13853

This is a multi-part message in MIME format.

------=_NextPart_000_001D_01C59E69.1E7A1880
Content-Type: text/plain;
	charset="gb2312"
Content-Transfer-Encoding: quoted-printable

>
>hi, i got one situation here, i create one pl/pgsql function that using =
temp table to store temporary data.
>wherever i execute my function, i need to delete all the data inside =
the temp table, but this will slow down the=20
>searching function if i conitnue to run the server because old tuples =
are not really clear if just using delete command.
>so i use drop table command and recreate the table. my question is, =
would it slow down the postmaster speed if i continue to=20
>run this searching function more than 300 time per day?, cause the =
speed for execute searching function will graduatelly increase=20
>after i test it for few day? anyway to test it is causing by the drop =
temp table and create temp table command?
>
>regards
>ivan
------=_NextPart_000_001D_01C59E69.1E7A1880
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;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&gt;hi, i got one situation here, i =
create one=20
pl/pgsql function that using temp table to store temporary =
data.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&gt;wherever i execute my function, i =
need to=20
delete all the data inside the temp table, but this will slow down the=20
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&gt;searching function if i conitnue to =
run the=20
server because old tuples are not really clear if just using delete=20
command.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&gt;so i use drop table command and =
recreate the=20
table. my question is, would it slow down the postmaster speed if i =
continue to=20
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&gt;run this searching function more =
than 300 time=20
per day?, cause the speed for execute searching function will =
graduatelly=20
increase </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&gt;after i test it for few day? anyway =
to test it=20
is causing by the drop temp table and create temp table =
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_001D_01C59E69.1E7A1880--




From zhouqq@cs.toronto.edu Thu Aug  11 06:39:53 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 6056E52EB5
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Thu, 11 Aug 2005 03:39:53 -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 19197-09
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Thu, 11 Aug 2005 06:39:49 +0000 (GMT)
Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2])
	by svr1.postgresql.org (Postfix) with ESMTP id EB75452EB2
	for <pgsql-performance@postgresql.org>;
	Thu, 11 Aug 2005 03:39:48 -0300 (ADT)
Received: by floppy.pyrenet.fr (Postfix, from userid 106)
	id 7C3663095A; Thu, 11 Aug 2005 08:49:47 +0200 (MET DST)
From: "Qingqing Zhou" <zhouqq@cs.toronto.edu>
X-Newsgroups: pgsql.performance
Subject: Re: it is always delete temp table will slow down the postmaster?
Date: Thu, 11 Aug 2005 14:37:20 +0800
Organization: Hub.Org Networking Services
Lines: 17
Message-ID: <dderrg$f6a$1@news.hub.org>
References: <002001c59e26$11bc5bf0$a279640a@Beh>
Reply-To: "Qingqing Zhou" <zhouqq@cs.toronto.edu>
X-Complaints-To: usenet@news.hub.org
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1506
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506
To: pgsql-performance@postgresql.org
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=1.185 tagged_above=0 required=5 tests=[AWL=-0.000,
	PRIORITY_NO_NAME=1.185]
X-Spam-Level: *
X-Archive-Number: 200508/109
X-Sequence-Number: 13856


""Chun Yit(Chronos)"" <ivanbeh@chronos.com.my> writes
>
>hi, i got one situation here, i create one pl/pgsql function that using
temp table to store temporary data.
>wherever i execute my function, i need to delete all the data inside the
temp table, but this will slow down the
>searching function if i conitnue to run the server because old tuples are
not really clear if just using delete command.
>so i use drop table command and recreate the table.

A better way to empty a table fast is "truncate table".

Regards,
Qingqing





From ivanbeh@chronos.com.my Thu Aug  11 07:03:32 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 BB0FF52EAF
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Thu, 11 Aug 2005 04:03:32 -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 88485-01
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Thu, 11 Aug 2005 07:03:28 +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 0199252EAB
	for <pgsql-performance@postgresql.org>;
	Thu, 11 Aug 2005 04:03:26 -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 5F8C252B51
	for <pgsql-performance@postgresql.org>;
	Thu, 11 Aug 2005 15:03:25 +0800 (MYT)
Received: from Beh (unverified [203.106.54.162])
	by mail-std.fnbs.net.my (SurgeMail 3.0c2) with ESMTP id 17330523
	for multiple; Thu, 11 Aug 2005 15:03:24 +0800 MYT
Message-ID: <001001c59e42$c9e9cca0$a279640a@Beh>
From: "Chun Yit(Chronos)" <ivanbeh@chronos.com.my>
To: "Qingqing Zhou" <zhouqq@cs.toronto.edu>,
 <pgsql-performance@postgresql.org>
References: <002001c59e26$11bc5bf0$a279640a@Beh> <dderrg$f6a$1@news.hub.org>
Subject: Re: it is always delete temp table will slow down the postmaster?
Date: Thu, 11 Aug 2005 15:03:30 +0800
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit
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.038 tagged_above=0 required=5 tests=[AWL=0.038]
X-Spam-Level: 
X-Archive-Number: 200508/110
X-Sequence-Number: 13857


----- Original Message ----- 
From: "Qingqing Zhou" <zhouqq@cs.toronto.edu>
To: <pgsql-performance@postgresql.org>
Sent: Thursday, August 11, 2005 2:37 PM
Subject: Re: [PERFORM] it is always delete temp table will slow down the 
postmaster?


>
> ""Chun Yit(Chronos)"" <ivanbeh@chronos.com.my> writes
>>
>>hi, i got one situation here, i create one pl/pgsql function that using
> temp table to store temporary data.
>>wherever i execute my function, i need to delete all the data inside the
> temp table, but this will slow down the
>>searching function if i conitnue to run the server because old tuples are
> not really clear if just using delete command.
>>so i use drop table command and recreate the table.
>
> A better way to empty a table fast is "truncate table".
>
> Regards,
> Qingqing
>

>sorry, but truncate table cannot use inside function, any other way ?
>Regards
>ivan
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>
>
> -- 
> No virus found in this incoming message.
> Checked by AVG Anti-Virus.
> Version: 7.0.338 / Virus Database: 267.10.5/68 - Release Date: 10/Aug/05
>