In response to Neil Saunders <n.j.saund...@gmail.com>: > Hi all, > > I maintain an online property rental application. The main focus of the UI > is the search engine, which I'd now like to improve by allowing filtering of > the search results shown on some criteria, but provide a count of the number > of properties that meet that criteria. > > For example, we're looking all properties, no criteria. I'd like to show > something like: > > Bedrooms: > 1 Bedroom (122) > 2 Bedrooms (143) > 3 Bedrooms (88) > > Facilities > BBQ (232) > Pool (122) > > ...and so on. My question is simple - What's the best way to implement this > - Do I literally have to execute a count for the WHERE criteria with the > filter criteria tagged on, or is there some clever trick that I'm not aware > of? I'd rather not count in the application as I'd like to plan for the day > we have up to 100k properties ( > > Any suggestions gratefully received!
In addition to the other excellent suggestions, you can provide estimates at a very small cost by using explain: EXPLAIN SELECT count(*) FROM properties WHERE bedrooms=3; Then parse the explain output to get the estimated # of rows. Very low overhead, but of course it's only an estimate. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
