Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
	by hub.org (8.9.3/8.9.3) with ESMTP id BAA41041
	for <pgsql-interfaces@postgreSQL.org>;
	Sat, 20 Nov 1999 01:27:42 -0500 (EST)
	(envelope-from tgl@sss.pgh.pa.us)
Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id BAA06507;
	Sat, 20 Nov 1999 01:26:35 -0500 (EST)
To: dougt@mugc.cc.monash.edu.au
cc: pgsql-interfaces@postgreSQL.org
Subject: Re: [INTERFACES] Front end memory consumption in SELECT 
In-reply-to: Your message of Sat, 20 Nov 1999 12:10:27 +1100 (EST)
	<199911200110.MAA09758@mugca.cc.monash.edu.au>
Date: Sat, 20 Nov 1999 01:26:35 -0500
Message-ID: <6505.943079195@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Douglas Thomson <dougt@mugc.cc.monash.edu.au> writes:
> However, judging by the memory consumption of my front-end process,
> it would seem that the SELECT is loading the entire table into memory
> before I even fetch the first row! Can anyone confirm that this is in
> fact what goes on?

libpq handles SELECTs that way.  You should consider DECLARE CURSOR
and FETCH if you need to retrieve a large query result in chunks.

It was probably bad design for libpq to offer random access to query
results --- I'm sure there are few applications that really care,
and the memory-consumption problem is a very real one for many apps.
But I see no way to fix it without fundamental changes to libpq's API,
and it's not clear it's worth that kind of pain.  Maybe there will
be a deliberately-incompatible libpq Mark II someday ... or maybe we'll
switch to a whole new client interface like CORBA.

> If so, is there any way to avoid it? The obvious solution would seem
> to be to use LIMIT and OFFSET to get just a few thousand rows at a
> time, but will that suffer from a time overhead while the backend
> skips over millions of rows to get to the ones it needs??

Yes.  See the CURSOR stuff instead.

			regards, tom lane