Thread: Temporary Table

Temporary Table

From
"Christian Paul B. Cosinas"
Date:

Does Creating Temporary table in a function and NOT dropping them affects the performance of the database?

 



I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.html

Re: Temporary Table

From
"Christian Paul B. Cosinas"
Date:
I am creating a temporary table in every function that I execute.
Which I think is bout 100,000 temporary tables a day.

What is the command for vacuuming these 3 tables?

Also I read about the auto vacuum of postgresql.
How can I execute this auto vacuum or the settings in the configuration?

-----Original Message-----
From: Alvaro Nunes Melo [mailto:al_nunes@atua.com.br]
Sent: Tuesday, October 25, 2005 10:58 AM
To: Christian Paul B. Cosinas
Subject: Re: [PERFORM] Temporary Table

Hi Christian,

Christian Paul B. Cosinas wrote:

> Does Creating Temporary table in a function and NOT dropping them
> affects the performance of the database?
>
I believe it will depend on how many temporary tables you will create in a
daily basis. We had a performance problem caused by them, and by not
monitoring properly the database size. The pg_attribite, pg_class and
pg_depend tables grow a lot. When I found out that this was the problem I
saw some messages in the list archieve, and now the overall performance is
great.

What I do is daily run VACUUM FULL and REINDEX in this three tables.

Alvaro


I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.html


Configuration Suggestion

From
"Christian Paul B. Cosinas"
Date:
Hi! Here is the Specifications of My Server.
I would really appreciate the best configuration of postgresql.conf for my
sevrer.

I have tried so many value in the parameters but It seems that I cannot get
the speed I want.

OS: Redhat Linux
CPU: Dual Xeon
Memory: 6 gigabyte
PostgreSQL Version 8.0

Most of my queries are having Order by Clause, and group by clause.
Creation of temporary table.

The biggest rows is about 3-5 million which I query almost every 5 seconds.

I'm just wondering is it normal to have this result in my memory usage:
             total       used       free     shared    buffers     cached
Mem:       6192460    6172488      19972          0      39904    5890824
-/+ buffers/cache:     241760    5950700
Swap:      2096472          0    2096472

What does this mean?


I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.html