Postgis ST_Intersects performance

Question

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

SELECT 
        * 
FROM 
        data.polygons AS poly, 
        data.gig_polygon AS gig
WHERE
        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?

Answer 1

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

Answer 2

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:

SELECT
    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:

WITH poly AS (SELECT
    st_subdivide(geom) geom
    FROM g973.ugebiet)
SELECT
    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.