How to get the total number of rows with a query "limit" ?

[krystoffff]

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

[Ron Johnson]

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: ro***********@cox.net |
| Jefferson, LA USA |
| |
| "Man, I'm pretty. Hoo Hah!" |
| Johnny Bravo |
+---------------------------------------------------------------+

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Franco Bruno Borghesi]

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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (FreeBSD)

iD8DBQA/Or2U21dVnhLsBV0RAsDhAJ0e+aku+RQHQR5/IRsJJzK1SuABWwCfZ4QN
eiZ35M8qfJeVNIW73hTrm3s=
=nARV
-----END PGP SIGNATURE-----

[Steve Worsley]

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match