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

The LIMIT/OFFSET technique, last time I checked, is a poor choice for
PostgreSQL
if that's your only way to filter the result set. I did some performance
work some versions back and discovered that the server must still process
all those leading rows before offset (ultimately discarding them), and
as you increase the offsets
you're just reprocessing the same rows in increasing numbers over and over.

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.

David Wall wrote:

>What are most people doing to handle queries with large result sets. By
>large, I mean results that have perhaps 200 or more rows and in which having
>the full resultset would likely be a performance and memory issue. Often,
>we have queries that could return thousands of rows, especially those
>performed by batch processing programs/threads.
>
>It seems that the setFetchSize() is not well implemented across JDBC
>platforms, and that the LIMIT/OFFSET combo, while workable despite the
>potential for overhead on the database (since it returns only a subset, yet
>the entire query must be evaluated) is not implemented everywhere, including
>Oracle 8i if I'm not mistaken.
>
>The other option appears to be to use cursors, though I'm not sure how well
>that's supported across JDBC drivers either. For sure, the syntax for
>declaring and using a cursor vary among different database, though I can
>work around that pretty easily.
>
>What are most people doing these days for handling large queries, especially
>those that have to support not only PostgreSQL, but also Oracle, DB2/UDB
>and/or MSFT SQLServer?
>
>Thanks,
>David
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>
>