[HACKERS] Temporary tables and in-memory use
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
Re: [HACKERS] Temporary tables and in-memory use
Marios Vodas mvo...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Temporary tables and in-memory use
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 Vodasmvo...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
