<div dir="ltr"><div>So, to be clearer about how to interface with -devel with your issues:</div><div><br></div><div>- data, in a dump file, as small as possible to demonstrate the issue, preferably just one table, generated with pg_dump -d database -t table --no-owner, ideally small enough to attach to a trac ticket</div><div>- the ideal size of data is "no data at all", many bugs can be reduced to one geometry that can be part of the SQL call that exercises the condition</div><div>- SQL, in a sql file, again as small as possible, one statement ideally, with as few functions as possible</div><div><br></div><div>The goal here is to allow a dev to copy your SQL and drop it into a console in one cut-paste step, because given our finite time base, spending a lot of time trying to replicate a situation and then getting a "null result" where everything still works fine is hugely annoying. I'm not going to attempt to replicate your setup as described because there are way too many degrees of freedom, I'm almost guaranteed to get a non replicable situation, and have wasted whatever time I invest in it.</div><div><br></div><div>People who invest the time to reduce their problem to a cut'paste example get responses and fixes surprisingly fast. Those who don't wonder why OSS developers are such standoffish bastards. There's a continuum of quality between "it's broken" and "here's a one line SQL statement that does exactly this thing which is not what is supposed to happen", but the closer you get to the far end, the faster and better things will go. I recognize that regressions and multi-version issues are the hardest, but here too time reducing the problem to the minimum degrees of freedom will result in attention.</div><div><br></div><div>In answer to your question: GEOS has no direct linkage to proj, and it makes little sense that ST_Area(geometry::geography) would be sensitive to GEOS version, as neither (the function nor the cast) enters the GEOS code line at all. (They do, however, both enter the proj code line.)</div><div><br></div><div>P.<br></div><div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Apr 26, 2021 at 8:11 AM Marco Boeringa <<a href="mailto:marco@boeringa.demon.nl">marco@boeringa.demon.nl</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>
<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 href="https://trac.osgeo.org/postgis/ticket/4372" target="_blank">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>Op 26-4-2021 om 15:52 schreef Marco
Boeringa:<br>
</div>
<blockquote type="cite">
<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>Op 26-4-2021 om 14:42 schreef Raúl
Marín:<br>
</div>
<blockquote type="cite">
<div>
<div>Hi Marco,</div>
<div><br>
</div>
<div>Two questions:<br>
* Does the slowdown also appears if you only do the casting
to geography (no ST_Area)?</div>
<div>* When you update GEOS, are you
updating any other package at the same time?</div>
<div><br>
</div>
<div>Regards,</div>
<div>Raúl.</div>
</div>
<div><br>
</div>
<div><br>
</div>
<div>On 2021-04-26 12:03, Marco Boeringa
wrote:<br>
</div>
<blockquote type="cite">
<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" href="http://apt.postgresql.org/pub/repos/apt/" target="_blank">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 href="https://launchpad.net/~ubuntugis/+archive/ppa/" target="_blank">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></fieldset>
<pre>_______________________________________________
postgis-devel mailing list
<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a>
</pre>
</blockquote>
<p><br>
</p>
<br>
<fieldset></fieldset>
<pre>_______________________________________________
postgis-devel mailing list
<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a>
</pre>
</blockquote>
<br>
<fieldset></fieldset>
<pre>_______________________________________________
postgis-devel mailing list
<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a>
</pre>
</blockquote>
</div>
_______________________________________________<br>
postgis-devel mailing list<br>
<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><br>
</blockquote></div>