query with results and also results size
От | Michael Swierczek |
---|---|
Тема | query with results and also results size |
Дата | |
Msg-id | AANLkTimq0Xhi4=zWcx1sTmG9e6_TWuoGMhcAbcfhEED6@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: query with results and also results size
|
Список | pgsql-novice |
I query a view based upon a number of user-input criteria. The results are displayed to the user 10 rows at a time. In my page view for the end user, I want to list "search results, records X through X + 9 out of (total)". Right now every time a person clicks through a page, the page request results in two queries. One query gets the count of the total number of records that meet the search criteria. The second query gets the 10 records for the current page. I want to reduce the number of round trips to the database, and if possible stop from performing the search twice. I can combine the two queries like this: select first.*, second.total_count (select * from patient_data_view where (...) order by ... offset _ limit 10) first join (select count(id) as "total_count" from patient_data_view where (...)) second on true; Doing it that way moves from two trips between the application and the database to one, but that one query still has two searches based upon the same criteria in it. I cannot store the count associated with each search between page views, because there are other people accessing the system concurrently and the number of records meeting the search criteria can change. Is there a way to write the query so that it does not perform the search twice? Thanks, -Mike
В списке pgsql-novice по дате отправления: