[postgis-users] Run on database or not?

[Brian Modra]

2009/11/24 tommy408 <tommytomorow at msn.com>:
>
> I have a web application that requires a lot of queries, like 200 queries a
> second.  And they're mostly running ST_Within on large data.  Should I just
> load the data into memory to do the calculations ( I'm using java)  or keep
> them in postgres and run the queries?  I don't know if database already
> doing caching.

ST_within is a function, as far as I know, its not doing its own caching.
But the database does naturally cache. So if you do the same query
again, it won't have to do so much IO... and the gist index will
probably also be in cache.

You'll have to benchmark it yourself, because it depends a lot on your
data and query.
But caching and doing the calculations in memory using Java will
likely be slow anyway if the data is large, because Java is not great
for maths. Rather cache one complete result with its query data, and
then if a matching query comes in, you have the results already. But
let the database find the result in the first place rather than do the
maths in Java.

> --
> View this message in context: http://old.nabble.com/Run-on-database-or-not--tp26494921p26494921.html
> Sent from the PostGIS - User mailing list archive at Nabble.com.
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>



-- 
Brian Modra   Land line: +27 23 5411 462
Mobile: +27 79 69 77 082
5 Jan Louw Str, Prince Albert, 6930
Postal: P.O. Box 2, Prince Albert 6930
South Africa
http://www.zwartberg.com/