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

Mon Apr 26 08:11:53 PDT 2021

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.

There is a known issue with PROJ 6+ where creating a transformation 
object is considerably more costly than in PROJ <=5 
(https://trac.osgeo.org/postgis/ticket/4372), 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:

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

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?

Marco

Op 26-4-2021 om 15:52 schreef 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
>
> _______________________________________________
> 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/ec73ac2f/attachment.html>