On 3 April 2014 15:34, Leonardo M. Ramé <l.rame@xxxxxxxxxxx> wrote: > Hi, in one of our systems, we added a kind of pagination feature, that > shows N records of Total records. > > To do this, we added a "count(*) over() as Total" field in our queries > in replacement of doing two queries, one for fetching the records, and > other for getting the count. This improved the performance, but we are't > happy with the results yet, by removing the count, the query takes > 200ms vs 2000ms with it. > > We are thinking of removing the count, but if we do that, the system > will lack an interesting feature. > > What strategy for showing the total number of records returned do you > recommend?. Assuming your results are unique (and what would be the point of showing duplicate results?) in a specific order, it should be possible to (uniquely) identify the last record shown on a previous page and display n results from there on. To add a result counter for displaying purposes to that, since you need to remember the last displayed result anyway, you might as well store the relative record number of that result with it and count from there on. This works well if you only have a next/previous results link, but not so well when people can pick arbitrary page numbers. It can work relative to the current page regardless of which page the user chose to navigate to next, but you'll have to go through all the results in between that page and the current page... That should still be faster than always counting from the start though (and you can be smart about from which end you start counting). -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
