PostgreSQL/PostGIS spatial index - no speed up

Question

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"

Answer 1

The most efficient index for the query expressed in your question is the one on gid as it is the only column that appears in a where expression:

 CREATE INDEX table_gid ON table (gid);

You can safely drop the gist index as it will only consume space and slow inserts/updates/deletes down.

Long explanation

As I said the most effective index in your case is the one on gid as it will allow the db engine to retrieve rows faster (with retrieval usually being the slowest part of the process). After that it will probably better compute the result of the

 ST_Contains(a.way, b.way)

espression without looking at the index. The reason is that the query planner will likely estimate that the extra cost of looking up the gist index on both columns versus looking up the a.way and b.way values directly is not worth the effort as the total number of rows to look up is probably very small especially if the index is unique.

As a rule of thumb remember that the planner will probably favor a table scan over an index scan for small datasets (dataset sizes are estimated by looking at the table statistics).

• MichiMichbeck: That makes the issue more clear to me. I will try it. So if I put the ST_Contains() query into the WHERE clause, the spatial index should actually be helpful? I think I have to reorganize my script to call ST_Contains within WHERE clause. At the moment I am iterating through all polygons and always test two of them seperately.
• Luffydude: ?? you're sying that a spatial index slows things down? That's a new for me because where I work we have spatial indexes for every single table and I wonder if it's a bad practice

Answer 2

As unicoletti said, the gist index in the geometry column would only work if you use ST_Contains() in the WHERE expression.

For instance, if you would like to know all polygons that contain one another, you could use something like this:

SELECT a.gid, b.gid
FROM table AS a, table as b
WHERE a.gid != b.gid and ST_Contains(a.way, b.way)

In this case, depending on the size of your table and the complexity of your geometries, the gist index should provide a significant speed up, as ST_Contains will start by filtering the polygons by comparing their boundary boxes before actually check their complete geometries. You can see a small explanation in the OpenGeo Tutorial.

• MichiMichbeck: Yes I see, I need this query to involve the index boundary test. Thx Alexandre. (I'll mark unicoletti as solution, as he was fast and cleared the issue to me)