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

Re: Large selects handled inefficiently?

From: Jules Bean <jules(at)jellybean(dot)co(dot)uk>
To: Chris <chrisb(at)nimrod(dot)itg(dot)telstra(dot)com(dot)au>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Large selects handled inefficiently?
Date: 2000-08-31 08:58:34
Message-ID: 20000831095833.A24680@grommit.office.vi.net (view raw or flat)
Thread:
Lists: pgsql-general
On Thu, Aug 31, 2000 at 03:28:14PM +1100, Chris wrote:
> Jules Bean wrote:
> > 
> > On Thu, Aug 31, 2000 at 12:22:36AM +1000, Andrew Snow wrote:
> > >
> > > > I believe I can work around this problem using cursors (although I
> > > > don't know how well DBD::Pg copes with cursors).  However, that
> > > > doesn't seem right -- cursors should be needed to fetch a large query
> > > > without having it all in memory at once...
> > >
> > > Actually, I think thats why cursors were invented in the first place ;-)  A
> > > cursor is what you are using if you're not fetching all the results of a
> > > query.
> > 
> > I really can't agree with you there.
> > 
> > A cursor is another slightly foolish SQL hack.
> 
> Not quite, 

All right ;)  Can we say that, like some other SQL features of which
I'm not fond (e.g. NULLs), cursors do have a use, but many ways in
which they're often used in practice are bad practice...

> but it is true that this is a flaw in postgres. It has been
> discussed on hackers from time to time about implementing a "streaming"
> interface. This means that the client doesn't absorb all the results
> before allowing access to the results. You can start processing results
> as and when they become available by blocking in the client. The main
> changes would be to the libpq client library, but there would be also
> other issues to address like what happens if an error happens half way
> through. In short, I'm sure this will be fixed at some stage, but for
> now cursors is the only real answer.

Or ...LIMIT...OFFSET, I guess. [As long as I remember to set the
transaction isolation to serializable.  *sigh*  Why isn't that the
default?]

I shall investigate whether LIMIT...OFFSET or cursors seems to be
better for my application.

Jules



In response to

Responses

pgsql-general by date

Next:From: Jules BeanDate: 2000-08-31 09:02:54
Subject: Re: Error with tcp/ip networking
Previous:From: Yury DonDate: 2000-08-31 08:55:05
Subject: Re: trigger, how to determine action calling?

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