Re: create table in memory

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(dot)rao(at)enterprisedb(dot)com>

>
> On Fri, Nov 23, 2012 at 2:43 PM, Peter Kroon <plakroon(at)gmail(dot)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 <plakroon(at)gmail(dot)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/
>