<div dir="ltr"><div dir="ltr">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.<div><br></div><div><div>With PostGIS 2.3.7 / GEOS 3.6.1, query took ~85 seconds on my machine:<div><br></div><div><blockquote style="margin:0 0 0 40px;border:none;padding:0px"><div>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<br></div><div><br></div><div>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;<br> QUERY PLAN <br>------------------------------------------------------------------------------------------------------------------------<br> HashAggregate (cost=10.30..10.45 rows=10 width=706) (actual time=35922.055..85310.959 rows=5 loops=1)<br> Group Key: category, vteccode, prod_type, validtime, starttime, endtime<br> -> 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)<br> Planning time: 0.460 ms<br> Execution time: 85314.516 ms<br>(5 rows)</div></blockquote></div></div></div><div><br></div><div>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.</div><div><br></div><div>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).</div><div><br></div><div>I also tested 3.8.0rc3 (which contains the recent <a href="https://github.com/libgeos/geos/commit/6bac4f45e94f8e18409803b17eb306dd94fa065a#diff-ee28c8845c72c5f8c7307c430276b944">performance fix</a> 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.</div><div><br></div><div>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.</div><div><br></div><div>Thanks,</div><div>Jason</div><div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, Oct 8, 2019 at 12:52 PM Jason Greenlaw - NOAA Affiliate <<a href="mailto:jason.greenlaw@noaa.gov" target="_blank">jason.greenlaw@noaa.gov</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div dir="ltr">Hi Raúl,<div><br></div><div>I think you may be right.</div><div><br></div><div>The original server which experienced the issue is on GEOS 3.7.1:</div><div><br></div><blockquote style="margin:0px 0px 0px 40px;border:none;padding:0px"><div>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</div></blockquote><div><br></div><div>The CentOS 7 VM I tested is on GEOS 3.7.2:</div><div><br></div><div><blockquote style="margin:0px 0px 0px 40px;border:none;padding:0px"><div>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<br>9" GDAL="GDAL 3.0.1, released 2019/06/28" LIBXML="2.9.1" LIBJSON="0.11" LIBPROTOBUF="1.0.2" TOPOLOGY (topology procs<br>from "2.2.5 r15298" need upgrade) RASTER<br></div></blockquote></div><div><br></div><div>And the crunchy images are also on GEOS 3.7.2</div><div><br></div><div><blockquote style="margin:0px 0px 0px 40px;border:none;padding:0px"><div>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<br></div></blockquote></div><div><br>The mdillon/postgis images, which don't exhibit the issue, appear to be on GEOS 3.5.1:</div><div><br></div><blockquote style="margin:0px 0px 0px 40px;border:none;padding:0px"><div>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</div></blockquote><div><br></div><div>Thanks for testing and pointing me to the GEOS bug - really appreciate the help!</div><div><br></div><div>Jason</div><div><br></div><div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, Oct 8, 2019 at 12:34 PM <<a href="mailto:rmrodriguez@carto.com" target="_blank">rmrodriguez@carto.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Hi,<br>
<br>
This seems related to <a href="https://trac.osgeo.org/postgis/ticket/4075" rel="noreferrer" target="_blank">https://trac.osgeo.org/postgis/ticket/4075</a>. What<br>
version of GEOS are you using in each server?<br>
<br>
I've tried with Postgis 3.0 and GEOS 3.8 and it hasn't finished after<br>
~10 minutes.<br>
<br>
Regards<br>
-- <br>
Raúl Marín Rodríguez<br>
<a href="http://carto.com" rel="noreferrer" target="_blank">carto.com</a><br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div></div>
</blockquote></div>
</div>