"Steven M. Wheeler" <swheeler@sabre.com> writes:
> Space taken up by entire DB: 6.7GB
> Space available on filesystem: 9.2GB
> Space taken by currnt table: 3.7GB
> Space taken by currnt index: 2.5GB
> Fluctuation of available space during a select distinct on the currnt table:
> max: 9.2GB, min: 4.1GB
OK, so you're not running out of space, but it could easy be that you
are hitting the limit on temp file size --- which is likely either 2 or
4Gb depending on whether your OS uses unsigned arithmetic for file
offsets.
Can you watch the pg_sorttempNNNN.NN files in the database directory and
see how large they get during the sort? There should be seven of 'em
generated by the sorting process, and at least one needs to grow to
reach the same size as the table (more or less).
BTW, I have some code which solves this problem by splitting large
temporary sort files into segments, just as we do for the tables
themselves ... but it's not even well-tested enough to commit to current
yet, let alone backpatch into 6.5.* ;-)
regards, tom lane
