With out knowing how much memory for each of those settings and how much work_mem for each connection its kinda hard to tell what is going.
Also need version for PG, OS, how big the tables are, Also would be nice to see the query itself with explain and analyze
PG does not cache the results from a query but the tables itself.
The table could be completely cached but there may be some nasty Nested loops causing the problem.
What are you expecting the query time to be??
check out http://wiki.postgresql.org/wiki/Performance_Optimization there is allot of info on how to tune, and diagnose problem queries
---- Message from Peter Alban <peter.alban2@xxxxxxxxx> at 06-21-2009 12:54:40 PM ------
Hey folks !
Still kind of analyzing the situation , I realized that I do have a reasonably high shared_memory and effective_cache_size , though if the same query is being run in a number of times ~100-200 concurrent connection it is not being cached .
Should PG realize that if the table data is same should the query result set also be the same ? Instead each query takes up to 1-2 seconds .
Where do I see what the PG does ? I can see now the query's that take long time ,but do not have information about what the optimizer does neither when the DB decides about to table scan or cache ?
cheers,
Peter
