> -----Original Message----- > From: mark [mailto:dvlhntr@xxxxxxxxx] > Sent: Thursday, March 10, 2011 9:37 AM > To: pgsql-general@xxxxxxxxxxxxxx > 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
