Обсуждение: How to get the total number of rows with a query "limit" ?

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 ?

Has anybody an idea ?
Thanks for the help
Krystoffff

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.

--
+---------------------------------------------------------------+
| Ron Johnson, Jr.        Home: ron.l.johnson@cox.net           |
| Jefferson, LA  USA                                            |
|                                                               |
| "Man, I'm pretty.  Hoo Hah!"                                  |
|    Johnny Bravo                                               |
+---------------------------------------------------------------+

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.

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