I have a spatial table in a PostgreSQL/PostGIS database. Every row in it represents a Polygon. It is of following form:
+----+--------+ |gid | way | +----+--------+ |241 | 01030..|
The geometric column is "way" which contains the geometry for a polygon. In WKT it is: POLYGON(('....')). I am doing a lot of ST_Contains queries on this table, to test whether two polygons are contained in each other e.g.:
Select ST_Contains(a.way, b.way) From table AS a, table AS b Where a.gid = 15 And b.gid = 16
I was wondering how to speed up this query and added a spatial index on the table:
CREATE INDEX table_way_gist ON table USING gist(way);
But actually I don't see a speed up. I create the index AFTER I filled the table with all polygons BEFORE I do the ST_Contains queries. Should the index be added before filling a table? Are there special requirements on the table to work with the index? The projection (srid) of the geometric column way is set to 900913.
I am using: psql (PostgreSQL) 9.1.4 / POSTGIS="1.5.3"