Thread: Guideline on use of temporary tables
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? Thank you very much.
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 > > > -----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 >
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