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

[Marco Boeringa]

Hi Raúl,

"Does the slowdown also appears if you only do the casting to geography 
(no ST_Area)?"

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.

"When you update GEOS, are you updating any other package at the same time?"

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

I do have one question for you as well:

* 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?

Regards,

Marco

Op 26-4-2021 om 14:42 schreef 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
>
>
>
> _______________________________________________
> 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/2b3c01b3/attachment-0001.html>