[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):

 Thanks for your feedback

 Replying to [comment:2 mdavis]:
 > My take on this is:
 >
 > As for the slower no-holes polygon intersection query, this is indeed an
 apparent anomaly, since a simpler polygon with fewer vertices should
 provide faster intersection.  However, the no-holes polygon query isn't an
 equivalent comparison, since it runs `ST_BuildArea` and `ST_Boundary`.
 These might be taking significant time.  Materializing the boundary
 polygon in a separate table would reveal if this is the cause of the
 slowdown.

 Good point I am running that test now.

 To create the new input test table is fast as you see.

 {{{
 CREATE table klima.plantegning_3035_test_no_hole AS
 select ST_BuildArea(ST_Boundary(geo)) as geo from
 klima.plantegning_3035_test;
 SELECT 1
 Time: 170,497 ms

 }}}

 The new table

 {{{
 \d klima.plantegning_3035_test_no_hole
                                 Table
 "klima.plantegning_3035_test_no_hole"
  Column |   Type   | Collation | Nullable |
 Default
 --------+----------+-----------+----------+-----------------------------------------------------------------
  geo    | geometry |           |          |
  id     | integer  |           | not null |
 nextval('klima.plantegning_3035_test_no_hole_id_seq'::regclass)
 Indexes:
     "plantegning_3035_test_no_hole_pkey" PRIMARY KEY, btree (id)
     "plantegning_3035_test_no_hole_geo_idx" gist (geo)
 }}}

 I am now running the command below against the new table

 {{{

 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)

 }}}

 Will report back when this is done.
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5953#comment:3>
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.