Indicator of ST_Intersection processing time

Question

Intersections on spatial datasets are slow. Inspired by this question intersection in postgis takes hours and hours to run, why? and Paul's post here, https://blog.cleverelephant.ca/2019/11/subdivide.html I made a little test to see if a dataset is going to be a problem. This query should return a value % of the how much of the area of interest that the largest polygon occupies. Presumably much of the dataset also has polygons of similar size.

SELECT concat(ROUND(MAX(ST_Area(Box2D(geom)))::NUMERIC / ST_Area(ST_Extent(geom))::NUMERIC*100,0),' %') AS max_feature
FROM spatial.table
;

If the dataset returns a large percent, there is going to be little benefit of the spatial index, reducing search efficiency. How can I also test for complexity (which presumably creates a processing cost)?. And ideally create some proxy indicator of processing time?

Answer 1

A few ideas:

• Like described in Paul's post, that's not really a problem of area of the polygons, but more the number of points of each polygons, so maybe you can try to look at the number of points and/or the number of polys ?
• Also the number of ovelapping polys should have an impact (10 poly at the same place means a request here will have at least to test the intersection with 10 polys)
• In fact the number of overlapping 2D box is more important, because it has a direct impact on the number of polys that will be tested. Each poly that have its 2D box intersect the 2D box of your geom will need to be checked, so you can say that the less useless 2D box intersection there is the better. There is a few ways to test that I think, like for exemple comparing the area of the poly with the area of its 2D box (the more squared the poly is the better)
• Lastly, sometimes I do that, you can test generating a random sample of points (or a grid) in your area (generate X and Y in the extent of your area, and keep the point if it's in the envelop of your table) and test some indicators, then you can make average, min and max, etc... of your indicator. In your case for example the indicator can be the sum of the number of points of all the polys that have their 2D box that intersects your points

Still, I'm not sure if you can say that any indicator you compute will always be directly corelated with the actual computation time, because there can be a lot of other things that have an impact, like for exemple the size of the table and the size of the cache (if everything can hold in memory, a lot of things are easier) and also projection if your data is in WSG84 and need to be projected to do the intersection.