[postgis-users] Improve perfomanse

[Mark Cave-Ayland]

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-
> bounces at postgis.refractions.net] On Behalf Of ???? ???????
> Sent: 28 November 2005 13:13
> To: postgis-users at postgis.refractions.net
> Subject: [postgis-users] Improve perfomanse
> 
> There is a table:
> 
> CREATE TABLE metadata ( ID int4 );
> SELECT AddGeometryColumn('','metadata','geom',-1,'MULTIPOLYGON',2);
> CREATE INDEX idx_metrics ON metadata USING GIST (geom GIST_GEOMETRY_OPS);
> 
> There are 300000 objects in this table and each object has only four
> points.
> I want to find all objects within a another object. The another object has
> 11538 points.
> 
> SQL:
> SELECT *
> FROM metadata
> WHERE 'MULTIPOLIGON(((another object)))'~ geom AND
>         CONTAINS('MULTIPOLIGON(((another object)))', geom);
> 
> Run time: 3 hours!!! P4 2.4 GHz; 1Gb RAM;
> 
> Are there any methods to improve perfomanse in that case?


Hi Ilyakon,

Firstly have you tuned your PostgreSQL installation for "shared_buffers"
according to the PostgreSQL docs
(http://www.postgresql.org/docs/8.0/interactive/runtime-config.html)? By
default PostgreSQL will only use 8Mb of RAM for disk buffers which is too
low for most applications.

Secondly, have you run an ANALYZE/SELECT update_geometry_stats() so that
PostgreSQL can determine which indices can use on the spatial data? (see
http://postgis.refractions.net/docs/ch04.html#id2511705)

If after trying these things you are still experiencing problems then please
post the EXPLAIN ANALYZE of the query to the list, along with information
about which versions of PostgreSQL/PostGIS you are using.


Kind regards,

Mark.

------------------------
WebBased Ltd
17 Research Way
Plymouth
PL6 8BT

T: +44 (0)1752 797131
F: +44 (0)1752 791023

http://www.webbased.co.uk   
http://www.infomapper.com
http://www.swtc.co.uk  

This email and any attachments are confidential to the intended recipient
and may also be privileged. If you are not the intended recipient please
delete it from your system and notify the sender. You should not copy it or
use it for any purpose nor disclose or distribute its contents to any other
person.