You wrote:
> Here's the offending loop. BTW, I messed up with the
> total number of rows, it's ~6,000,000, not 600,000.
[...]
> while(dbResult.next())
Unfortunately, PostgreSQL clients always retrieve the entire
resultset from the server before it is processed by the .next()
loop.
In your case this means that the 6.000.000 rows are first read
by the Java client into one huge ResultSet. This will no doubt
require much more than 50M of memory.
Can't you perform this operation in one or more SQL statements
or procedurally in PLSQL on the server, instead of retrieving
all data to the client?
Cheers,
René Pijlman