Thread: When limit is greater than 813,500 activity seems to slow way down or stop

I'm writing a Java client to load Google Base with records from our
PostgreSQL 8.3 db.  The table I'm querying has about 15 million records
and I was hoping to just loop over this table creating one Google entry
for each db row.  However, using the code below, if the limit is greater
than around 813,500 the application just sits there apparently doing
nothing.  For lower values it starts immediately and runs to
completion.  I'm guessing that this might be some sort of memory problem
but I thought using a cursor-based, read-only result set would prevent
memory issues.

Any ideas why 813,500+ seems to be a magic number?

Thanks for any help or advice,
-=beeky


public static final String dataQuery = "select
A.cache_id,A.validname,A.validnameauthor, A.daycollected,
A.monthcollected, A.yearcollected, A.latitude, A.longitude, A.depth,
B.resource_full_name,
'http://marinespecies.org/aphia.php?p=taxdetails&id=' || C.worms_id as
worms_url,  'http://data.iobis.org/distRec.do?id='|| A.cache_id as
dist_rec_url from obis.cache A, obis.obis_resources B, obis.taxa C where
A.resource_id=B.resource_id and A.taxon_id=C.taxon_id offset 0 limit
800000" ;

conn.setAutoCommit(false);
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY) ;
stmt.setFetchSize(600);
rs = stmt.executeQuery(dataQuery) ;

while (rs.next()) {
    ... process next row

Wm.A.Stafford wrote:
> I'm writing a Java client to load Google Base with records from our
> PostgreSQL 8.3 db.  The table I'm querying has about 15 million records
> and I was hoping to just loop over this table creating one Google entry
> for each db row.  However, using the code below, if the limit is greater
> than around 813,500 the application just sits there apparently doing
> nothing.  For lower values it starts immediately and runs to
> completion.  I'm guessing that this might be some sort of memory problem
> but I thought using a cursor-based, read-only result set would prevent
> memory issues.
>
> Any ideas why 813,500+ seems to be a magic number?

[...]

> public static final String dataQuery = "select
> A.cache_id,A.validname,A.validnameauthor, A.daycollected,
> A.monthcollected, A.yearcollected, A.latitude, A.longitude, A.depth,
> B.resource_full_name,
> 'http://marinespecies.org/aphia.php?p=taxdetails&id=' ||
> C.worms_id as
> worms_url,  'http://data.iobis.org/distRec.do?id='|| A.cache_id as
> dist_rec_url from obis.cache A, obis.obis_resources B, obis.taxa C where
> A.resource_id=B.resource_id and A.taxon_id=C.taxon_id offset 0 limit
> 800000" ;

The first thing I would try is to run EXPLAIN on the query as you
vary the LIMIT. Does the execution plan change at 813500?

If that does not yield any results, I would try to run both queries
from the psql command line to see if the behaviour is similar.
That way you can determine if the problem has to do with the database
server or with the JDBC driver.

Yours,
Laurenz Albe