<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>One more thing to note related to this issue is that the UPDATE
taking place is in a high multi-threaded application, with
individual UPDATE statements for each record, however batched in
hundreds or thousands of records inside a single transaction. <br>
</p>
<p>There is a known issue with PROJ 6+ where creating a
transformation object is considerably more costly than in PROJ
<=5 (<a class="moz-txt-link-freetext" href="https://trac.osgeo.org/postgis/ticket/4372">https://trac.osgeo.org/postgis/ticket/4372</a>), but both
installations I tested for GEOS 3.8.0 and GEOS 3.9.0 are running
PROJ 6.3.1, as the 'SELECT postgis_full_version()' showed,
*unless* the answer to my last question:</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>is that GEOS *does* have its own PROJ version, and my non-updated
original installation of GEOS 3.8.0 is using a lower than PROJ 6
version, while the GEOS 3.9.0 is on 6 or higher. Anyone can answer
the above?</p>
<p>Marco<br>
</p>
<div class="moz-cite-prefix">Op 26-4-2021 om 15:52 schreef Marco
Boeringa:<br>
</div>
<blockquote type="cite"
cite="mid:3a323dba-09fe-d322-bfe3-f4fed4be9f6e@boeringa.demon.nl">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<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" 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>
<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>