<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
<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/">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/">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>
</body>
</html>