count and limit

Fabio Victora Hecht wrote:

Hi!

I've been using Postgresql for a while now and I'm very satisfied.

I was wondering if there's a way to count the results of a query and
return part of the result set it in one query (LIMIT). Because I usually
have to count the results using one query (to tell the user how many
records match the criteria) and show the first 20 records.

I was going to suggest a cursor but I don't think you can get the number
of results a cursor has :(

--
Postgresql & php tutorials
http://www.designmagick.com/

Chris <dmagick@gmail.com> writes:

Fabio Victora Hecht wrote:

I was wondering if there's a way to count the results of a query and
return part of the result set it in one query (LIMIT).

I was going to suggest a cursor but I don't think you can get the number
of results a cursor has :(

In general it's not possible to determine the number of rows a query
will return without actually executing it to completion.

You could use a cursor like this:

begin;
declare c cursor for select ... ;
fetch 20 from c;
-- display the first 20 results
move forward all in c;
-- note the count returned by MOVE, add 20 to get the total

but if you're expecting the MOVE to be instantaneous, prepare to be
disappointed.

If you're willing to settle for a (very) approximate count, there are
things you could do. Some people just feed the query to EXPLAIN and
grab the rowcount estimate out of the first line of output.

regards, tom lane

On Fri, Aug 18, 2006 at 10:34:44AM +1000, Chris wrote:

Fabio Victora Hecht wrote:

I was wondering if there's a way to count the results of a query and
return part of the result set it in one query (LIMIT). Because I usually
have to count the results using one query (to tell the user how many
records match the criteria) and show the first 20 records.

I was going to suggest a cursor but I don't think you can get the number
of results a cursor has :(

You can but you have to MOVE to the end, so the backend has to visit
each tuple just as it would for COUNT. I answered a similar question
recently:

http://archives.postgresql.org/pgsql-novice/2006-07/msg00220.php

--
Michael Fuhr

Tom Lane wrote:

Chris <dmagick@gmail.com> writes:

Fabio Victora Hecht wrote:

I was wondering if there's a way to count the results of a query and
return part of the result set it in one query (LIMIT).

I was going to suggest a cursor but I don't think you can get the number
of results a cursor has :(

In general it's not possible to determine the number of rows a query
will return without actually executing it to completion.

You could use a cursor like this:

begin;
declare c cursor for select ... ;
fetch 20 from c;
-- display the first 20 results
move forward all in c;
-- note the count returned by MOVE, add 20 to get the total

but if you're expecting the MOVE to be instantaneous, prepare to be
disappointed.

If you're willing to settle for a (very) approximate count, there are
things you could do. Some people just feed the query to EXPLAIN and
grab the rowcount estimate out of the first line of output.

Interesting idea :) I wasn't sure if cursor did the whole query and
(somehow) stored it in memory.. obviously not (and of course that would
blow your memory usage out of the water for big result sets so there
goes that idea anyway!)..

--
Postgresql & php tutorials
http://www.designmagick.com/

Michael Fuhr wrote:

On Fri, Aug 18, 2006 at 10:34:44AM +1000, Chris wrote:

Fabio Victora Hecht wrote:

I was wondering if there's a way to count the results of a query and
return part of the result set it in one query (LIMIT). Because I usually
have to count the results using one query (to tell the user how many
records match the criteria) and show the first 20 records.

I was going to suggest a cursor but I don't think you can get the number
of results a cursor has :(

You can but you have to MOVE to the end, so the backend has to visit
each tuple just as it would for COUNT. I answered a similar question
recently:

http://archives.postgresql.org/pgsql-novice/2006-07/msg00220.php

Thanks for the pointer :)

I do a lot of those type of things too so this may be a way to handle it
better.

--
Postgresql & php tutorials
http://www.designmagick.com/

Ok, so I think the best that can be done is to EXPLAIN the query and check
if it's gonna take long. If it is, I could use the estimated count.

Thanks,

Fabio