Re: Guideline on use of temporary tables

2007/10/15, Gauthier, Dave <dave(dot)gauthier(at)intel(dot)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(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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>