[postgis-users] ST_Union(ST_Buffer(...)) hangs for large polygons on RHEL7/CentOS7 / PG 9.5

[Jason Greenlaw - NOAA Affiliate]

For those interested, the GEOS performance regression was filed at
https://trac.osgeo.org/geos/ticket/997

A functioning workaround is to call ST_SnapToGrid on the buffered geometry
before the union operation, e.g. ST_Union(ST_SnapToGrid(ST_Buffer(...),
0.0001))

Jason


On Wed, Oct 9, 2019 at 2:54 PM Jason Greenlaw - NOAA Affiliate <
jason.greenlaw at noaa.gov> wrote:

> I did a bit more testing and determined that the 3.6.1 -> 3.6.2 regression
> does not actually appear to be the cause.  Instead, performance issues for
> my use-case seem to have been introduced between 3.6.x -> 3.7.0.
>
> With PostGIS 2.3.7 / GEOS 3.6.1, query took ~85 seconds on my machine:
>
> POSTGIS="2.3.7 r16523" PGSQL="95" GEOS="3.6.1-CAPI-1.10.1 r0" PROJ="Rel.
> 4.9.3, 15 August 2016" GDAL="GDAL 1.11.4, released 2016/01/25"
> LIBXML="2.9.1" LIBJSON="0.11" RASTER
>
> EXPLAIN ANALYZE SELECT ST_Union(ST_Buffer(wkb_geometry,
> 100.0))::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.30..10.45 rows=10 width=706) (actual
> time=35922.055..85310.959 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.014..0.053 rows=21 loops=1)
>  Planning time: 0.460 ms
>  Execution time: 85314.516 ms
> (5 rows)
>
>
> I got similar timings with GEOS 3.6.2 (90 sec), 3.6.3 (89 sec) and 3.6.4
> (100 sec), but when testing with 3.7.0, the command took 70 minutes to
> finish.
>
> I've kept the PostGIS version at 2.3 for this round of testing because 2.5
> was not working with GEOS 3.6.0 (error about missing
> function GEOSFrechetDistanceDensify).
>
> I also tested 3.8.0rc3 (which contains the recent performance fix
> <https://github.com/libgeos/geos/commit/6bac4f45e94f8e18409803b17eb306dd94fa065a#diff-ee28c8845c72c5f8c7307c430276b944>
> ported from JTS) with PostGIS 2.5.3, but that too showed severe performance
> issues for my use-case, with the command finally finishing after 2 hours 27
> minutes.
>
> If anyone has additional insight please let me know.  I will likely do a
> few more tests and follow up with a GEOS bug report.
>
> Thanks,
> Jason
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20191011/14db2393/attachment.html>