Thread: How to get the total number of rows with a query "limit" ?
How to get the total number of rows with a query "limit" ?
From
proghome@silesky.com (krystoffff)
Date:
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:
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.
Attachment
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./ >>