Обсуждение: pg_sorttemp hits 2GB during index construction

I am trying to make an index on three columns (text, int2, date)
on a table with 193 million records.   I believe I am finding that
the pg_sorttemp files reach 2GB before the index finishes. 
The backend finishes the index but it's clearly missing tuples.

Does this make sense to you folks?

--Martin

P.S. My text field only contains a single char and I realize that
I was foolish not use a char(1) instead of varchar . . . 

Martin Weinberg <weinberg@osprey.astro.umass.edu> writes:
> I am trying to make an index on three columns (text, int2, date)
> on a table with 193 million records.   I believe I am finding that
> the pg_sorttemp files reach 2GB before the index finishes. 

2GB/193million is only about 10 (bytes per index tuple), and your
index tuples obviously will need more than 10 bytes apiece, so
yeah, you can't do that in 6.5.*.  It'd lose even without the fact
that sorts in 6.5.* require more space than the actual data volume.

One possible workaround is to define the indexes while the table
is empty and then fill the table.  You could probably have not only
a coffee break but a full-course meal while the data is loading,
but at least it'd work.

> The backend finishes the index but it's clearly missing tuples.

Yeah :-(.  The 6.5 sort code fails to notice write errors on the temp
files, so lost tuples would be the likely result of file overflow.

These problems are fixed in current sources, but I dunno if you
want to run bleeding-edge development code just to get work done...
        regards, tom lane