Re: How to get the total number of rows with a query
От | Steve Worsley |
---|---|
Тема | Re: How to get the total number of rows with a query |
Дата | |
Msg-id | 3F3BACBF.2010403@commandprompt.com обсуждение исходный текст |
Ответ на | Re: How to get the total number of rows with a query (Franco Bruno Borghesi <franco@akyasociados.com.ar>) |
Список | 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./ >>
В списке pgsql-general по дате отправления: