Re: Guideline on use of temporary tables

Question regarding temp tables....

If I (user=joe) attach and run something that uses a temp table, then I
(user=joe again) attach again in another session, will there be 2
distinct temp tables? Or does one user get one temp table per DB?

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Richard Huxton
Sent: Monday, October 15, 2007 5:32 AM
To: Jimmy Choi
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Guideline on use of temporary tables

Jimmy Choi wrote:
> I would like to use temporary table as a caching mechanism to speed up
> queries within the same session.

> Is this what temporary table is designed for? Are there caveats that I
> should be aware of? Can you think of other better alternatives?

It's a very common usage of temporary tables. Another is when loading
data that you want to process / split up.

Bear in mind that a temporary table is private to a particular backend,
so if you had 100 connections all using a temporary table for the same
query, that could be 100 copies of the data - not necessarily a
performance improvement.

Also, temporary tables have real entries in the system-tables, so make
sure autovacuum (or your manual vacuums) are scanning pg_class etc often

enough.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend