Re: How to get the total number of rows with a query

Hmmm.... Processing the entire query as such would make the entire
query take longer, at least in my experience. I ran into the same
problem, since count() is an aggregate function you cant actually get it
with the data without putting it in every row. You _can_ do a subselect
which select the count in the same query... On my test case this ran
pretty quick in only 1 query. However, simply running a count at the
top of the page, and then executing the limited query should be very
fast. I took a system with around 115k entries in it (and it was
displaying them all !) and paginated it using LIMIT 40 OFFSET X. The
performance went from approx. 1 min to load to page, to loading in less
than a second. All math operations are handled by the DB, even.
However, this APP is written in LXP, so I'm not sure what the
performance difference there would be.

-Steve

My test case was:

mydb=# SELECT username, (SELECT count(username) AS count FROM users) AS
count FROM users ORDER BY username LIMIT 10 OFFSET 10;
username | count
------------+-------
a | 5678
a96larol | 5678
aaguiar | 5678
aaguy | 5678
aahash | 5678
aalalji | 5678
aamirwahid | 5678
aanaya | 5678
aaniceto | 5678
aapala | 5678

Franco Bruno Borghesi wrote:

> Or maybe you could just execute the full query (no limit, no offset),
> and you can get the whole row count using PQntuples (C), pg_num_rows
> (php), etc.
>
> When you iterate the resultset to show the rows, you just show the
> rows that belong to the showed page, and skip the rest.
>
> On Wed, 2003-08-13 at 18:53, Ron Johnson wrote:
>
>>/On Wed, 2003-08-13 at 08:43, krystoffff wrote:
>>> Hi
>>>
>>> I would like to paginate the results of a query on several pages. So I
>>> use a query with a limit X offset Y to display X results on a page,
>>> ok.
>>>
>>> But for the first page, I need to run the same query with a count(*)
>>> to know how many pages I will get (number total of rows/ X).
>>>
>>> The problem is my query is very slow (maybe 5s) because there is much
>>> worch to do, and on the first page, I need to run this query twice
>>> (not exactly, but ...) so the page is very very slow to load.
>>>
>>> My question is : is there a function to get the total number of rows
>>> even on a query with "limit" ? Or what could I do else ?
>>
>>Presuming that this is your own app, and not psql, why not suck the
>>result set into a doubly linked list (or dynamic list, if you use
>>Python, Perl, etc)?
>>
>>There's also the possibility of "chunked buffers", where you malloc,
>>say, 8KB before the query runs, and when that gets full, realloc
>>to add more space, and continue until the query completes./
>>