<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Hi Raúl,</p>
<p>"Does the slowdown also appears if you only do the casting to
geography (no ST_Area)?"</p>
<p>I was in the process of restoring a VM and restarting a process.
Answering this question will require me to stop that process and
restore the VM again from backup, which is quite time consuming.
It will therefor take some time to answer this.</p>
<p>"When you update GEOS, are you updating any other package at the
same time?"</p>
<p>I only selected 'libgeos-3.9.0' from the Synaptic Package
Manager. I can't remember if it automatically selected other
dependencies of that package to update as well, but I certainly
didn't select anything else myself at the same time. You probably
have a better idea of what dependencies might have been updated in
other to support 'libgeos-3.9.0'.</p>
<p>I do have one question for you as well:</p>
<p>* Is GEOS dependency on PROJ dynamic like PostGIS, or is it baked
in? What I mean is, if I see 'SELECT postgis_full_version' return
'PROJ="6.3.1"' as it does in my installation, is GEOS / libgeos
then using the exact same version 6.3.1, or might it be using an
older version, e.g. PROJ 5.x?</p>
<p>Regards,</p>
<p>Marco<br>
</p>
<div class="moz-cite-prefix">Op 26-4-2021 om 14:42 schreef Raúl
Marín:<br>
</div>
<blockquote type="cite"
cite="mid:ddef057a-07d3-1df7-eb54-d36e9f3bdcf0@rmr.ninja">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<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" moz-do-not-send="true">postgis-devel@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" moz-do-not-send="true">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a>
</pre>
</blockquote>
<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>
</body>
</html>