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

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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Richard Huxton
Sent: Monday, October 15, 2007 5:32 AM
To: Jimmy Choi
Cc: pgsql-general@postgresql.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

2007/10/15, Gauthier, Dave <dave.gauthier@intel.com>:

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?

There will be 2 distinct tables with same name

Pavel

On 10/12/07, Jimmy Choi <yhjchoi@gmail.com> wrote:

I'm looking for general guideline on the use of temporary tables.

I would like to use temporary table as a caching mechanism to speed up
queries within the same session. Specifically, a temporary table is
created to store a subset of data from a possibly large table, and
subsequent queries select from the temporary table instead of
re-applying the same complex filters on the actual table again and
again.

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

well, let's start with listing a couple of reasons reasons _not_ to
use temporary tables.
* your application sessions can't be mapped to database sessions
(usually web environments with connection pooling)
* plan invalidation issues with functions (largely fixed in upcoming 8.3)
* you need tables to be 'global'...shared between sessions
* you are using temp tables in place of a more elegant solution like a
view (this is very situational)

other than the above, go for it; temp tables are faster than regular
tables and give you full benefits of sql for holding and manipulating
your data.

merlin