Re: Parsing speed (was Re: pgstats_initstats() cost)

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > You probably know but I'll quickly outline it to point out the
> > differences, as I see them, from the 'COPY' ability. Basically the user
> > defines their own C structure and then malloc's an array of them. The
> > user then tells the database the type, offset from start of structure
> > and the skip (size of structure) for each column returned by the select
> > statement. The user can then do 'bulk' grabs with a single command into
> > the memory space allocated, doing more than one and changing the offsets
> > inbetween if more is returned than was initially allocated for. The
> > user can realloc or allocate new segments and do their own handling of
> > the segments if they choose.
>
> [shrug] That seems like a substantial increase in API complexity for
> at best marginal performance gains. What does it gain for the user to
> malloc space rather than libpq?

Perhaps I'm just used to dealing with very large selects.. When
dealing with Oracle it's been my experiance that it doesn't grab the
entire select return and store in local memory for the user to puruse
using the appropriate calls. It grabs a portion and stores it in a
local cache and then gets more from the server periodically as the user
requests more. This avoids having the library malloc'ing a very large
amount of memory when a very large query is done. Doing the 'bulk'
transfer avoids the cache entirely and just dumps the data from the
server into the user's allocated memory area. The user can then step
through it, gain what they need from it, throw it out and get the next
batch. If libpq grabs the entire result in one go then that may
actually cause a problem for me when I start to move things from Oracle
to postgres since the clients don't always have much memory available.

Otherwise it would just avoid the function calls to libpq in the loop
which passes over the data. That probably wouldn't be that much of a
gain if libpq has all the data local though.

Stephen