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

[Jason Greenlaw - NOAA Affiliate]

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


On Tue, Oct 8, 2019 at 12:52 PM Jason Greenlaw - NOAA Affiliate <
jason.greenlaw at noaa.gov> wrote:

> Hi Raúl,
>
> I think you may be right.
>
> The original server which experienced the issue is on GEOS 3.7.1:
>
> POSTGIS="2.4.8 r17696" PGSQL="95" GEOS="3.7.1-CAPI-1.11.1 27a5e771"
> PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.3.2, released 2018/09/21"
> LIBXML="2.9.1" LIBJSON="0.11" LIBPROTOBUF="1.0.2" RASTER
>
>
> The CentOS 7 VM I tested is on GEOS 3.7.2:
>
> POSTGIS="2.5.3 r17699" [EXTENSION] PGSQL="95" GEOS="3.7.2-CAPI-1.11.2
> b55d2125" PROJ="Rel. 6.2.0, September 1st, 201
> 9" GDAL="GDAL 3.0.1, released 2019/06/28" LIBXML="2.9.1" LIBJSON="0.11"
> LIBPROTOBUF="1.0.2" TOPOLOGY (topology procs
> from "2.2.5 r15298" need upgrade) RASTER
>
>
> And the crunchy images are also on GEOS 3.7.2
>
> POSTGIS="2.5.3 r17699" [EXTENSION] PGSQL="95" GEOS="3.7.2-CAPI-1.11.2
> b55d2125" PROJ="Rel. 6.2.0, September 1st, 2019" GDAL="GDAL 3.0.1, released
> 2019/06/28" LIBXML="2.9.1" LIBJSON="0.11" LIBPROTOBUF="1.0.2" RASTER
>
>
> The mdillon/postgis images, which don't exhibit the issue, appear to be on
> GEOS 3.5.1:
>
> POSTGIS="2.5.2 r17328" [EXTENSION] PGSQL="95" GEOS="3.5.1-CAPI-1.9.1
> r4246" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.1.2, released
> 2016/10/24" LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.2.1" TOPOLOGY
> RASTER
>
>
> Thanks for testing and pointing me to the GEOS bug - really appreciate the
> help!
>
> Jason
>
>
>
> On Tue, Oct 8, 2019 at 12:34 PM <rmrodriguez at carto.com> wrote:
>
>> Hi,
>>
>> This seems related to https://trac.osgeo.org/postgis/ticket/4075. What
>> version of GEOS are you using in each server?
>>
>> I've tried with Postgis 3.0 and GEOS 3.8 and it hasn't finished after
>> ~10 minutes.
>>
>> Regards
>> --
>> Raúl Marín Rodríguez
>> carto.com
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20191009/0f21f5e6/attachment.html>