[postgis-users] Improve perfomanse

[Ries van Twisk]

Mark Cave-Ayland wrote:

>>-----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?
>>    
>>
>
>  
>
Try a query that looks like this:
SELECT P.*,distance(P.wkb_geometry, A.wkb_geometry) FROM 
"polbnda at bnd(*)_area" A, "mispopp at pop(*)_point" P WHERE A.ogc_fid = 
22245 AND distance(P.wkb_geometry, Simplify(A.wkb_geometry, 
10))::integer = 0

A.ogc_fid is a area I am interested in and P is a point table so I am 
trying to find out all point's within a area.
Now when the area is complex (lot's of points on my polygon) then it 
takes quite a long time.
When the area has not to much points then this function should be 
resonable fast. Try it, see what happens...


However, I do want to optimize this query for me by doing a tree step 
process in plgsql.
1) First get the extent of the area by creating a square.
2) Find all points within my square area.
3) Remove all point's from my resulting poin't cloud by checking them to 
my complex area.

Does that sounds like optimizing in  my case when I have comples area's?


Cheers, Ries

>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.
>
>
>_______________________________________________
>postgis-users mailing list
>postgis-users at postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-users
>  
>