[postgis-users] slow sql queries in postgis

[Greg Williamson]

> 
> Hello Everyone !
> 
> I have a postgis table containing mangrove data with 5 fields and 950,000 
> records. I am accessing the table using hibernate spatial in a web gis 
> application.  I have done the following optimizations:
> 
> - Ran vacuum analyze.
> 
> - Clustering.
> 
> - Increased shared buffer from 32mb to 128mb.
> 
> 
> Inspite of this optimizations, the sql queries accessing the mangrove data are 
> very slow.
> 
> I will like to ask what could be the performance problem ?
> 
> My server specs are as follows:
> 
> Processor speed: 1.87ghz. 
> Processor type: Intel Xenon.
> RAM: 2GB.
> 
> 
As as has been noted these specs are on the low side -- things will work but slowly, since the system will need to be using disk I/O more frequently.

Try to capture the long-running queries and then run them with "EXPLAIN ANALYZE ..."; this will do the actual operations so you may want to wrap this in a transaction so you can rollit back. EXPLAIN ANALYZE will show you the actual and expected values for estimates and details of the plan used; post those back here along with table definitions, This might help spot  missing indexes and the like.

Hibernate has been seen doing some poor SQL in our company -- CROSS JOINS that were killing performance, for example. It may be generating poor queries for you too; make sure to examine them for sanity.

postGIS with large or complicated queries can be slow if things have to get reprojected on the fly, if there are lots of tests to be applied to lots of points it just takes time. 

HTH,

Greg Williamson

_______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>