Re: select count(*) and limit

On May 18, 2006, at 8:22 AM, Verena Ruff wrote:

>> An
>> alternative to using the LIMIT clause is to use a cursor, but the
>> ability to
>> do so depends on the environment in which you are working. In a web
>> environment, cursors are not useful given the stateless nature of
>> the web
>> interface. Cursors are explained in the Docs.
>>
> The queries are for a webpage, so coursers won't be usefull.

A cursor still might be useful, but it would only be used for the
current request. You could run the query once and fetch the rows you
want to display from the cursor. Then scan to the end of the cursor
to find out how many rows it has. The MOVE command does this and
returns the number of rows.

You would have to test it, but my guess is this would be faster than
executing the same query twice for the two results you are looking for.

Alternatively, you might skip calculating the true count unless the
user clicks on a separate link. This option could show X rows from
the end of the result set and the count(*) result.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL