[postgis-devel] Much slower processing on GEOS 3.9.0 versus 3.8.0 for geodesic area calculation - Upgrade to PROJ 7.2.1 causes issue

Wed Apr 28 09:59:02 PDT 2021

Just to try and further remove variables from this investigation, I've put a little program here, which can be built and run against any Proj >= 6

https://gist.github.com/pramsey/493b2490a8736fd8c00e30efa62e4ec3

It just runs the proj_create_crs_to_crs() function a number of times and figures out the average invocation cost. You can change the from/to path by altering the commandline values (see the comment at top for build instructions and run instructions). 

Testing against proj8, I find that setting up a full projection is quite costly (22ms)

Proj version '8.0.0'
Using 'epsg:4326' as from-srid
Using 'epsg:26910' as to-srid
Ran 1000 iterations, 22031.7 us per iteration

While setting up a null-transformation is quite cheap (240us)

Proj version '8.0.0'
Using 'epsg:4326' as from-srid
Using 'epsg:4326' as to-srid
Ran 1000 iterations, 249.991 us per iteration

The odd thing (unfortunately for trying to understand this issue) is that it is precisely this null transformation which is the setup penalty when doing the geometry::geography cast. It just instantiates a epsg:4326->epsg:4326 transform so it can interrogate the objects and see if they are geodetic. 

Now, maybe the null case got optimzed in version 8? I dunno.

I now leave running this little test against multiple proj versions from 6 to 8 as an exercise for someone to try out. Maybe initialization costs leapt up in one version or another.

P.

> On Apr 27, 2021, at 11:51 PM, Marco Boeringa <marco at boeringa.demon.nl> wrote:
> 
> Hi Paul and others,
> 
> After the first update of GEOS only, see below, that showed *normal* speed, I now selected:
> 
> * proj-bin
> 
> from the available updatable packages in the UbuntuGIS repository as a first test to see if this would replicate the performance issues. According to Synaptic, the installed version was 6.3.1 (which correspond with the result of 'SELECT postgis_full_version()'), and the available update 7.2.1.
> 
> Once selected, the additional dependencies:
> 
> * libproj19
> 
> * libproj-dev
> 
> * proj-data
> 
> were also automatically marked for update / installation by Synaptic, so also included in the update.
> 
> I then did a full reboot / restart of the VM, and restarted the PostgreSQL service. I subsequently started the processing again, and yes(!), I *do* see the *slow* processing again specifically with the same step of 'ST_Area(way::geography,true)' where the cast to geography is done.
> 
> One thing that is really strange though, is that:
> 
> 'SELECT postgis_full_version()'
> 
> still returns:
> 
> "PROJ=6.3.1"
> 
> instead of the exptected "PROJ=7.2.1" after updating the 'proj-bin' package via Synaptic, even though Synaptic clearly showing the packages were succesfully updated to 7.2.1 and no error was thrown. I have this code line in my Python code, and also ran it from pgAdmin4, both reporting the same result for 'SELECT postgis_full_version()', so 6.3.1.
> 
> Anyway, what was initially suspected, that PROJ is involved, now seems to be quite clear. However, contrary to an update of PROJ 5.x TO PROJ 6.x, it seems the upgrade of any of the related associated packages to PROJ 7.x is causing the issue.
> 
> Note though that in the PostGIS ticket:
> 
> "#4890: ST_Transform large slow down from upgrade to 3.1.1 from 2.4"
> 
> according to the original user reporting it, both PostGIS versions 2.4/3.1.1 were supposed to be on PROJ 7.2.1 already. However, given the fact that I see my installation return '6.3.1' even after the upgrade to '7.2.1' and doing a full reboot of the VM and restart of the PostgreSQL service, there is a possibility the 'SELECT postgis_full_version()' result for the 2.4 version of the original poster, might be wrong, and he/she might still be using PROJ 5.x on that installation (allthough this would not fit my issue with *lower* than installed version reported).
> 
> Marco
> 
> 
> Op 28-4-2021 om 00:20 schreef Marco Boeringa:
>> Hi Paul,
>> 
>> Maybe I can put a nail into that GEOS coffin myself. I now attempted to reproduce this issue a third time after another restore of the VM from backup, but now was really careful to only select the bare minimum in Synaptic Package Manager.
>> 
>> As a bare minimum, these were updated:
>> 
>> * libgeos-3.9.0
>> * libgeos-c1v5
>> * libgeos-dev
>> 
>> to 3.9.0.
>> 
>> This now ran at *normal* speed, so no slow down compared to 3.8.0.
>> 
>> I want to investigate this further, and see if installing any of the other possible upgrades from the UbuntuGIS repository, that Synaptic offers once I refresh it, potentially are involved. Will take some time to find out though.
>> 
>> Marco
>> 
>> Op 27-4-2021 om 22:46 schreef Paul Ramsey:
>>> Just to stick a nail in the GEOS-version-affects-geography-area conversation, I took the time to set up two builds (GEOS 3.8 and GEOS 3.9) and swapped between them without changing anything else about the system.
>>> 
>>> postgis=# select postgis_full_version();
>>> postgis_full_version
>>> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
>>>   POSTGIS="3.2.0dev 3.1.0rc1-162-g46efb9f2d" [EXTENSION] PGSQL="130" GEOS="3.8.3-CAPI-1.13.4" PROJ="8.0.0" GDAL="GDAL 3.2.0, released 2020/10/26" LIBXML="2.9.4" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)"
>>> 
>>> postgis=# select sum(st_area(geom::geography)) from va;
>>>          sum
>>> --------------------
>>>   1070124793748.3967
>>> (1 row)
>>> 
>>> Time: 1449.551 ms (00:01.450)
>>> 
>>> 
>>> Then stop the server, change the LD_LIBRARY_PATH to the 3.9 install location and restart.
>>> 
>>> postgis=# select postgis_full_version();
>>> postgis_full_version
>>> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
>>>   POSTGIS="3.2.0dev 3.1.0rc1-162-g46efb9f2d" [EXTENSION] PGSQL="130" GEOS="3.9.2dev-CAPI-1.14.2" PROJ="8.0.0" GDAL="GDAL 3.2.0, released 2020/10/26" LIBXML="2.9.4" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)"
>>> 
>>> postgis=# select sum(st_area(geom::geography)) from va;
>>>          sum
>>> --------------------
>>>   1070124793748.3959
>>> (1 row)
>>> 
>>> Time: 1439.969 ms (00:01.440)
>>> 
>>> 
>>> 
>>> There's no effect to be seen when GEOS version is the only variable in play.
>>> 
>>> P
>>> _______________________________________________
>>> 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