pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedFront end memory consumption in SELECT
3+ messages / 2 participants
[nested] [flat]
* Front end memory consumption in SELECT
@ 1999-11-20 01:10 Douglas Thomson <dougt@mugc.cc.monash.edu.au>
1999-11-20 06:26 ` Re: [INTERFACES] Front end memory consumption in SELECT Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Douglas Thomson @ 1999-11-20 01:10 UTC (permalink / raw)
To: pgsql-interfaces
I have a large table that I need to traverse in full. I currently
start with a simple unrestricted SELECT, and then fetch each and
every row one at a time. I thought that by fetching just one row at a
time I would not consume any significant amount of memory.
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?
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??
Thanks for any clues anyone can provide!
Doug.
P.S. If it matters, I am using the Perl interface. I am also running in
SERIALIZABLE mode...
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [INTERFACES] Front end memory consumption in SELECT
1999-11-20 01:10 Front end memory consumption in SELECT Douglas Thomson <dougt@mugc.cc.monash.edu.au>
@ 1999-11-20 06:26 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-11-21 06:14 ` Re: [INTERFACES] Front end memory consumption in SELECT Douglas Thomson <dougt@mugc.cc.monash.edu.au>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 1999-11-20 06:26 UTC (permalink / raw)
To: dougt@mugc.cc.monash.edu.au; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [INTERFACES] Front end memory consumption in SELECT
1999-11-20 01:10 Front end memory consumption in SELECT Douglas Thomson <dougt@mugc.cc.monash.edu.au>
1999-11-20 06:26 ` Re: [INTERFACES] Front end memory consumption in SELECT Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-11-21 06:14 ` Douglas Thomson <dougt@mugc.cc.monash.edu.au>
0 siblings, 0 replies; 3+ messages in thread
From: Douglas Thomson @ 1999-11-21 06:14 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: dougt@mugc.cc.monash.edu.au; pgsql-interfaces
Tom Lane <tgl@sss.pgh.pa.us> writes:
> 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.
Thanks for this suggestion, it has solved my problem very nicely.
In case any Perl interface users are reading this and interested,
this meant changing my old code that used to work like:
my $result = $conn->exec("SELECT * from big_table");
$result->resultStatus == PGRES_TUPLES_OK or die(...);
while (my @tuple = $result->fetchrow) {
...
}
to instead work like:
my $result = $conn->exec(
"DECLARE big_cursor CURSOR FOR SELECT * FROM big_table");
$result->resultStatus == PGRES_COMMAND_OK or die(...);
do {
$result = $conn->exec("FETCH 100 FROM big_cursor");
$result->resultStatus == PGRES_TUPLES_OK or die(...);
while (my @tuple = $result->fetchrow) {
...
}
} while ($result->ntuples == 100);
$conn->exec("CLOSE big_cursor"); # if not about to COMMIT anyway
which limits to 100 the number of tuples in memory at any one time.
I only found two cases that I could not handle this way: SELECT FOR
UPDATE (since PostgreSQL cursors are read-only) and selects done
outside of transactions. In my application both these exceptions only
affected quite small selections (usually just one tuple) so I was
able to revert to the non-cursor select with no problem.
For what it is worth, I measured no significant difference in the
total time taken to traverse the entire table, but the reduction in
memory consumption was dramatic. Note that fetching only a single
tuple at a time (instead of the 100 shown above) did take much
longer.
Hope this information helps someone,
Doug.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~1999-11-21 06:14 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-11-20 01:10 Front end memory consumption in SELECT Douglas Thomson <dougt@mugc.cc.monash.edu.au>
1999-11-20 06:26 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-11-21 06:14 ` Douglas Thomson <dougt@mugc.cc.monash.edu.au>
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