I've put up a small test case for creating TEMP and UNLOGGED tables. DROP TABLE IF EXISTS test CASCADE; CREATE TEMP TABLE test( id serial, the_value text ); Exec time: 54ms
DROP TABLE IF EXISTS test CASCADE; CREATE UNLOGGED TABLE test( id serial, the_value text ); Exec time: 198ms There is a significant difference. Also I need those tables per session, so creating and dropping with TEMP tables appear to be faster. Best, Peter KRoon 2012/11/23 Raghavendra <raghavendra....@enterprisedb.com> > > On Fri, Nov 23, 2012 at 2:43 PM, Peter Kroon <plakr...@gmail.com> wrote: > >> I've converted some mssql functions and they appear to be slower in pgsql. >> I use a lot of declared tables in mssql as they are created in memory. >> Which makes it very fast. >> >> 2012/11/23 Peter Kroon <plakr...@gmail.com> >> >>> Is a temp table created to memory(ram) or disk? >>> I've converted some msssq >>> >> >> > Not exactly as MS Sql declare tables. > In PostgreSQL, TEMP tables are session-private. These tables are session > based and stored in a special schema and visible only to the backend which > has created. Memory management is controlled with temp_buffer(shared by all > backends) in postgresql.conf. > > You should check UNLOGGED tables of same family, these tables are visible > to all the backends and data shared across backends. > Since, data is not written to WAL's you should get better performance. > > --- > Regards, > Raghavendra > EnterpriseDB Corporation > Blog: http://raghavt.blogspot.com/ >
