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