I am selecting a lot of polygons within a gigantic polygon by using the postgis function ST_Intersects().

        data.polygons AS poly, 
        data.gig_polygon AS gig
        ST_Intersects(poly.geom, gig.geom)

The gigantic polygon is just a (one row) large sized polygon at the moment. The query is taking a very long time. I was wondering if it would be faster to split the gigantic polygon in multiple smaller polygons and run the query again. I dont want to cancel it without knowing this will be faster. To know the answer to my question I guess you have to understand in what way the ST_Intersects() function works.

Does anybody know that? Or knows another way of speeding up the ST_Intersect() functionality?

  • To me your query looks like a massive join on a geometry field rather than comparison against a single Gigantic polygon. Such joins are unfortunately rather slow since there is a lot of calculations involved. Are you quite sure there souldn't be a filter on gig_polygon?
    – e4c5
    Commented Oct 7, 2015 at 0:02
  • Yes, it will be faster, I've done a similar task not so long ago and realized PostGIS works better with lots of small features rather than few big ones. You can read more on my blog. Commented Oct 9, 2015 at 16:31
  • 1
    The other thing that slows down Intersects() is if the polygons are complex (i.e., have many vertices). Even with indexes, ST_Intersects() is slow when comparing polygons where one or more of them has a large number of vertices; I routinely check ST_Intersects() between rectangular cadastre polygons (with 4 vertices) against zoning polygons (with sometimes upwards of 40,000 vertices per zoning polygon, and over a thousand polygons in the state). That's the nature of the comparison being performed, unfortunately.
    – GT.
    Commented May 20, 2016 at 6:07

2 Answers 2


It could be faster, you're right but what you really need if you don't already have one is a spatial index. This will then be able to do a first pass of your query quickly by working out which bits of the data the query needs to look it. It will then check these data points for which fall exactly within the polygon.

You can create an index using information here - it also gives an overview of spatial indexing.

The reason it might be faster if you break up the large polygon is that if, say your polygon is long and thin, the bounding box (which the index uses) might not cover much of the actual area of the polygon, so you will get a lot of false positives from the index. Equally if your polygon covers most of your other dataset anyway, the index will not be much use. This might be more appropritate on gis.stackexchange.com


I have a similar task: intersecting lots of linestrings with a complex polygon. I just experienced a huge performance benefit from splitting the polygon. This is very easy using st_subdivide().

Previous query, runtime 22:43 minutes:

    sum(st_length(st_intersection(netw.geom, poly.geom))) len
    FROM g973.xglnetzg netw LEFT JOIN g973.ugebiet poly ON st_intersects(netw.geom, poly.geom);

New query, same result in 26,692 seconds:

    st_subdivide(geom) geom
    FROM g973.ugebiet)
    sum(st_length(st_intersection(netw.geom, poly.geom))) len
    FROM g973.xglnetzg netw LEFT JOIN poly ON st_intersects(netw.geom, poly.geom);

Where the polygon is the boundary of Tirol/Austria from OSM with 3 outer rings and 47.264 vertices and network is a road network with 184.203 edges and up to 1.046 vertices per edge. Both layers have spatial indizes. 150.822 edges match the polygon per &&, 114.064 per st_intersects().

I guess that query could be optimized even more, but for now I feel lucky.

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.