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

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