[postgis-users] speeding up a postgis/postgres application

Dave Potts dave.potts at pinan.co.uk
Tue Aug 14 00:28:37 PDT 2012

I have java/hibernate/postgis application, running it, it takes about 20
mins to generate a result.

Running a system monitor on it,  I can see 2 of the 8 cpu spiking at 100%.

A strace indicates a lot of recv/send network traffic for this application.

The memory foot print is a constant

With out wanting to do a rewrite, is there any millage to telling this
application to use local connection to the database so that I avoid going
through the network stack?

Would increasing any of the memory limits in a standard Postgres set up help?

I have already rewritten all of the nearest neighbours of the queries that
say something like

select  blah blah order by distance

with calls to st_dwithin

I use bounding boxes where possible to limit the generated results.

All input  geometries have been  simplified where possible.

I am wondering if its worth caching some of the results of the select
statements in a hash table to avoid the costs of lookups.

Any suggestions greatfuly received?



More information about the postgis-users mailing list