I found out that declaring tables outside of functions increases the execution time of the function. And CREATE UNLOGGED TABLE is very fast.
2012/11/23 Peter Kroon <plakr...@gmail.com> > 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/ >> > >
