Postgis ST_Intersects performance

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:

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.