Обсуждение: temp_buffers and work_mem and big temp tables
Hey all: I was wondering if someone might spell out which setting has more impact on temporary tables being stored in memory versus disk? I have read the docs but I thought I'd reach out and ask the community: I keep increasing both in some scripts where Large Temp Tables are created, but I'm wondering if it's more efficient just to use one or the other.
On Tuesday, November 10, 2020, Wells Oliver <wells.oliver@gmail.com> wrote:
Hey all: I was wondering if someone might spell out which setting has more impact on temporary tables being stored in memory versus disk? I have read the docs but I thought I'd reach out and ask the community: I keep increasing both in some scripts where Large Temp Tables are created, but I'm wondering if it's more efficient just to use one or the other.
IIUC work_mem has nothing to do with temporary tables. All tables have a disk component and read/writes are done via buffers.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tuesday, November 10, 2020, Wells Oliver <wells.oliver@gmail.com> wrote:
>> Hey all: I was wondering if someone might spell out which setting has more
>> impact on temporary tables being stored in memory versus disk? I have read
>> the docs but I thought I'd reach out and ask the community: I keep
>> increasing both in some scripts where Large Temp Tables are created, but
>> I'm wondering if it's more efficient just to use one or the other.
> IIUC work_mem has nothing to do with temporary tables. All tables have a
> disk component and read/writes are done via buffers.
The temp_buffers setting controls how much temporary-table data can be
kept in a session's memory. If you get to more than that, it spills out
to disk (or at least to the kernel's disk buffers).
regards, tom lane