2007/10/15, Gauthier, Dave <dave.gauthier@xxxxxxxxx>: > 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@xxxxxxxxxxxxxx > [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Richard Huxton > Sent: Monday, October 15, 2007 5:32 AM > To: Jimmy Choi > Cc: pgsql-general@xxxxxxxxxxxxxx > Subject: Re: 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 > ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
