[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 Lars Aksel Opsahl):

 Yes it did go faster as you suggested , but still used almost 1.5 hours.

 {{{

 explain analyze select sum(ST_area(st_intersection(gk.geom, pt.geo)))
 FROM
 klima.plantegning_3035_test_no_hole pt,
 klima.gk_02 AS gk
 WHERE ST_Intersects(gk.geom, pt.geo)


 Aggregate  (cost=4602.65..4602.66 rows=1 width=8) (actual
 time=5199377.456..5199377.459 rows=1 loops=1)
         ->  Nested Loop  (cost=0.28..448.20 rows=329 width=488) (actual
 time=9.992..9553.507 rows=329118 loops=1)
                                 ->  Seq Scan on
 plantegning_3035_test_no_hole pt  (cost=0.00..0.51 rows=1 width=32)
 (actual time=0.020..0.031 rows=1 loops=1)
                                 ->  Index Scan using gk_02_geom_idx on
 gk_02 gk  (cost=0.28..447.36 rows=33 width=456) (actual
 time=6.312..8893.807 rows=329118 loops=1)
                                                         Index Cond: (geom
 && pt.geo)
                                                         Filter:
 st_intersects(geom, pt.geo)
 Planning Time: 17.973 ms
 Execution Time: 5199379.466 ms
 (8 rows)

 Time: 5199360.839 ms (01:26:39.361)

 }}}


 Then if add subdivide when using klima.plantegning_3035_test_no_hole it
 goes more than 400 times faster


 {{{
 explain analyze select sum(ST_area(st_intersection(gk.geom, pt.geo)))
 FROM
 (select st_subdivide(geo,300) as geo from
 klima.plantegning_3035_test_no_hole) pt,
 klima.gk_02 AS gk
 WHERE ST_Intersects(gk.geom, pt.geo);


 Aggregate  (cost=4590314.56..4590314.57 rows=1 width=8) (actual
 time=12042.480..12042.482 rows=1 loops=1)
          ->  Nested Loop  (cost=0.28..434377.01 rows=329118 width=488)
 (actual time=1026.694..1987.237 rows=340682 loops=1)
                                  ->  ProjectSet  (cost=0.00..18.02
 rows=1000 width=32) (actual time=1026.463..1028.579 rows=1208 loops=1)
                                                          ->  Seq Scan on
 plantegning_3035_test_no_hole  (cost=0.00..0.51 rows=1 width=32) (actual
 time=0.011..0.014 rows=1 loops=1)
                                  ->  Index Scan using gk_02_geom_idx on
 gk_02 gk  (cost=0.28..434.03 rows=33 width=456) (actual time=0.039..0.761
 rows=282 loops=1208)
                                                          Index Cond: (geom
 && (st_subdivide(plantegning_3035_test_no_hole.geo, 300, '-1'::double
 precision)))
                                                          Filter:
 st_intersects(geom, (st_subdivide(plantegning_3035_test_no_hole.geo, 300,
 '-1'::double precision)))
                                                          Rows Removed by
 Filter: 12
 Planning Time: 1.078 ms
 JIT:
          Functions: 13
          Options: Inlining true, Optimization true, Expressions true,
 Deforming true
          Timing: Generation 0.600 ms, Inlining 17.177 ms, Optimization
 29.579 ms, Emission 17.382 ms, Total 64.738 ms
 Execution Time: 12043.200 ms
 (14 rows)

 Time: 12045.467 ms (00:12.045)

 }}}


 This means subdivide needs to used on any input of some size as it is now.
 That's OK when we do only area computations.

 But if the user also needs the intersection shapes, we may be forced to do
 group by to remove the extra lines added by subdivide.

 If do not do a group by before sending back the result, we may mislead the
 user if we checks spatial data for the biggest/smallest and so on, since
 the input polygons has divided and then does reflect the shape of the
 original data covered by the input polygon. We may also end with tiny
 sliver polygons if we are unlucky.
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5953#comment:4>
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.