Re: create table in memory

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 <plakroon(at)gmail(dot)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(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/
>>
>
>