[geos-devel] [GEOS] #997: ST_Union performance degradation from GEOS 3.6.x -> 3.7.x

[GEOS]

#997: ST_Union performance degradation from GEOS 3.6.x -> 3.7.x
-------------------------+---------------------------
 Reporter:  greenlaw     |       Owner:  geos-devel@…
     Type:  defect       |      Status:  new
 Priority:  major        |   Milestone:
Component:  Default      |     Version:  3.7.0
 Severity:  Significant  |  Resolution:
 Keywords:  union        |
-------------------------+---------------------------

Comment (by greenlaw):

 @mdavis Yes, the `ST_SnapToGrid` workaround seems like it should work
 perfectly for me as long as I use a small enough value (0.1 produced
 `ERROR:  GEOSUnaryUnion: TopologyException: Input geom 1 is invalid: Self-
 intersection at or near point -15008907.701449277 7619503.4876811597 at
 -15008907.701449277 7619503.4876811597`).

 Not terribly concerned about precision here, but 0.001 seems to work just
 fine, and the command completes in 79 sec. The resulting geometry looks
 good to me.

 I will put this change into production - don't expect any issues but will
 be sure to report back if I run into anything.

 Will be happy to test any potential fixes you guys come up with down the
 road.

 Thanks so much for your help.

 {{{
 EXPLAIN ANALYZE SELECT ST_Union(ST_SnapToGrid(ST_Buffer(wkb_geometry,
 100.0), 0.001))::geometry(Geometry,
 3857),category,vteccode,prod_type,validtime,starttime,endtime FROM
 public.nws_haz_hang_201910 GROUP BY
 category,vteccode,prod_type,validtime,starttime,endtime;
                                                        QUERY PLAN
 ------------------------------------------------------------------------------------------------------------------------
  HashAggregate  (cost=10.32..10.47 rows=10 width=706) (actual
 time=33352.916..79931.923 rows=5 loops=1)
    Group Key: category, vteccode, prod_type, validtime, starttime, endtime
    ->  Seq Scan on nws_haz_hang_201910  (cost=0.00..10.10 rows=10
 width=706) (actual time=0.006..0.041 rows=21 loops=1)
  Planning time: 0.133 ms
  Execution time: 79931.984 ms
 }}}

-- 
Ticket URL: <https://trac.osgeo.org/geos/ticket/997#comment:11>
GEOS <http://trac.osgeo.org/geos>
GEOS (Geometry Engine - Open Source) is a C++ port of the Java Topology Suite (JTS).