Date: Sat, 19 Oct 2002 18:47:25 +0300
From: am@fx.ro
To: pgsql-interfaces@postgresql.org
Subject: Re: Handling data in libpq
Message-ID: <20021019184725.A313@coto>
References: <80bf0666.0210170015.59e12eed@posting.google.com>
	<20021019132352.A419@coto> <20021019110135.GX11228@xs4all.nl>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.2.5i
In-Reply-To: <20021019110135.GX11228@xs4all.nl>;
	from jtv@xs4all.nl on Sat, Oct 19, 2002 at 01:01:36PM +0200

First of all, thanks for your answer.

On Sat, Oct 19, 2002 at 01:01:36PM +0200, Jeroen T. Vermeulen wrote:
> On Sat, Oct 19, 2002 at 01:23:52PM +0300, am@fx.ro wrote:
> 
> > I'm looking for a new approach regarding fetching the data.
> > Usage of cursors is probably the path to follow. I would be deeply 
> > grateful to anyone who would share a bit of his/her experience:
> > 
> > - i assume the table has 50000 tuples
> > - what is better:  to fetch one tuple at one time, or to fetch
> >   a certain number of tuples (let's say 100).
> 
> Obviously there is some query and communications overhead associated with
> fetching anything, regardless of size, and the odds are it's going to be 
> larger than the cost of sending a tuple of actual data across.  So if
> you're going to pay that overhead anyway, you'll want to get some decent
> amount of data for it.

Hmm.. Good point.  
So, you're saying that fetching one tuple at one time is out of discussion. 

> However, that also implies that fetching 500 blocks of 100 tuples each is 
> always going to be slower than getting 50000 tuples in one go. The two
> advantages of fetching smaller blocks are: (1) you can start processing
> some data right away, which might be useful for e.g. user interaction
> (or you can use asynchronous communication so you can process your last
> block of data while fetching the next); 

Well, the program doesn't do automatically data processing: 
it only allows the user do see and modify the data.

>and (2) you may be running out of
> memory when fetching and processing 50000 tuples at once.  Swapping can
> be a real drag on performance.

There is also another aspect: usually the user will not
browse all of those tuples ( the program allows the user to specify
custom WHERE and ORDER BY clauses, so that he/she can select only 
the interesting rows ; and only sometimes will scroll all tuples ).

The conclusion is that the number of rows fetched at one time 
should be chosen carefully. 

> > - if i fetch 100 tuples, PQntuples returns 100? how do i know how
> >   many rows has the table?  
> 
> Well, if you assume your table has 50000 rows, why do you need to ask?

This assumption is only for that table. I want that my program
works well even for larger tables ( most of the program's classes
and functions are independent from the actual table, and will be 
moved to a library for later use).

> Seriously though, you don't get this information.  But if an estimate
> is good enough, you can "select count(*) from table" before you start
> fetching data.

I see: the returned number is exact as long as another user doesn't 
add/delete tuples after the 'select count(*)' and before the fetch.
 
I thought there was a way to find out how many tuples would return
a FETCH ALL, without executing that command.
 
> > - i have the impression that keeping track of the tuple's index i
> >   nside the RecordSet and the index inside the whole table is quite 
> >   tricky, but manageable. How do you guys handle this kind of situations?
> 
> It's not so hard.  Just an extra nested loop.

Adrian Maier
(am@fx.ro)