Re: Temporary tables and in-memory use

Lists: pgsql-hackers
From: Marios Vodas <mvodas(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Temporary tables and in-memory use
Date: 2011-09-29 16:47:53
Message-ID: CAMuJg2SnL78C7kWE4Fu8c7NUEFW_bf-4G2c9-4Sa=fMLV4bLqA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Hello,

If I'm not wrong, temporary tables stay in memory if they do not go over
temp_buffers limit (e.g. if temp_buffers is 2GB and the size of the table is
300MB the table will remain in memory).
What if a column is variable length (e.g. text), how does this column stay
in-memory since it should be stored in TOAST?
When I build a GiST index on a temporary table does the index stay in memory
as well?

Thank you,
Marios


From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Marios Vodas <mvodas(at)gmail(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Temporary tables and in-memory use
Date: 2011-09-29 16:55:29
Message-ID: 401.1317315329@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Marios Vodas <mvodas(at)gmail(dot)com> writes:
> If I'm not wrong, temporary tables stay in memory if they do not go over
> temp_buffers limit (e.g. if temp_buffers is 2GB and the size of the table is
> 300MB the table will remain in memory).
> What if a column is variable length (e.g. text), how does this column stay
> in-memory since it should be stored in TOAST?

Well, the toast table is also temp, so it'll get cached in temp_buffers
as well, as long as it fits.

> When I build a GiST index on a temporary table does the index stay in memory
> as well?

Same answer.

Keep in mind that temp_buffers is per process, not global. Just as with
work_mem, you need to be careful about setting it sky-high.

regards, tom lane


From: Marios Vodas <mvodas(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Temporary tables and in-memory use
Date: 2011-09-29 17:30:22
Message-ID: 4E84AB2E.7040807@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Thank you. The setup is intended for one user environment for complex
queries and operations that's why I wrote 2GB temp_buffers!
Thank you again, I really appreciate it.
Marios

On 29/9/2011 7:55 μμ, Tom Lane wrote:
> Marios Vodas<mvodas(at)gmail(dot)com> writes:
>> If I'm not wrong, temporary tables stay in memory if they do not go over
>> temp_buffers limit (e.g. if temp_buffers is 2GB and the size of the table is
>> 300MB the table will remain in memory).
>> What if a column is variable length (e.g. text), how does this column stay
>> in-memory since it should be stored in TOAST?
> Well, the toast table is also temp, so it'll get cached in temp_buffers
> as well, as long as it fits.
>
>> When I build a GiST index on a temporary table does the index stay in memory
>> as well?
> Same answer.
>
> Keep in mind that temp_buffers is per process, not global. Just as with
> work_mem, you need to be careful about setting it sky-high.
>
> regards, tom lane