[postgis-devel] Much slower processing on GEOS 3.9.0 versus 3.8.0 for geodesic area calculation

[Marco Boeringa]

Hi all,

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:

POSTGIS 3.1.1 / GEOS 3.8.0: 1m52s
POSTGIS 3.1.1 / GEOS 3.9.0: 3h(!)14m45s

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.

The actual code run is nothing more than what the PostGIS manual shows:

"float ST_Area(geography geog, boolean use_spheroid=true);"

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':

"ST_Area(way::geography,true)"

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.

The postgis_full_version results for these setups are:

- POSTGRESQL 13.2 / POSTGIS 3.1.1 / GEOS 3.8.0:

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)"

  - POSTGRESQL 13.2 / POSTGIS 3.1.1 / GEOS 3.9.0:

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)"

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:

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

Note that the installation of PostgreSQL / PostGIS is based on the plain 
vanilla one as available from the official apt repository for PostgreSQL:

http://apt.postgresql.org/pub/repos/apt/ 
<http://apt.postgresql.org/pub/repos/apt/>

while GEOS was updated to 3.9.0 from the UbuntuGIS repository:

https://launchpad.net/~ubuntugis/+archive/ppa/ 
<https://launchpad.net/~ubuntugis/+archive/ppa/>

*** STEPS to reproduce ***

- Load some OpenStreetMap as WGS1984 / SRID 4326 using osm2pgsql

- Ensure you have the default POSTGRESQL 13.2 / POSTGIS 3.1.1 / GEOS 
3.8.0 version installed as from the official repository

- Create a column to store the geodesic area data

- UPDATE the column using "ST_Area(way::geography,true)" and note timing

- Now update GEOS to 3.9.0 from the UbuntuGIS repository

- Ensure GEOS is properly at 3.9.0 by calling 'SELECT 
postgis_full_version()'

- Run UPDATE a second time with "ST_Area(way::geography,true)" and again 
note timing

This should show a major difference.

Marco



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20210426/5ac4b3ae/attachment.html>