[postgis-users] Improve perfomanse

Илья Конюхов ilyakon at mail.ru
Mon Nov 28 05:13:16 PST 2005


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?








More information about the postgis-users mailing list