sort mem: size in RAM vs size on Disk

What version of PostgreSQL are you using? sort_mem is now called
work_mem (to better reflect the reality that it isn't just used in
sorting, I think), and has been for some time.

--
Peter Geoghegan       http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services

On Thu, Mar 10, 2011 at 8:03 AM, Peter Geoghegan <peter@2ndquadrant.com> wrote:

What version of PostgreSQL are you using? sort_mem is now called
work_mem (to better reflect the reality that it isn't just used in
sorting, I think), and has been for some time.

--
Peter Geoghegan       http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services

I hang my head somewhat down in shame as I type this...- 8.3.7

I just used the term "sort mem" as it's the sorting operations that I
am typically seeing spilling over to disk when I check the query with
an explain analyze.

I understand that work_mem is a more accurate description.

in summary it seems that if I see a temp file logged of say 20MB I
need about 40MB of work_mem before it doesn't spill to disk. just
wondering if I am at all accurate with this or if I am way off base.

thanks

-Mark

-----Original Message-----
From: mark [mailto:dvlhntr@gmail.com]
Sent: Thursday, March 10, 2011 9:37 AM
To: pgsql-general@postgresql.org
Subject: sort mem: size in RAM vs size on Disk

Hi all,

I am wondering if anyone has any estimates on how much larger
a working set for a sort is when the query execution puts it
in memory vs when it spills out to disk. It seems like sorts
in memory are larger than they are if they still out to disk.
(which I could understand), I am just looking for a general
'rule' if I see 20M in an exernal disk merge that it means I
would have needed 2 x that for work_mem before it would not
have spilled out. (2x seems to be about right thus far)

Also I am seeing COPY statements (to stdout) have temp files
a lot. These copies have a select in them so usually them
temp file is only a few meg, yet no matter how large my
work_mem is they always seem to use a temp file.
Is this normal or should I keep looking into this? E.g. my
work mem is 32MB currently and I see some copies to stdout
use a 12MB temp file.

3rd question:

If I see (eg.) pgsql_tmp25049.0 and then pgsql_tmp25049.1,
pgsql_tmp25049.2,
pgsql_tmp25049.3 should I assume the max file size for a temp
file is 1024MB and then it spills to the next one ?(it would
not surprise me if this query needed Gigs of temp files...).
is it normal for .0 to be sub 1024MB while 1 and 2 are ? they
all list the same same statement as the cause and I don't
think we ran it 3 times.

Thank you,

-Mark

Mark, you are about right in regards to ratio between required work_mem
size and the size that sort operation occupies on disk. work_mem needs
to be 2-3 times greater than sort occupies on disk.

As for "temp" files, PG has separate from work_mem memory area called
temp_buffers, try to play with this configuration parameter.

And, yes there is a 1GB files size limit (not only for temp files),
that's why you see .1, .2, ... In the file names.

Regards,
Igor Neyman