Skip site navigation (1) Skip section navigation (2)

Front end memory consumption in SELECT

From: Douglas Thomson <dougt(at)mugc(dot)cc(dot)monash(dot)edu(dot)au>
To: pgsql-interfaces(at)postgreSQL(dot)org
Subject: Front end memory consumption in SELECT
Date: 1999-11-20 01:10:27
Message-ID: 199911200110.MAA09758@mugca.cc.monash.edu.au (view raw or flat)
Thread:
Lists: 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...

pgsql-interfaces by date

Next:From: Sergio A. KesslerDate: 1999-11-20 03:18:58
Subject: Re: [INTERFACES] pg_pwd
Previous:From: Mads PultzDate: 1999-11-19 23:21:39
Subject: JDBC compliancy question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group