Re: out of memory using Postgres with Spring/Hibernate/Java

On 3/15/06, maarten roosendaal <mroosendaal(at)yahoo(dot)com> wrote:
> Hi,
>
> We are currently having a problem that our Postgres DB
> is throwing an SQL error which states that it's 'out
> of memory'.
>
> What we have is a DB with 1 table that has 3.9 million
> records. We need to find certain records that are to
> be processed by a Java App so we do a "select id from
> table where type=a and condition in (1, 2) order by id
> limit 2000". When this query gets executed we see the
> memory on the DB Server increasing and after it has
> finishes it drops a bit but we see it growing a few MB
> per few minutes. This has caused an out of memory
> after the system has been processing for a day or 2.
> The query is heavy because of the order by but that
> does not explain why the memory is increasing.

What does explain show for the query? Are the fields indexed
appropriately? Have you analyzed the table recently?

Postgres needs to store the ordered results somewhere so of course
that explains the memory increase.

--
Postgresql & php tutorials
http://www.designmagick.com/