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"

2 Answers 2


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).

  • 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. Commented Dec 20, 2012 at 9:57
  • ?? 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
    – Luffydude
    Commented May 17, 2017 at 10:10

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.

  • 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) Commented Dec 20, 2012 at 10:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.