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

[Raúl Marín]

Hi Marco,

Two questions:
* Does the slowdown also appears if you only do the casting to geography 
(no ST_Area)?
* When you update GEOS, are you updating any other package at the same time?

Regards,
Raúl.


On 2021-04-26 12:03, Marco Boeringa wrote:
>
> 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
>
>
>
>
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-devel


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