Re: Problem: libpq, network traffic, memory usage

On Wed, 2005-12-07 at 11:01, Alexander Scholz wrote:
> Dear NG,
>
> I have a serious performance flaw, when using postgresql 8.1 (other
> versions haven't been tested) with libpq. When executing a
>
> select * from "xyz" 1)
>
> and "xyz" contains 300'000 records, it takes more than 60 seconds for
> the query just to complete. First I thought it is a performance problem
> of the server, but then we noticed an enormous amount of TCP/IP packets
> that are beeing received in that (waiting)time. The memory usage of my
> process, while waiting for the query to complete, bumps up to more than
> 300MB, the CPU usage goes up to 100%! It seems as if libpq would be
> receiving the complete resultset from the server _before_ returning from
> the query execution! (Even then 300MB seems for me to be too much, but
> that's not the point!) Just to ensure: I didn't perform ANY _fetch_ so
> far! It's just about executing the query.

Yep, libpq is doing exactly what you asked it to do, no more, no less.

If you're like your result set to be in a cursor, you'll need to declare
one.

try

declare cursor xyz as select ...
fetch 100;

and see how that works.