Re: How to get the total number of rows with a query - Mailing list pgsql-general

  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./
>>