[PostGIS] #5953: Slow st_intersection with big input polygons

[PostGIS]

#5953: Slow st_intersection with big input polygons
--------------------------------+---------------------------
  Reporter:  Lars Aksel Opsahl  |      Owner:  pramsey
      Type:  enhancement        |     Status:  new
  Priority:  medium             |  Milestone:  PostGIS 3.6.0
 Component:  postgis            |    Version:  master
Resolution:                     |   Keywords:
--------------------------------+---------------------------
Comment (by pramsey):

 Yes, the pattern that maximizes the amount of caching, when you're doing
 an intersection is something like
 {{{
 SELECT big.id AS big_id, small.id AS small_id,
   CASE
     WHEN ST_Contains(big.geom, small.geom)
     THEN small
     ELSE ST_Intersection(bit.geom, small.geom)
     END AS geom
 FROM big
 JOIN small
   ON ST_Intersects(big.geom, small.geom)
 }}}
 This gets you two levels of cached predicate logic protecting the
 expensive intersection logic. The join condition ensures that no disjoint
 pairs are considered, efficiently using intersects. The case condition
 ensures that fully contained objects are not passed to intersection.
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5953#comment:7>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.