You're not going to get anywhere using Postgres for this kind of task. Especially if you have millions of products like we do in our database.
We switched to using Solr for our search indexing and querying. It's way faster than Postgres for obtaining counts like you need. We still fetch the full product info from our Postgres database, but for doing the searches and determining the counts of products given the search criteria, we found Solr to be way faster. It's also Java based, so you're golden there I think.
Thanks,
____________________________________________________________________
Brendan Duddridge | CTO | 403-277-5591 x24 | brendan@clickspace.com
ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB T2G 0V9
http://www.clickspace.com On Jan 16, 2009, at 10:41 AM, Mohamed wrote:
Hi, this is basically what I would like to improve :
1) A user searches for a product on category and location.
a) The query is run and the result (limit 30) are returned and shown.
b) The same query is ran again but now I return the count on how many matches there was totally. (This has to be possible to include somehow in the above, without the limit it would return all rows and therefor the number of matches as well)
c) The same query is ran again but now I return the count on how many matches there was in each location
c) --- " " --- count on category.
d) --- " " --- count on how many that was used or new.
etc...
How can I improve this? Seems so unnecessary to have to run the query on every count I want to do when its the same query hitting the same relation with a slight modification in each? Is there a way or should I just forget about it and run them over and over again? I am guessing using functions could be possible but now so little about them and how to run them from Java. There might be 1000 or more users on the website simultaneously and I am guessing this could be a real downside...
Thanks / Moe
