Re: OFFSET and LIMIT - performance

On Thu, 2007-06-28 at 11:36 -0700, David Wall wrote:
> > Network transmission costs alone would make the second way a loser.
> >
> > Large OFFSETs are pretty inefficient because the backend generates and
> > discards the rows internally ... but at least it never converts them to
> > external form or ships them to the client. Rows beyond the LIMIT are
> > not generated at all.
> >
> Some of this would depend on the query, too, I suspect, since an ORDER
> BY would require the entire result set to be determined, sorted and then
> the limit/offset could take place.

In 8.3 a LIMIT clause will be evaluated at the same time as ORDER BY, so
that the full sort cost is avoided. This means that queries with LIMIT
are more likely to return in constant time, whether you have no ORDER
BY, an ORDER BY on an index, or an ORDER BY with no index. So indexes
specifically to provide a fast ORDER BY/LIMIT are no longer required.
Courtesy of Greg Stark.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com