<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<div class="moz-cite-prefix">
<div class="moz-cite-prefix">Hi Marco,</div>
<div class="moz-cite-prefix"><br>
</div>
<div class="moz-cite-prefix">Two questions:<br>
* Does the slowdown also appears if you only do the casting to
geography (no ST_Area)?</div>
<div class="moz-cite-prefix">* When you update GEOS, are you
updating any other package at the same time?</div>
<div class="moz-cite-prefix"><br>
</div>
<div class="moz-cite-prefix">Regards,</div>
<div class="moz-cite-prefix">Raúl.</div>
</div>
<div class="moz-cite-prefix"><br>
</div>
<div class="moz-cite-prefix"><br>
</div>
<div class="moz-cite-prefix">On 2021-04-26 12:03, Marco Boeringa
wrote:<br>
</div>
<blockquote type="cite"
cite="mid:fc426c0d-6098-cd77-a551-33e91d7ecb38@boeringa.demon.nl">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<p>Hi all,</p>
<p>I am seeing a very significant performance regression (104x)
when attempting to calculate geodesic area using PostGIS after
upgrading GEOS from 3.8.0 to 3.9.0:<br>
</p>
<p>POSTGIS 3.1.1 / GEOS 3.8.0: 1m52s<br>
POSTGIS 3.1.1 / GEOS 3.9.0: 3h(!)14m45s</p>
<p>This makes it impossible for me to process planet level
OpenStreetMap data, and I had to restore a backup to get back to
a usable situation.</p>
<p>The actual code run is nothing more than what the PostGIS
manual shows:<br>
</p>
<p>"float ST_Area(geography geog, boolean use_spheroid=true);"<br>
<br>
Note that the OpenStreetMap data, as imported by osm2pgsql, is
stored in PostGIS in WGS1984 / SRID 4326 as 'geometry', so in
order to use it in 'ST_Area', the data is simply cast to
'geography':<br>
<br>
"ST_Area(way::geography,true)"</p>
<p>Nothing else happens in this part of the processing when the
performance regression is visible. In fact, other more
complicated parts of the code that call more advanced functions
like 'ST_SimplifyVW' / 'ST_Transform' / 'ST_ChaikinSmoothing'
run at expected / normal speed in GEOS 3.9.0 compared to 3.8.0,
so it is not an overall slower processing that is happening.<br>
</p>
<p>The postgis_full_version results for these setups are:</p>
- POSTGRESQL 13.2 / POSTGIS 3.1.1 / GEOS 3.8.0:<br>
<p>PostGIS version: POSTGIS="3.1.1 aaf4c79" [EXTENSION]
PGSQL="130" GEOS="3.8.0-CAPI-1.13.1 " PROJ="6.3.1"
LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3"
WAGYU="0.5.0 (Internal)"<br>
<br>
- POSTGRESQL 13.2 / POSTGIS 3.1.1 / GEOS 3.9.0:<br>
<br>
PostGIS version: POSTGIS="3.1.1 aaf4c79" [EXTENSION] PGSQL="130"
GEOS="3.9.0-CAPI-1.16.2" PROJ="6.3.1" LIBXML="2.9.10"
LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)"</p>
<p>I am not entirely sure why 'postgis_full_version' is showing
'PGSQL="130"' instead of 'PGSQL="132"', as I am definitely on
13.2, 'SELECT version()' returns: </p>
<p>PostgreSQL 13.2 (Ubuntu 13.2-1.pgdg20.04+1) on
x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit<br>
</p>
<p>Note that the installation of PostgreSQL / PostGIS is based on
the plain vanilla one as available from the official apt
repository for PostgreSQL:</p>
<p><a rel="nofollow" class="external free"
href="http://apt.postgresql.org/pub/repos/apt/"
moz-do-not-send="true">http://apt.postgresql.org/pub/repos/apt/</a></p>
<p>while GEOS was updated to 3.9.0 from the UbuntuGIS repository:</p>
<p> <a class="https"
href="https://launchpad.net/~ubuntugis/+archive/ppa/"
moz-do-not-send="true">https://launchpad.net/~ubuntugis/+archive/ppa/</a></p>
<p>*** STEPS to reproduce ***</p>
<p>- Load some OpenStreetMap as WGS1984 / SRID 4326 using
osm2pgsql</p>
<p>- Ensure you have the default POSTGRESQL 13.2 / POSTGIS 3.1.1 /
GEOS 3.8.0 version installed as from the official repository</p>
<p>- Create a column to store the geodesic area data</p>
<p>- UPDATE the column using "ST_Area(way::geography,true)" and
note timing</p>
<p>- Now update GEOS to 3.9.0 from the UbuntuGIS repository</p>
<p>- Ensure GEOS is properly at 3.9.0 by calling 'SELECT
postgis_full_version()'</p>
<p>- Run UPDATE a second time with "ST_Area(way::geography,true)"
and again note timing</p>
<p>This should show a major difference.</p>
<p>Marco<br>
</p>
<p><br>
</p>
<p><br>
</p>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<pre class="moz-quote-pre" wrap="">_______________________________________________
postgis-devel mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-devel@lists.osgeo.org">postgis-devel@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/postgis-devel">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a>
</pre>
</blockquote>
<p><br>
</p>
</body>
</html>