Re: Guideline on use of temporary tables

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

>
>
> -----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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>