Re: Large selects - Mailing list pgsql-jdbc

Thank you both!
I will have a closer look tomorrow
.



On Aug 24, 2006, at 10:38 AM, Koth, Christian (DWBI) wrote:
>
> Bendik,
>
> - Loop through the results, building a Lucene index.
>
> I guess you are trying to search within the contact data and
> therefore using lucene? Why not using the database build in
> possibilities for searching using SQL?
>
>
> - Does anyone have any suggestions on how to deal with this, other
> that throwing more hardware at the problem? Cursors?
>
> We are using cursors to access large result sets which works quite
> well. Also with postgres JDBC you can use scrollable result sets
> (only forward scrolling) and a limited fetch size.
>
> Christian
>






On Aug 24, 2006, at 10:30 AM, Roland Walter wrote:

> Bendik Rognlien Johansen schrieb:
>> Hello!
>>
>> We are having a lot of trouble trying to use JDBC with PostgreSQL.
>>
>> This is what we want to achieve:
>>
>> - Select all rows in a table (people)
>> - Select addresses belonging to each person. By subselect or join
>> (addresses)
>> - Select phone numbers belonging to each person. By subselect or
>> join (contacts)
>> - Loop through the results, building a Lucene index.
>>
>>
>> Our tables:
>> - people: ~ 8 million records (could be larger in the future,
>> maybe 50 million)
>> - addresses ~8 million records
>> - contacts: ~10 million records
>>
>>
>> Hardware:
>> 2x dual core 3GHz intel Xeon
>> 1-2 Gb RAM
>>
>>
>>
>> Problem:
>>
>> It seems that the PostgreSQL driver fetches the whole result,
>> storing it in memory, causing Java to run out of memory.
>> java.lang.OutOfMemoryError
>> We have tried increasing memory for the program (-Xmn100M -Xms512M
>> -Xmx512M), but we only have one gigabyte of memory to work with.
>>
>> Does anyone have any suggestions on how to deal with this, other
>> that throwing more hardware at the problem? Cursors?
>>
>>
>> Any help is much appreciated!
>> Thanks
>>
> Look here:
>
> http://archives.postgresql.org/pgsql-jdbc/2006-01/msg00004.php
>
> --
> Roland Walter                   mailto: rwa (at) mosaic-ag (dot) com
> MOSAIC SOFTWARE AG               phone: +49 (0) 22 25 / 88 2-44 9
> Am Pannacker 3                     fax: +49 (0) 22 25 / 88 2-20 1
> D-53340 Meckenheim                http://www.mosaic-ag.com
>
>
> Die in dieser E-Mail enthaltenen Nachrichten und Anhaenge sind
> ausschliesslich fuer den bezeichneten Adressaten bestimmt. Sie
> koennen rechtlich geschuetzte, vertrauliche Informationen
> enthalten. Falls Sie nicht der bezeichnete Empfaenger
> oder zum Empfang dieser E-Mail nicht berechtigt sind, ist die
> Verwendung,
> Vervielfaeltigung oder Weitergabe von Nachrichten und Anhaengen
> untersagt.
> Falls Sie diese E-Mail irrtuemlich erhalten haben, informieren Sie
> bitte unverzueglich den Absender und vernichten Sie die E-Mail.
>
> This e-mail message and any attachment are intended exclusively for
> the named addressee. They may contain confidential information
> which may also be protected by professional secrecy. Unless you are
> the named addressee (or authorised to
> receive for the addressee) you may not copy or use this message or
> any attachment
> or disclose the contents to anyone else. If this e-mail was sent to
> you by mistake
> please notify the sender immediately and delete this e-mail.
>