Re: [GENERAL] Count for pagination
Any suggestions on how to get the count of all records that could be returned We use a window function to get the total # of records within each of our paginated queries: SELECT ... ,COUNT(*) OVER() fullRowCount FROM ... WHERE ... ORDER BY ... LIMIT ... OFFSET ...; While there is a cost to using the window function, it's faster (for us) than two separate queries, and, more importantly, it's flexible enough to work in the 100s of different query contexts we have. /mcr -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Count for pagination
The main search screen of my application has pagination. I am basically running 3 queries with the same where clause. 1. Totals for the entire results(not just the number of rows on the first page) a. 300 ms 2. Subset of the total records on that page. a. 1-2 sec 3. Count of the total records for the pagination to show the number of pages a. 1-2 sec The queries are generated by Hibernate and I am looking to rewrite them in native SQL to improve performance. Any suggestions on how to get the count of all records that could be returned and only a subset of those records for that page in an optimized fashion? I have no problem using a widow query or a Postgres specific feature as my app only runs on Postgres. -- Thank you for your time, Jason Long CEO and Chief Software Engineer BS Physics, MS Chemical Engineering http://www.octgsoftware.com HJBug Founder and President http://www.hjbug.com
[GENERAL] Count for pagination
The main search screen of my application has pagination. I am basically running 3 queries with the same where clause. 1. Totals for the entire results(not just the number of rows on the first page) a. 300 ms 2. Subset of the total records for one page. a. 1-2 sec 3. Count of the total records for the pagination to show the number of pages a. 1-2 sec The queries are generated by Hibernate and I am looking to rewrite them in native SQL to improve performance. Any suggestions on how to get the count of all records that could be returned and only a subset of those records for that page in an optimized fashion? I have no problem using a widow query or a Postgres specific feature as my app only runs on Postgres. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Count for pagination
* Jason Long (ja...@octgsoftware.com) wrote: The main search screen of my application has pagination. http://www.depesz.com/index.php/2007/08/29/better-results-paging-in-postgresql-82/ Thanks, Stephen signature.asc Description: Digital signature
