Skip site navigation (1) Skip section navigation (2)

Re: select count(*) and limit

From: John DeSoi <desoi(at)pgedit(dot)com>
To: Verena Ruff <lists(at)triosolutions(dot)at>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: select count(*) and limit
Date: 2006-05-18 14:02:08
Message-ID: 333A65CD-8A90-4743-8594-8D5548507599@pgedit.com (view raw or flat)
Thread:
Lists: pgsql-novice
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


In response to

Responses

pgsql-novice by date

Next:From: JoeDate: 2006-05-18 14:23:12
Subject: Re: MySQL DB to PostgresSQL DB
Previous:From: Sean DavisDate: 2006-05-18 12:28:10
Subject: Re: select count(*) and limit

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group