[postgis-users] ST_Intersection very slow

[Mark Wynter]

> Thanks Mark.  I will indeed do the st_dump, but I don't think it will help much (very few multis).  I think the tiling will help a lot.  What I wonder, though, is how long it will take to tile?  Afterall, it's still an st_intersection operation to tile each geometry against each tile.

I’ve almost finished writing the tutorial - where I address many of these points.    The variables that affect performance are:
* how you’ve written your ST_Intersection query
* multi vs. non-multi
* size and complexity of geoms
* no. available CPUs (for parallelisation)
* tile batch size - important!!!

All strategies in combination may be necessary if your queries are taking forever.

For the demonstration dataset (a multi polygon representing whole of Australia), my tutorial tiling query incorporates ST_Intersection and ST_Difference subqueries to produce tiled features representing land and water.
I achieved a 49x reduction in the query time to tile the whole of Australia, starting with a single multi polygon.  

The more complex the query, the more significant this time saving is in absolute terms.

> Is there a quad tree type tiling algorithm in a function?  If I do 256 x 256 tiles, doing it all at once would be 65536 operations of st_intersection(complex_geom, square).  With a quad tree I'll only have 4 operations of st_intersection(complex_geom, square), and then 16 of (a_little_less_complex_geom, square), and then 64 of (even_less_complex_geom, square) and so on, for 8 nests.  The last one will still be 65536 operations, but the complex geoms should be a lot simpler by the time I get down to that level.  What do you think, is this worth trying?  Or is intersecting with a square fairly simple regardless of how complex the other geometry is?

I do have a SQL quadgrid tiling function - where a cell divides recursively subject to a maximum number of levels or “value thresholds” - but I’m not sure if that’s the right approach.