Re: Large queries; fetchsize, cursors and limit/offset

From: Rod Taylor <rbt(at)rbt(dot)ca>
To: Dave Tenny <jeffrey(dot)tenny(at)comcast(dot)net>
Cc: David Wall <d(dot)wall(at)computer(dot)org>, pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Large queries; fetchsize, cursors and limit/offset
Date: 2003-11-03 23:15:05
Message-ID: 1067901305.79058.4.camel@jester
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

On Mon, 2003-11-03 at 17:53, Dave Tenny wrote:
> The LIMIT/OFFSET technique, last time I checked, is a poor choice for
> PostgreSQL

> In other words, it isn't a pragmatic solution for really large result sets.
> I'll be curious to hear other people's solutions here.

Cursors work in that you can ask for the first 100 rows, then the next
100, then the next 100.... It'll sit and wait for you to request the
next group.

The disadvantage is you need to get the same database connection over
and over for the same client. With web stuff that can be tricky.

If you can tie a db connection to a clients session (return it to the
pool when the client session expires) or have a single connection handle
all of these scroll type queries it becomes fairly painless.

With cursors that can cross transaction boundaries (7.4) it is just that
much easier.

In response to

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message David Wall 2003-11-03 23:49:56 Re: Large queries; fetchsize, cursors and limit/offset
Previous Message Dave Tenny 2003-11-03 22:53:49 Re: Large queries; fetchsize, cursors and limit/offset