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