Re: sort mem: size in RAM vs size on Disk

> -----Original Message-----
> From: mark [mailto:dvlhntr(at)gmail(dot)com]
> Sent: Thursday, March 10, 2011 9:37 AM
> To: pgsql-general(at)postgresql(dot)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