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.
http://lucene.apache.org/solr/
Thanks,
____________________________________________________________________
Brendan Duddridge | CTO | 403-277-5591 x24 | bren...@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
