From: Tom Lane <tgl@sss.pgh.pa.us>
To: dougt@mugc.cc.monash.edu.au
Cc: pgsql-interfaces@postgreSQL.org
Subject: Re: [INTERFACES] Front end memory consumption in SELECT
Date: Sat, 20 Nov 1999 01:26:35 -0500
Message-ID: <6505.943079195@sss.pgh.pa.us> (raw)
In-Reply-To: <199911200110.MAA09758@mugca.cc.monash.edu.au>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: tgl@sss.pgh.pa.us, dougt@mugc.cc.monash.edu.au
Subject: Re: [INTERFACES] Front end memory consumption in SELECT
In-Reply-To: <6505.943079195@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox